Python MySQL Tutorial: Learn Database Access from Scratch (2026)
I learned MySQL the hard way — debugging a production outage caused by an unparameterized query. That experience taught me that database access in Python isn't just about running SQL; it's about connection management, parameter binding, transaction handling, and avoiding the injection risks that come from string interpolation. The mysql-connector-python library provides a clean DB-API 2.0 interface that works across MySQL and MariaDB.
This tutorial covers the full lifecycle: connecting to a MySQL database, creating tables, performing CRUD operations, using parameterized queries, handling transactions, and managing connection pools for production applications.
Connecting to MySQL and Creating a Database
mysql.connector.connect() establishes a connection with host, user, password, and database parameters. I always use connection context managers (with statement) so connections are closed even if an error occurs. After connecting, create a cursor to execute SQL statements.
import mysql.connector
from mysql.connector import Error
try:
conn = mysql.connector.connect(
host='localhost',
user='root',
password='your_password'
)
cursor = conn.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS ecommerce")
cursor.execute("USE ecommerce")
print("Database connected and ready")
except Error as e:
print(f"Error: {e}")
finally:
if conn.is_connected():
cursor.close()
conn.close()Creating Tables with Constraints
Define tables with appropriate data types, primary keys, foreign keys, and constraints. AUTO_INCREMENT generates unique IDs. Foreign key constraints maintain referential integrity — attempting to delete a referenced parent row raises an error unless ON DELETE CASCADE is specified.
create_products = """
CREATE TABLE IF NOT EXISTS products (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(200) NOT NULL,
price DECIMAL(10, 2) NOT NULL,
stock INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)"""
create_orders = """
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
product_id INT NOT NULL,
quantity INT NOT NULL,
total DECIMAL(10, 2) NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id)
)"""
cursor.execute(create_products)
cursor.execute(create_orders)
conn.commit()CRUD Operations with Parameterized Queries
Never use f-strings or string formatting to build SQL — that's how SQL injection happens. Use %s placeholders and pass parameters as a tuple to cursor.execute(). For multiple rows, executemany() inserts them in one call.
insert_product = "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)"
product_data = ("Laptop", 999.99, 15)
cursor.execute(insert_product, product_data)
print(f"Inserted with ID: {cursor.lastrowid}")
products = [
("Mouse", 29.99, 100),
("Keyboard", 79.99, 50),
("Monitor", 299.99, 30)
]
cursor.executemany(insert_product, products)
conn.commit()
select = "SELECT * FROM products WHERE price > %s AND stock > %s"
cursor.execute(select, (100, 10))
for row in cursor.fetchall():
print(f"{row[1]}: ${row[2]} ({row[3]} in stock)")Transactions and Error Handling
Transactions group multiple operations into one atomic unit. conn.start_transaction() or simply set conn.autocommit = False. If any operation fails, rollback() reverts all changes. commit() persists them. I always wrap transactional blocks in try/except and rollback on error.
try:
conn.start_transaction()
cursor = conn.cursor()
cursor.execute(
"UPDATE products SET stock = stock - %s WHERE id = %s",
(1, 1)
)
cursor.execute(
"INSERT INTO orders (product_id, quantity, total) VALUES (%s, %s, %s)",
(1, 1, 999.99)
)
conn.commit()
print("Transaction committed")
except Error as e:
conn.rollback()
print(f"Transaction failed, rolled back: {e}")Connection Pooling for Production
Opening a new database connection for every request is slow and exhausts server resources. MySQLConnectionPool maintains a pool of reusable connections. Acquire a connection with pool.get_conn(), use it, then return it with conn.close() (which returns it to the pool).
from mysql.connector.pooling import MySQLConnectionPool
pool = MySQLConnectionPool(
pool_name='mypool',
pool_size=10,
pool_reset_session=True,
host='localhost',
database='ecommerce',
user='root',
password='your_password'
)
try:
conn = pool.get_conn()
cursor = conn.cursor()
cursor.execute("SELECT COUNT(*) FROM products")
count = cursor.fetchone()[0]
print(f"Total products: {count}")
except Error as e:
print(f"Pool error: {e}")
finally:
if conn.is_connected():
conn.close()Using MySQL with Pandas and Exporting
Pandas makes it trivial to read query results directly into a DataFrame with pd.read_sql(). This is my standard workflow for analysis: run SQL to aggregate data, load into Pandas, then visualize with Matplotlib or Seaborn.
import pandas as pd
query = """
SELECT p.name, SUM(o.total) as revenue, COUNT(o.id) as order_count
FROM products p
JOIN orders o ON p.id = o.product_id
GROUP BY p.name
ORDER BY revenue DESC
"""
df = pd.read_sql(query, conn)
print(df.head())
df.head(10).plot(kind='bar', x='name', y='revenue')
plt.title('Top Products by Revenue')
plt.tight_layout()
plt.show()
df.to_sql('product_summary', conn, if_exists='replace', index=False)Frequently Asked Questions
Should I use mysql-connector-python or PyMySQL?
Both implement the DB-API 2.0 spec. mysql-connector-python is the official Oracle connector. PyMySQL is pure Python and easier to install. I recommend PyMySQL for compatibility and mysql-connector for performance.
How do I handle character encoding issues?
Set charset='utf8mb4' in the connect call and ensure the database and tables use utf8mb4 as the default charset.
What is the difference between autocommit and explicit transactions?
With autocommit=True, each DML statement is committed immediately. With explicit transactions, you control when changes are committed, allowing rollback on error.
How do I avoid 'MySQL has gone away' errors?
Use connection pooling, set wait_timeout and interactive_timeout in MySQL config, and add retry logic. Pooled connections are automatically validated before use.
Originally published on Ayodhyyya. Last updated June 1, 2026.