Python MySQL Create Table

📊 Create Your First Table!

A table is like a spreadsheet with columns and rows. Let's make one to store student information!


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

Columns

& Rows

Like

Excel

Easy

To Make

What is a Table?

A table is like:

📋 A spreadsheet with columns and rows
📇 A contact list with name, phone, email
📚 A class roster with student names and grades
🏪 A product list with item, price, quantity

Create a Simple Table

Basic Table Creation


import mysql.connector

# Connect to database
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpass',
    database='my_school'  # Use our database
)

cursor = connection.cursor()

# Create students table
cursor.execute("""
CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(150)
)
""")

print("✅ Table 'students' created!")

cursor.close()
connection.close()

Simple Data Types

🔢

INT

Whole numbers
Like: 1, 25, 100

📝

VARCHAR(n)

Text up to n letters
Like: "John", "Hello"

📅

DATE

Dates
Like: 2024-01-15

💰

DECIMAL

Money/precise numbers
Like: 19.99, 100.50

Safe Way to Create Tables

Safe Table Creation


import mysql.connector

try:
    connection = mysql.connector.connect(
        host='localhost',
        user='root',
        password='yourpass',
        database='my_school'
    )
    
    cursor = connection.cursor()
    
    # Safe way - won't crash if table exists
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        age INT,
        email VARCHAR(150)
    )
    """)
    
    print("✅ Students table is ready!")
    
except mysql.connector.Error as error:
    print(f"❌ Error: {error}")
    
finally:
    if connection.is_connected():
        cursor.close()
        connection.close()

Real Example: Online Store

Products Table


import mysql.connector

connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpass',
    database='my_store'
)

cursor = connection.cursor()

# Create products table
cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2),
    quantity INT DEFAULT 0,
    created_date DATE
)
""")

print("✅ Products table created!")

cursor.close()
connection.close()

See Your Tables

List All Tables


import mysql.connector

connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpass',
    database='my_school'
)

cursor = connection.cursor()

# Show all tables
cursor.execute("SHOW TABLES")
tables = cursor.fetchall()

print("📊 Your tables:")
for table in tables:
    print(f"  - {table[0]}")

# Show table structure
cursor.execute("DESCRIBE students")
columns = cursor.fetchall()

print("\n📋 Students table structure:")
for column in columns:
    print(f"  {column[0]}: {column[1]}")

cursor.close()
connection.close()

Create Multiple Tables

School Database Tables


import mysql.connector

connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='yourpass',
    database='my_school'
)

cursor = connection.cursor()

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

# Teachers table
cursor.execute("""
CREATE TABLE IF NOT EXISTS teachers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(50),
    salary DECIMAL(10, 2)
)
""")

# Classes table
cursor.execute("""
CREATE TABLE IF NOT EXISTS classes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    class_name VARCHAR(100) NOT NULL,
    room_number VARCHAR(10)
)
""")

print("✅ All school tables created!")
print("📊 Tables: students, teachers, classes")

cursor.close()
connection.close()

Important Table Parts

🔑 PRIMARY KEY

Unique ID for each row (like student ID number)

🔄 AUTO_INCREMENT

Automatically creates ID numbers: 1, 2, 3...

❌ NOT NULL

This field must have a value (can't be empty)

✅ DEFAULT

Sets a default value if none is given

💡 Beginner Tips

Always use PRIMARY KEY: Every table needs a unique ID
Use AUTO_INCREMENT: Let MySQL create ID numbers automatically
VARCHAR for text: Use VARCHAR(100) for names, VARCHAR(150) for emails
INT for numbers: Use INT for ages, quantities, etc.
Use IF NOT EXISTS: Prevents errors if table already exists