Create Table in MySQL using Python

Creating a table is the first step to storing structured data in MySQL. This chapter shows how to define and create a MySQL table from Python using mysql-connector-python, covering column data types, common constraints, the IF NOT EXISTS guard, how to list existing tables, and how to handle errors cleanly.

Prerequisites

Before running any of the examples below, make sure you have:

Python 3.x installed
A running MySQL server
mysql-connector-python installed:

pip install mysql-connector-python

A database to work with (see MySQL Create Database if you need to create one)

Connecting to MySQL

Every operation starts with a connection. Pass your credentials and the target database name to mysql.connector.connect():

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

The cursor() object is what you use to send SQL statements to the server.

Creating a Table

Use the SQL CREATE TABLE statement inside cursor.execute() to define a new table. The example below creates a customers table with four columns:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = """
CREATE TABLE customers (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  address VARCHAR(255),
  age     INT
)
"""

mycursor.execute(sql)
print("Table created successfully.")

mycursor.close()
mydb.close()

Column types and constraints used above

Column	Type	Notes
`id`	`INT`	`AUTO_INCREMENT` generates a unique ID for each row; `PRIMARY KEY` enforces uniqueness and speeds up lookups
`name`	`VARCHAR(255)`	Variable-length string up to 255 characters; `NOT NULL` means every row must supply a value
`address`	`VARCHAR(255)`	Optional — no `NOT NULL`, so it accepts `NULL`
`age`	`INT`	Stores whole numbers

Using IF NOT EXISTS

Running CREATE TABLE on a table that already exists raises an error. Add IF NOT EXISTS to make the statement a no-op when the table is already present:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = """
CREATE TABLE IF NOT EXISTS customers (
  id      INT AUTO_INCREMENT PRIMARY KEY,
  name    VARCHAR(255) NOT NULL,
  address VARCHAR(255),
  age     INT
)
"""

mycursor.execute(sql)
print("Done — table created or already exists.")

mycursor.close()
mydb.close()

This pattern is safe to call on every application start-up without causing duplicate-table errors.

Listing Tables to Verify Creation

After creating a table, you can confirm it exists by running SHOW TABLES:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="yourusername",
  password="yourpassword",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

tables = [row[0] for row in mycursor.fetchall()]
print("Tables in database:", tables)

if "customers" in tables:
  print("customers table is ready.")
else:
  print("customers table was not found.")

mycursor.close()
mydb.close()

Example output:

Tables in database: ['customers']
customers table is ready.

Handling Errors

Wrapping your table-creation code in a try/except block lets you catch problems — such as a lost connection or a syntax error in your SQL — without crashing the program:

import mysql.connector
from mysql.connector import Error

try:
  mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
  )

  mycursor = mydb.cursor()

  sql = """
  CREATE TABLE IF NOT EXISTS customers (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    name    VARCHAR(255) NOT NULL,
    address VARCHAR(255),
    age     INT
  )
  """

  mycursor.execute(sql)
  print("Table created successfully.")

except Error as e:
  print(f"Error: {e}")

finally:
  if mydb.is_connected():
    mycursor.close()
    mydb.close()
    print("Connection closed.")

The finally block ensures the connection is always closed, even if an exception occurs.

Common Column Data Types

MySQL supports many data types. These are the ones you will use most often in Python applications:

Data type	Use case
`INT`	Whole numbers (IDs, counts, ages)
`FLOAT`, `DOUBLE`	Decimal numbers (prices, measurements)
`VARCHAR(n)`	Variable-length text up to `n` characters
`TEXT`	Long strings with no fixed maximum
`DATE`	Calendar dates (`YYYY-MM-DD`)
`DATETIME`	Date and time combined (`YYYY-MM-DD HH:MM:SS`)
`BOOLEAN`	`TRUE` / `FALSE` (stored as `TINYINT(1)`)

A More Complete Example

Here is a more realistic table that uses several data types and constraints together:

import mysql.connector
from mysql.connector import Error

try:
  mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="mydatabase"
  )

  mycursor = mydb.cursor()

  sql = """
  CREATE TABLE IF NOT EXISTS orders (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    customer_id  INT NOT NULL,
    product      VARCHAR(255) NOT NULL,
    quantity     INT NOT NULL DEFAULT 1,
    price        FLOAT NOT NULL,
    order_date   DATETIME DEFAULT CURRENT_TIMESTAMP
  )
  """

  mycursor.execute(sql)
  print("orders table created (or already exists).")

except Error as e:
  print(f"Error: {e}")

finally:
  if mydb.is_connected():
    mycursor.close()
    mydb.close()

DEFAULT 1 means quantity is set to 1 when no value is provided. DEFAULT CURRENT_TIMESTAMP automatically records when each order row is inserted.

Conclusion

To create a table in MySQL from Python:

Install mysql-connector-python and connect to your database.
Write a CREATE TABLE statement that defines each column's name, data type, and any constraints.
Use IF NOT EXISTS to avoid errors on repeated runs.
Call SHOW TABLES (or query information_schema.tables) to confirm the table was created.
Always close the cursor and connection — ideally in a finally block.

Once your table exists, you can start inserting rows (see MySQL Insert) and querying them back (see MySQL Select).