← Back to Python Course | Chapter 15: Python & MySQL | Lesson 3 of 12

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:

  1. Basic Table Creation
  2. Choosing Column Data Types
  3. Checking If a Table Already Exists
  4. Using SHOW TABLES to Confirm Creation
  5. Foreign Keys and Table Relationships
Syntax
python 
cursor.execute("CREATE TABLE table_name (column1 type, column2 type)")

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, "  # auto-incrementing 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)"  # DECIMAL for exact-precision currency values
)
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)")  # no error if the table already exists
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")  # lists every table in the current database
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))"  # enforces that customer_id must reference a real customer
)
print("orders.customer_id must reference an existing customers.id")
Related Topics

Python MySQL Create Database · Python MySQL Insert Into Table · Python MySQL Select From Table · Python MySQL Drop Table

{# common_mistakes/chapter_summary/browser_support: on Hindi pages the view already swaps in the hi_ translation fields (or blanks these out if untranslated), so this renders correctly for both languages without a lang_code check here. #}
Common Mistakes
  1. Forgetting to specify a PRIMARY KEY column, which makes it harder to uniquely identify and later update or delete individual rows.
  2. Mismatching Python data types and MySQL column types -- for instance storing very large numbers in an INT column, which has a fixed maximum size.
  3. 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

Login to run this code

C/C++/Java/PHP execution requires a free account. Your code is saved — you'll land right back in the editor after logging in.