2026-03-015 min read

MySQL Create Table (Python Programming)

Q: Q: How do I close the connection to my MySQL server after finishing my work?

To close the connection, use the `close()` method of the connection object: ```python mydb.close() ```

Learn MySQL Create Table (Python Programming) step by step with clear examples and exercises.

Title: MySQL Create Table (Python Programming) - Expanded Version

Why This Matters

In this tutorial, we will delve into creating a table in MySQL using Python programming. Mastering this skill is crucial for database management and development, making it indispensable for various applications such as web development, data analysis, machine learning projects, and more. Understanding the process of creating tables will help you handle real-world scenarios like fixing bugs, optimizing performance, or even preparing for technical interviews.

Prerequisites

To follow this tutorial, you should have a basic understanding of Python programming and SQL (Structured Query Language). Familiarity with MySQL is not required as we will cover the necessary concepts along the way.

Python:
MySQL:
Install MySQL Connector for Python (MySQL-python or mysqlclient): pip install mysql-connector-python or pip install mysqlclient

Before we dive into creating tables, let's cover some essential SQL concepts and data types:

Data Types: MySQL supports various data types such as INT (integer), VARCHAR (variable-length string), DECIMAL (decimal number), DATE (date), TIMESTAMP (timestamp), and more.

Primary Key: A primary key is a column or set of columns in a table that uniquely identifies each row. In our examples, we will use the AUTO_INCREMENT attribute to automatically assign unique IDs to each row.

Foreign Key: A foreign key is a reference to the primary key of another table, used for establishing relationships between tables. We won't cover creating foreign keys in this tutorial but encourage you to explore them further if needed.

Core Concept

To create a table in MySQL using Python, we will use the mysql.connector module. First, let's establish a connection to our MySQL server:

import mysql.connector

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

mycursor = mydb.cursor()

Now that we have a connection, let's create a table named employees. Here is the SQL query to create the table:

CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(255) NOT NULL,
lastname VARCHAR(255) NOT NULL,
email VARCHAR(255),
department VARCHAR(255),
salary DECIMAL(10, 2)
);

In the employees table, we have added a new column called salary to store employees' salaries. The DECIMAL(10, 2) data type is used for storing decimal numbers with up to 10 digits (total) and 2 digits after the decimal point.

To execute the SQL query using Python, we will use the execute() method of the cursor object:

mycursor.execute("CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(255) NOT NULL, lastname VARCHAR(255) NOT NULL, email VARCHAR(255), department VARCHAR(255), salary DECIMAL(10, 2))")

Worked Example

Let's create a table named products with columns for product ID, name, price, description, and stock:

mycursor.execute("CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) NOT NULL, description TEXT, stock INT)")

Now that the table is created, let's insert some data into it:

mycursor.execute("INSERT INTO products (name, price, description, stock) VALUES ('Laptop', 800.00, 'A high-performance laptop', 100)")
mycursor.execute("INSERT INTO products (name, price, description, stock) VALUES ('Smartphone', 600.00, 'A top-of-the-line smartphone', 200)")

To commit the changes and save the data to our MySQL server, we use the commit() method of the connection object:

mydb.commit()

Common Mistakes

Forgetting to import mysql.connector: Make sure you have imported the necessary module at the beginning of your Python script.

Incorrect database, username, or password: Double-check that you've entered the correct details for your MySQL server connection.

Table already exists: If you try to create a table that already exists in the database, you will encounter an error. To avoid this, use the execute() method with the IF NOT EXISTS keyword:

CREATE TABLE IF NOT EXISTS employees (id INT AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(255) NOT NULL, lastname VARCHAR(255) NOT NULL, email VARCHAR(255), department VARCHAR(255), salary DECIMAL(10, 2))

Incorrect data types: Ensure that the data types you use for each column match the expected data in your table. For example, using VARCHAR(255) for numeric data will cause errors when inserting or querying data.

Not committing changes: After executing SQL queries, don't forget to commit the changes to save them to the MySQL server:

mydb.commit()

Not closing the connection: To close the connection to your MySQL server, use the close() method of the connection object:

mydb.close()

Practice Questions

Create a table named customers with columns for customer ID, name, phone number (using the VARCHAR data type), and date of birth (using the DATE data type).

Insert some data into the products table created earlier.

Update the price of a specific product in the products table (e.g., update the price of 'Laptop' to 900.00).

Delete a product from the products table with a specific ID (e.g., delete the product with ID 1).

Create an index on the name column of the employees table for faster search operations.

FAQ

Q: Can I create multiple tables in one Python script?

A: Yes, you can create and manipulate multiple tables within the same Python script by executing separate SQL queries for each table.

Q: How do I close the connection to my MySQL server after finishing my work?

A: To close the connection, use the close() method of the connection object:

mydb.close()

Q: What are some common data types in MySQL?

A: Some common data types in MySQL include INT (integer), VARCHAR (variable-length string), DECIMAL (decimal number), DATE (date), TIMESTAMP (timestamp), and more.

Q: How do I handle errors when executing SQL queries using Python?

A: You can use exception handling to catch errors that may occur while executing SQL queries. Here's an example:

try:
mycursor.execute("CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(255) NOT NULL, lastname VARCHAR(255) NOT NULL, email VARCHAR(255), department VARCHAR(255), salary DECIMAL(10, 2))")
except mysql.connector.Error as error:
print("Error while creating table:", error)