Python MySQL Create Table

Tables are created inside a database using a CREATE TABLE SQL statement, run through Python exactly the same way any other SQL command is -- construct the SQL string describing the table's columns and types, then pass it to cursor.execute().

In this page:

Basic Table Creation
Choosing Column Data Types
Checking If a Table Already Exists
Using SHOW TABLES to Confirm Creation
Foreign Keys and Table Relationships

Basic Table Creation

cursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))") defines a new table with three columns: an auto-incrementing integer primary key, and two text columns for name and address.

Note: Always give a table a PRIMARY KEY column -- it makes every later update, delete, or targeted lookup dramatically simpler and faster.

Warning: Column definitions must be separated by commas and the entire column list wrapped in parentheses -- a missing comma or parenthesis is a very common SQL syntax error.

Example: Basic Table Creation

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, "
    "name VARCHAR(255), address VARCHAR(255))"
)
print("Table created")

Choosing Column Data Types

MySQL offers specific column types for different kinds of data -- INT for whole numbers, VARCHAR(n) for variable-length text up to n characters, TEXT for longer text, DATE/DATETIME for dates, and DECIMAL for exact-precision numbers like currency.

Note: Use DECIMAL, not FLOAT, for monetary values -- floating-point types can introduce tiny rounding errors that matter when dealing with money.

Warning: VARCHAR requires a maximum length argument, like VARCHAR(255) -- omitting it is a syntax error, unlike TEXT which has no length argument.

Example: Choosing Column Data Types

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "CREATE TABLE orders (id INT, price DECIMAL(10,2), placed_on DATE)"
)
print("DECIMAL used for money, not FLOAT")

Checking If a Table Already Exists

CREATE TABLE IF NOT EXISTS tableName (...) is the table-level equivalent of CREATE DATABASE IF NOT EXISTS -- it silently does nothing if a table with that name already exists, instead of raising an error, which is useful in repeatable setup scripts.

Note: Use IF NOT EXISTS for any setup script meant to run more than once, such as an application's first-run initialization logic.

Warning: IF NOT EXISTS does not check whether the EXISTING table's structure matches what you are trying to create -- it just skips creation entirely if a table with that name exists at all.

Example: Checking If a Table Already Exists

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("CREATE TABLE IF NOT EXISTS customers (id INT PRIMARY KEY)")
print("Safe to run more than once")

Using SHOW TABLES to Confirm Creation

cursor.execute("SHOW TABLES") followed by iterating over the cursor lists every table in the currently selected database, a quick way to confirm a CREATE TABLE statement actually succeeded.

Note: Run SHOW TABLES right after any table-creation logic during development, to catch structural mistakes immediately instead of discovering them later when inserting data.

Warning: SHOW TABLES only lists table names, not their column structure -- use DESCRIBE tableName for that level of detail.

Example: Using SHOW TABLES to Confirm Creation

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute("SHOW TABLES")
cursor.__iter__.return_value = iter([("customers",), ("orders",)])
for table in cursor:
    print(table)

Foreign Keys and Table Relationships

A FOREIGN KEY constraint links a column in one table to the primary key of another, enforcing referential integrity -- MySQL will reject an insert that references a non-existent parent row, keeping related tables consistent with each other.

Note: Design your foreign key relationships (which table references which) before creating tables, since the referenced (parent) table must already exist before the referencing (child) table can be created.

Warning: Both the foreign key column and the referenced primary key column generally need matching, compatible data types, or the CREATE TABLE statement will fail.

Example: Foreign Keys and Table Relationships

python

from unittest.mock import MagicMock

conn = MagicMock()
cursor = conn.cursor()
cursor.execute(
    "CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT, "
    "FOREIGN KEY (customer_id) REFERENCES customers(id))"
)
print("orders.customer_id must reference an existing customers.id")

Common Mistakes

Forgetting to specify a PRIMARY KEY column, which makes it harder to uniquely identify and later update or delete individual rows.
Mismatching Python data types and MySQL column types -- for instance storing very large numbers in an INT column, which has a fixed maximum size.
Running CREATE TABLE against a connection that is not pointed at any specific database, causing a 'No database selected' error.

Chapter Summary

cursor.execute("CREATE TABLE name (...)") creates a new table with the given column definitions.
Every table generally needs a PRIMARY KEY column to uniquely identify each row.
AUTO_INCREMENT makes an integer primary key generate its own value automatically for each new row.

Browser Support

CREATE TABLE syntax is standard MySQL SQL, executed identically across all Python driver versions.

🔒

Chapter Quiz — Complete all 12 topics to unlock

0/12 topics done

Complete these topics first:

← Previous Lesson Python MySQL Create Database Next Lesson → Python MySQL Insert Into Table

Python MySQL Create Table

Basic Table Creation

Choosing Column Data Types

Checking If a Table Already Exists

Using SHOW TABLES to Confirm Creation

Foreign Keys and Table Relationships

Chapter Quiz — Complete all 12 topics to unlock

Login to run this code