Connect Python to MySQL: Complete Guide

Connecting Python to MySQL allows a program to store information permanently, search records, update data, and build real applications such as inventory systems, dashboards, registration forms, and web services. The connection itself is simple, but production-quality code must also protect credentials, use parameterized queries, close resources, and handle transactions correctly.

This guide walks through a complete Python MySQL connection using a direct connector. You will create a database and table, insert and read data, update and delete records, process errors, and see when SQLAlchemy may be a better option.

What You Need

Before writing Python code, install and start a MySQL server. You also need a database user with only the permissions required by the application. Avoid developing with a highly privileged administrative account.

Install the official connector in an isolated environment:

python -m venv .venv
# Windows
.venv\Scripts\activate
# macOS or Linux
source .venv/bin/activate

python -m pip install mysql-connector-python

The guide to Python virtual environments explains why project isolation matters. For connector details, consult the official MySQL Connector/Python documentation. SQL syntax and server behavior are documented in the official MySQL manual.

Create a Basic Connection

Import the connector and pass the server address, user, password, and database name. A local development server commonly uses localhost.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password",
    database="store_db",
)

print("Connected:", connection.is_connected())
connection.close()

Closing the connection releases server resources. In a larger application, put cleanup in a finally block so it also runs after an exception.

Keep Credentials Outside the Code

Never publish a real password in a source file or Git repository. Load credentials from environment variables. This keeps the same code usable in development, testing, and production.

import os
import mysql.connector

connection = mysql.connector.connect(
    host=os.getenv("DB_HOST", "localhost"),
    user=os.environ["DB_USER"],
    password=os.environ["DB_PASSWORD"],
    database=os.environ["DB_NAME"],
)

Environment variables are not a complete security system, but they prevent the most common accidental exposure. Add local secret files to .gitignore, restrict the database account, and rotate any credential that was committed by mistake.

Create a Database

If the database does not exist, connect without the database argument and create it. The user must have permission to do so.

import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="app_admin",
    password="your_password",
)

cursor = connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS store_db")

cursor.close()
connection.close()

In production, infrastructure or migration tools normally create databases. Application code should usually work inside an existing database with limited privileges.

Create a Table

Connect to the database and create a table for products. Constraints protect data even when a bug reaches the database.

connection = mysql.connector.connect(
    host="localhost",
    user="app_user",
    password="your_password",
    database="store_db",
)

cursor = connection.cursor()

cursor.execute("""
    CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(120) NOT NULL,
        price DECIMAL(10, 2) NOT NULL,
        stock INT NOT NULL DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
""")

connection.commit()
cursor.close()
connection.close()

DECIMAL is usually preferable to an approximate floating-point type for money. The primary key identifies each record, and NOT NULL prevents incomplete products.

Insert Data Safely

Use placeholders instead of joining user values into SQL strings. Parameterized queries reduce the risk of SQL injection and handle escaping correctly.

sql = "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)"
values = ("Mechanical keyboard", 89.90, 12)

cursor.execute(sql, values)
connection.commit()

print("New product ID:", cursor.lastrowid)

Do not write this:

# Unsafe: never build a query with raw user text
sql = f"INSERT INTO products (name) VALUES ('{user_text}')"

The same principle applies to filters, updates, and deletions.

Insert Multiple Rows

Use executemany() when inserting several records. It is cleaner and normally more efficient than sending each row separately.

products = [
    ("Mouse", 29.90, 20),
    ("Monitor", 249.00, 6),
    ("USB cable", 9.50, 50),
]

sql = "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)"
cursor.executemany(sql, products)
connection.commit()

print(cursor.rowcount, "products inserted")

Read Records with SELECT

A cursor executes the query and exposes the result. fetchone() returns one row, fetchmany() returns a limited group, and fetchall() loads all remaining rows.

cursor = connection.cursor(dictionary=True)

cursor.execute("""
    SELECT id, name, price, stock
    FROM products
    WHERE stock > %s
    ORDER BY name
""", (0,))

for product in cursor.fetchall():
    print(product["id"], product["name"], product["price"])

A dictionary cursor makes columns easier to access by name. For a very large result, process rows in batches rather than loading everything into memory.

Filter One Product

product_id = 3

cursor.execute(
    "SELECT id, name, price, stock FROM products WHERE id = %s",
    (product_id,),
)

product = cursor.fetchone()

if product is None:
    print("Product not found")
else:
    print(product)

The comma in (product_id,) creates a one-item tuple. The Python None guide explains why is None is the correct missing-value check.

Update Records

sql = "UPDATE products SET price = %s, stock = %s WHERE id = %s"
values = (94.90, 15, 1)

cursor.execute(sql, values)
connection.commit()

print(cursor.rowcount, "row updated")

Always include a deliberate WHERE condition. An update without it affects every row in the table. Check rowcount when the application expects exactly one change.

Delete Records

product_id = 4

cursor.execute("DELETE FROM products WHERE id = %s", (product_id,))
connection.commit()

print(cursor.rowcount, "row deleted")

Destructive operations deserve extra validation. Many systems use a soft-delete flag instead of immediately removing important business records.

Transactions, Commit, and Rollback

A transaction groups changes into one logical operation. Consider an order that reduces stock and creates an order row. Both steps must succeed together. If the second step fails, rollback should undo the first.

