Python MySQL Table Creation – Step-by-Step Guide

To create a table in MySQL using Python, you typically use the mysql-connector-python or PyMySQL package. Here's how you can do it using mysql-connector-python (most commonly used):

1. Install MySQL Connector (if not already installed)

pip install mysql-connector-python

2. Connect to MySQL Database

import mysql.connector

# Establish connection
conn = mysql.connector.connect(
    host="localhost",
    user="your_username",
    password="your_password",
    database="your_database_name"
)

# Create cursor object
cursor = conn.cursor()

3. Create a Table

# Define your SQL query
create_table_query = """
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
)
"""

# Execute the query
cursor.execute(create_table_query)

print("Table 'students' created successfully.")

4. Close the Connection

# Close cursor and connection
cursor.close()
conn.close()

Full Example

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="123456",
    database="school"
)

cursor = conn.cursor()

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
)
""")

print("Table created successfully.")

cursor.close()
conn.close()

Notes:

• Always use IF NOT EXISTS in CREATE TABLE to avoid errors if the table already exists.
• Ensure the database (school in this case) exists before creating the table.