Python MySQL Create Table

To create a table in a MySQL database using Python, follow these steps:

1. Install MySQL Connector

If you haven’t already installed the MySQL connector, run this command:

pip install mysql-connector-python

2. Connect to the MySQL Database

First, you need to connect to the database where you want to create the table.

Example Python Script to Create a Table

import mysql.connector

# Connect to the MySQL database
mydb = mysql.connector.connect(
  host="localhost",         # Change this to your MySQL host if needed
  user="yourusername",       # Change this to your MySQL username
  password="yourpassword",   # Change this to your MySQL password
  database="mynewdatabase"   # Change this to your database name
)

# Create a cursor object
mycursor = mydb.cursor()

# Create a table
mycursor.execute("""
    CREATE TABLE customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255),
        address VARCHAR(255)
    )
""")

# Verify if the table was created
mycursor.execute("SHOW TABLES")

for table in mycursor:
    print(table)

Explanation:

• mysql.connector.connect(): Establishes a connection to the MySQL server using the provided host, username, password, and database name.
• mycursor.execute(): Executes the SQL command to create a new table called customers with three columns: id (an auto-incremented integer that serves as the primary key), name (a string up to 255 characters), and address (a string up to 255 characters).
• SHOW TABLES: Lists all tables in the database to verify that the customers table was created successfully.

3. Example of Adding More Columns to the Table

You can modify the CREATE TABLE statement to include other types of columns. For example:

mycursor.execute("""
    CREATE TABLE orders (
        order_id INT AUTO_INCREMENT PRIMARY KEY,
        product_name VARCHAR(255),
        quantity INT,
        price DECIMAL(10, 2),
        order_date DATE
    )
""")

This example creates a table called orders with the following columns:

• order_id: Auto-incrementing primary key.
• product_name: Name of the product (VARCHAR).
• quantity: Number of items (INT).
• price: Price with two decimal places (DECIMAL).
• order_date: Date of the order (DATE).

4. Closing the Connection

Once you’re done with your operations, it’s a good practice to close the connection:

mydb.close()

This script will create the table in your specified database and verify it by listing all tables.