Creating Tables

Before storing data, you need tables. CREATE TABLE defines the structure.

cursor.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        age INTEGER
    )
""")
conn.commit()

Each column has a name and type. Common SQLite types:

• INTEGER - whole numbers
• REAL - decimal numbers
• TEXT - strings
• BLOB - binary data

Constraints add rules:

• PRIMARY KEY - unique identifier for each row
• NOT NULL - value is required
• UNIQUE - no duplicates allowed
• AUTOINCREMENT - auto-assign increasing IDs

IF NOT EXISTS prevents errors when the table already exists.

I cover table design in my SQL with Python course.