try:
    connection.start_transaction()

    cursor.execute(
        "UPDATE products SET stock = stock - %s WHERE id = %s AND stock >= %s",
        (2, 1, 2),
    )

    if cursor.rowcount != 1:
        raise ValueError("Insufficient stock")

    cursor.execute(
        "INSERT INTO orders (product_id, quantity) VALUES (%s, %s)",
        (1, 2),
    )

    connection.commit()
except Exception:
    connection.rollback()
    raise

Catch specific exceptions when you can. The Python exception-handling guide covers specific exception types, else, finally, and re-raising.

Handle Connector Errors

import mysql.connector
from mysql.connector import Error

connection = None
cursor = None

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="app_user",
        password="your_password",
        database="store_db",
        connection_timeout=10,
    )
    cursor = connection.cursor(dictionary=True)
    cursor.execute("SELECT id, name FROM products")
    print(cursor.fetchall())
except Error as exc:
    print(f"Database error: {exc}")
finally:
    if cursor is not None:
        cursor.close()
    if connection is not None and connection.is_connected():
        connection.close()

In real services, use structured logging instead of printing sensitive error details to end users. Keep enough context for diagnosis without recording passwords or confidential values.

A Small CRUD Repository

Wrapping repeated SQL operations in a class keeps the rest of the program focused on business rules. This example uses a connection factory and closes each cursor immediately.

class ProductRepository:
    def __init__(self, connection):
        self.connection = connection

    def create(self, name, price, stock):
        cursor = self.connection.cursor()
        try:
            cursor.execute(
                "INSERT INTO products (name, price, stock) VALUES (%s, %s, %s)",
                (name, price, stock),
            )
            self.connection.commit()
            return cursor.lastrowid
        except Exception:
            self.connection.rollback()
            raise
        finally:
            cursor.close()

    def get_by_id(self, product_id):
        cursor = self.connection.cursor(dictionary=True)
        try:
            cursor.execute(
                "SELECT id, name, price, stock FROM products WHERE id = %s",
                (product_id,),
            )
            return cursor.fetchone()
        finally:
            cursor.close()

    def update_stock(self, product_id, stock):
        cursor = self.connection.cursor()
        try:
            cursor.execute(
                "UPDATE products SET stock = %s WHERE id = %s",
                (stock, product_id),
            )
            self.connection.commit()
            return cursor.rowcount
        except Exception:
            self.connection.rollback()
            raise
        finally:
            cursor.close()

    def delete(self, product_id):
        cursor = self.connection.cursor()
        try:
            cursor.execute("DELETE FROM products WHERE id = %s", (product_id,))
            self.connection.commit()
            return cursor.rowcount
        except Exception:
            self.connection.rollback()
            raise
        finally:
            cursor.close()

This organization connects naturally with object-oriented Python and makes database code easier to test.

Use SQLAlchemy When Appropriate

A direct connector gives precise control and is excellent for learning SQL. SQLAlchemy adds connection management, a database abstraction layer, and an object-relational mapper. It can reduce repetitive code in larger applications.

python -m pip install sqlalchemy mysql-connector-python

from sqlalchemy import create_engine, text

engine = create_engine(
    "mysql+mysqlconnector://app_user:password@localhost/store_db",
    pool_pre_ping=True,
)

with engine.begin() as connection:
    rows = connection.execute(
        text("SELECT id, name FROM products WHERE stock > :minimum"),
        {"minimum": 0},
    )
    for row in rows:
        print(row)

Do not hardcode the production URL. Build it from protected configuration. The official SQLAlchemy documentation explains engines, sessions, transactions, and ORM models.

Common Connection Problems

• Access denied: verify username, password, host permissions, and account privileges.
• Unknown database: create the database or correct its name.
• Connection refused: confirm that MySQL is running and listening on the expected host and port.
• ModuleNotFoundError: activate the correct virtual environment and install the connector there.
• Unread result: fetch or discard all rows before reusing some cursor configurations.
• Too many connections: close connections and use a pool in long-running applications.

The article on fixing ModuleNotFoundError helps with connector installation issues.

Best Practices Checklist

• Use a dedicated account with minimum permissions.
• Keep credentials outside source code.
• Use placeholders for every external value.
• Validate input before executing a query.
• Commit only complete operations and rollback failures.
• Close cursors and connections.
• Add indexes for columns used frequently in filters and joins.
• Paginate large queries instead of loading every record.
• Use migrations to track schema changes.
• Back up important data and test restoration.

Frequently Asked Questions

Which package should beginners use?

mysql-connector-python is a straightforward choice and has official MySQL documentation.

Why should I avoid f-strings in SQL queries?

Raw interpolation can allow SQL injection and may escape values incorrectly. Use the connector’s placeholders.

When do I call commit?

Call it after a logical group of insert, update, or delete operations succeeds. Use rollback after a failure.

Should one connection stay open forever?

Not usually. Long-running applications should use managed connection pools and return connections promptly.

Do I need SQLAlchemy?

No. Direct SQL is sufficient for many programs. SQLAlchemy becomes useful when an application grows and needs pooling, reusable models, or database abstraction.

Conclusion

A reliable Python MySQL connection involves more than opening a socket. Build the connection from protected configuration, use parameterized SQL, define constraints, manage transactions, and close resources. Start with the direct connector to understand CRUD operations, then consider SQLAlchemy when the project needs a higher-level architecture.