Python MySQL Tutorial – How to Using Create a Table Python
Last updated 5 months, 1 week ago | 462 views 75 5

Creating tables is a fundamental step when working with relational databases like MySQL. In this tutorial, you'll learn how to create MySQL tables using Python and the mysql-connector-python
package. We'll walk through each step with code snippets, best practices, and a complete example.
Table of Contents
-
Prerequisites
-
Installing MySQL Connector
-
Connecting to MySQL Database
-
Creating a Table with SQL
-
Verifying Table Creation
-
Full Working Example
-
Common Pitfalls
-
Tips and Best Practices
✅ 1. Prerequisites
Before starting, ensure you have:
-
Python installed on your machine
-
MySQL server running locally or remotely
-
MySQL credentials (username and password)
-
A database already created (see Python MySQL Create Database Tutorial)
2. Installing MySQL Connector
Install the MySQL Connector package using pip:
pip install mysql-connector-python
This library allows Python to communicate with your MySQL database.
3. Connecting to a MySQL Database
You'll first need to connect to an existing database where your table will reside.
import mysql.connector
db = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="mydatabase"
)
cursor = db.cursor()
print("Connected to database!")
✅ Replace
"your_username"
,"your_password"
, and"mydatabase"
with your actual MySQL credentials and target database name.
4. Creating a Table with SQL
Use the CREATE TABLE
SQL command to define your table schema. Here's a basic example:
cursor.execute("""
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
)
""")
print("Table 'users' created successfully.")
This table has:
-
id
: a unique, auto-incrementing integer -
name
: a string up to 100 characters -
email
: a string up to 100 characters -
age
: an integer
5. Verifying Table Creation
You can check all existing tables in the current database with:
cursor.execute("SHOW TABLES")
for table in cursor:
print(table)
This will list all tables, including the one you just created.
6. Full Working Example
Here's a complete Python script that connects to a MySQL database and creates a table:
import mysql.connector
from mysql.connector import Error
def create_table():
try:
# Connect to the database
db = mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="mydatabase"
)
cursor = db.cursor()
# Create table
cursor.execute("""
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
)
""")
print("Table 'users' created successfully.")
except Error as e:
print("Error:", e)
finally:
if db.is_connected():
db.close()
print("Connection closed.")
# Run the function
create_table()
⚠️ 7. Common Pitfalls
Issue | Solution |
---|---|
1049 (42000): Unknown database |
Ensure the database exists before connecting |
1050 (42S01): Table already exists |
Use CREATE TABLE IF NOT EXISTS |
Wrong column types | Validate your SQL syntax and data types |
Not closing connection | Always close connection in a finally block |
8. Tips and Best Practices
-
✅ Always use
CREATE TABLE IF NOT EXISTS
to avoid errors if the table already exists. -
✅ Use consistent naming conventions (
snake_case
orcamelCase
). -
✅ Keep column names descriptive and meaningful.
-
✅ Define
PRIMARY KEY
and useAUTO_INCREMENT
for IDs. -
✅ Separate database logic into functions for better structure and testing.
Example with IF NOT EXISTS
:
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
)
""")
Summary Table
Task | Code |
---|---|
Install connector | pip install mysql-connector-python |
Connect to MySQL | mysql.connector.connect() |
Create table | cursor.execute("CREATE TABLE ...") |
Show tables | cursor.execute("SHOW TABLES") |
Handle errors | Use try-except block with finally |
Final Thoughts
Creating tables in MySQL using Python is an essential skill for any backend or full-stack developer. With mysql-connector-python
, you can programmatically manage your databases and tables with ease. Follow best practices to ensure clean, scalable, and error-free database integration.