Python MySQL Tutorial – How to Using Create a Table Python

Last updated 5 months, 1 week ago | 462 views 75     5

Tags:- MySQL Python

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

  1. Prerequisites

  2. Installing MySQL Connector

  3. Connecting to MySQL Database

  4. Creating a Table with SQL

  5. Verifying Table Creation

  6. Full Working Example

  7. Common Pitfalls

  8. 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 or camelCase).

  • ✅ Keep column names descriptive and meaningful.

  • ✅ Define PRIMARY KEY and use AUTO_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.