Python MySQL: Create Table

Introduction

Creating tables in a MySQL database is a fundamental task when working with relational databases. Python, with its extensive library support, makes it easy to interact with MySQL databases. In this guide, we will use the mysql-connector-python library to create a new table in a MySQL database.

Setting Up

Install MySQL Connector

First, you need to install the MySQL connector for Python. You can install it using pip:

pip install mysql-connector-python

Connecting to MySQL

To create a table, you need to connect to the MySQL server and the specific database where you want to create the table. You will need the following details:

Hostname (usually localhost)
Username
Password
Database name

Example: Connecting to MySQL

import mysql.connector

# Connect to the MySQL server and database
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

if connection.is_connected():
    print("Connected to MySQL database")

# Close the connection
connection.close()

Creating a Table

Once you have connected to the MySQL database, you can create a new table by executing an SQL statement using a cursor object.

Example: Creating a Table

import mysql.connector

# Connect to the MySQL server and database
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object
cursor = connection.cursor()

# Create a new table
create_table_query = """
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT,
    gender ENUM('Male', 'Female') NOT NULL,
    position VARCHAR(255)
)
"""

cursor.execute(create_table_query)
print("Table 'employees' created successfully")

# Close the connection
connection.close()

Checking if Table Exists

Before creating a table, it’s good practice to check if it already exists to avoid errors.

Example: Check if Table Exists

import mysql.connector

# Connect to the MySQL server and database
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object
cursor = connection.cursor()

# Check if table exists
cursor.execute("SHOW TABLES")

tables = cursor.fetchall()

table_name = "employees"

if (table_name,) in tables:
    print(f"Table '{table_name}' already exists.")
else:
    create_table_query = f"""
    CREATE TABLE {table_name} (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        age INT,
        gender ENUM('Male', 'Female') NOT NULL,
        position VARCHAR(255)
    )
    """
    cursor.execute(create_table_query)
    print(f"Table '{table_name}' created successfully.")

# Close the connection
connection.close()

Inserting Data into the Table

After creating a table, you can insert data into it using an INSERT statement.

Example: Inserting Data

import mysql.connector

# Connect to the MySQL server and database
connection = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database"
)

# Create a cursor object
cursor = connection.cursor()

# Insert data into the table
insert_query = """
INSERT INTO employees (name, age, gender, position)
VALUES (%s, %s, %s, %s)
"""

data = ("John Doe", 28, "Male", "Software Engineer")
cursor.execute(insert_query, data)

# Commit the transaction
connection.commit()
print("Data inserted successfully")

# Close the connection
connection.close()

Complete Example

Here is a complete example that includes connecting to the MySQL server, checking if a table exists, creating a table if it does not exist, and inserting data into the table.

import mysql.connector

# Database connection details
host = "localhost"
user = "your_username"
password = "your_password"
database = "your_database"

# Connect to the MySQL server and database
connection = mysql.connector.connect(
    host=host,
    user=user,
    password=password,
    database=database
)

# Create a cursor object
cursor = connection.cursor()

# Check if table exists
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()

table_name = "employees"

if (table_name,) in tables:
    print(f"Table '{table_name}' already exists.")
else:
    create_table_query = f"""
    CREATE TABLE {table_name} (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        age INT,
        gender ENUM('Male', 'Female') NOT NULL,
        position VARCHAR(255)
    )
    """
    cursor.execute(create_table_query)
    print(f"Table '{table_name}' created successfully.")

# Insert data into the table
insert_query = f"""
INSERT INTO {table_name} (name, age, gender, position)
VALUES (%s, %s, %s, %s)
"""

data = ("John Doe", 28, "Male", "Software Engineer")
cursor.execute(insert_query, data)

# Commit the transaction
connection.commit()
print("Data inserted successfully")

# Close the connection
connection.close()

Conclusion

Creating a MySQL table using Python is straightforward with the mysql-connector-python library. By following the steps outlined above, you can easily connect to a MySQL database, check for the existence of a table, create a new table, and insert data into it. This provides a solid foundation for building and managing your databases programmatically using Python.