Connect Python to a Database (MySQL Example): The Complete 2026 Guide

If you’ve ever built a Python script that needed to “remember” something after it finished running, you’ve already run into the problem that databases solve. Variables and lists disappear the moment your program closes. A database doesn’t.

This guide walks you through exactly how to connect Python to a database (MySQL example) — from installing the right tools, to writing your first connection script, to building a small real-world project you can actually reuse. Every code example below has been tested against the current mysql-connector-python release line (9.x, the official Oracle-maintained driver) running on Python 3.10 through 3.14, so you’re not learning a workflow that’s already outdated.

By the end, you’ll be able to confidently read, write, update, and delete data from a MySQL database using Python — and, just as importantly, you’ll know how to do it safely.

What You’ll Learn in This Guide

What a database is and why MySQL pairs so well with Python
How to install MySQL Server, MySQL Workbench, and Python itself
How to choose between mysql-connector-python, PyMySQL, and other options
How to write a real connection script using the Connection and Cursor objects
How to perform full CRUD operations (Create, Read, Update, Delete)
How to use parameterized queries to prevent SQL injection
How transactions, commit, and rollback keep your data consistent
How to handle errors, close connections properly, and use connection pooling
A complete mini project: an Employee Management CLI tool
Common mistakes, troubleshooting tips, and a 10-question FAQ

Why Connect Python to a Database?

Python is excellent at processing data, but on its own it has no memory beyond a single program run. The moment your script ends, every variable is gone. A database gives your application persistent, structured storage that survives restarts, supports multiple users at once, and can be queried in powerful ways that a plain text file simply can’t match.

Common Use Cases

Web applications that store user accounts, products, or orders
Automation scripts that log results over time (for example, a script that checks prices daily and needs to compare today’s value against yesterday’s)
Data pipelines that pull, clean, and store information for later analysis
Admin dashboards and internal tools that read and write business data

If you’re already automating repetitive tasks with Python, a database is often the missing piece. For instance, if you’re scheduling a script that runs automate daily tasks on your computer using Python-style, you’ll usually want it to log its results to a database instead of just printing them to the screen. The same applies if you’re building something that sends emails automatically using Python — you’ll often want to pull the recipient list or email content from a database table rather than hardcoding it.

Why MySQL Specifically?

MySQL remains one of the most widely used open-source relational databases in the world, and for good reason:

It’s free, mature, and extremely well documented
It’s the default or supported database for most shared hosting and cloud platforms
It has a huge ecosystem of tools (like MySQL Workbench) and a massive community
Python’s official and community-maintained MySQL drivers are stable and actively developed

Pro Tip: Everything you learn connecting Python to MySQL transfers almost directly to PostgreSQL, SQLite, or SQL Server later on — the SQL syntax differs slightly, but the pattern (connect → cursor → execute → commit → close) is the same everywhere.

Prerequisites Before You Start

Python Knowledge You Should Have

You don’t need to be an expert, but you should be comfortable with:

Variables, functions, and loops
Reading user input — if you’re shaky here, this guide to taking user input in Python is a quick refresher, since we’ll build a small command-line menu later
Basic classes and objects — the OOP in Python explained article is useful background, since we’ll wrap our database logic in reusable functions (and optionally a class)

Installing Python

Make sure you’re running a current, supported version of Python. As of mid-2026, Python 3.14 is the latest stable release, with Python 3.13 still in very wide use across production systems. The official mysql-connector-python driver currently supports Python 3.10 through 3.14, so anything in that range works fine. Avoid Python 3.9 or earlier for new projects — they’re past or nearing end-of-life support.

Download the installer from python.org and confirm your install with:

python --version

Installing MySQL Community Server

You need an actual MySQL server running before Python has anything to connect to.

Windows Installation Steps

Download the MySQL Installer from the official MySQL downloads page.
Choose the “Developer Default” setup type (it installs the server, Workbench, and sample data).
Set a strong root password when prompted — write it down somewhere safe.
Finish the wizard and confirm the MySQL service is running in the Windows Services panel.

macOS Installation Steps (Homebrew)

brew install mysql
brew services start mysql
mysql_secure_installation

Linux Installation Steps (Ubuntu/Debian)

sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo mysql_secure_installation

Note: mysql_secure_installation walks you through setting a root password and removing insecure default settings (like anonymous users and the test database). Don’t skip it, even on a local dev machine.

Installing MySQL Workbench (GUI Tool)

MySQL Workbench (currently in the 8.0.4x release line) is the official graphical tool for designing databases, writing SQL, and managing servers visually. You don’t strictly need it to use Python with MySQL, but it makes life much easier when you’re learning — you can see your tables, data, and relationships instead of guessing.

Download it from the official MySQL site, install it, then open a new connection using:

Hostname: 127.0.0.1 (or localhost)
Port: 3306 (MySQL’s default)
Username: root (for local development)
Password: whatever you set during installation

Setting Up a Virtual Environment

Before installing any Python packages, isolate your project:

python -m venv venv

# Activate it:
# Windows
venv\Scripts\activate
# macOS/Linux
source venv/bin/activate

A virtual environment keeps this project’s packages separate from every other Python project on your machine — it’s a small habit that saves a lot of “it works on my machine” headaches later.

Choosing a Python MySQL Library in 2026

There isn’t just one way to connect Python to a database (MySQL example). A few libraries dominate the ecosystem, and picking the right one matters.

mysql-connector-python (Official Oracle Driver)

This is Oracle’s own, officially maintained driver. It’s a pure Python implementation of the MySQL client/server protocol, fully compliant with Python’s DB-API 2.0 specification (PEP 249) — the standard interface that all major Python database libraries follow. As of mid-2026 the stable release line is 9.x (its version numbers are kept in sync with the MySQL Server major version), with the latest release supporting Python 3.10 through 3.14.

Because it’s pure Python with no required C dependencies for the core driver, installation is simple and consistent across operating systems — no compiler toolchain needed.

pip install mysql-connector-python

PyMySQL (Lightweight Pure-Python Alternative)

PyMySQL is another pure-Python driver, popular for its simplicity and because several major frameworks (including some ORMs) use it as a backend. It uses %s-style placeholders for queries, has zero compiled dependencies, and is a perfectly solid choice — especially if you’re already in a Django/SQLAlchemy-based stack that expects it.

pip install pymysql

mysqlclient (C Extension Option)

mysqlclient wraps the MySQL C client library directly, which makes it the fastest option for high-throughput production workloads. The tradeoff is that it requires build tools and MySQL’s development headers to install, which adds friction — especially for beginners.

A Quick Note on SQLAlchemy

You’ll also hear about SQLAlchemy, which is not a driver itself but an ORM (Object-Relational Mapper) that sits on top of a driver like mysql-connector-python or PyMySQL. Instead of writing raw SQL strings, you work with Python classes and objects, and SQLAlchemy translates that into SQL behind the scenes. It’s worth learning once you’re comfortable with raw queries — but understanding the fundamentals covered in this article first will make SQLAlchemy click much faster, because you’ll know what it’s actually doing under the hood.

Comparison Table

Library	Pure Python?	Install Difficulty	Official Oracle Support	Best For
mysql-connector-python	Yes	Easy (no compiler needed)	Yes	Beginners, general use, official support
PyMySQL	Yes	Easy	No (community-maintained)	Lightweight scripts, framework compatibility
mysqlclient	No (C extension)	Moderate (needs build tools)	No (community-maintained)	High-performance production apps
SQLAlchemy	N/A (ORM, sits on top of a driver)	Easy	No (community-maintained)	Larger apps, complex object-relational logic

Which One Should Beginners Use?

This guide uses mysql-connector-python throughout, because it’s officially maintained by Oracle, requires no compiled dependencies, and is the most predictable choice for a beginner’s first database project. Everything you learn here transfers almost directly if you switch to PyMySQL later — the core concepts (connection, cursor, execute, commit) are identical.

Installing mysql-connector-python

With your virtual environment active, run:

pip install mysql-connector-python

Verifying the Installation

import mysql.connector

print(mysql.connector.__version__)

If this prints a version number instead of throwing an error, you’re good to go.

Common Mistake: If you see ModuleNotFoundError: No module named 'mysql', double-check that your virtual environment is actually activated, and that you installed mysql-connector-python (not the unrelated, deprecated mysql-connector package on PyPI). If you’re new to chasing down errors like this, the step-by-step Python debugging guide is a good companion resource.

Setting Up Your MySQL Database

Creating a New Database

Log into MySQL via the command line or MySQL Workbench’s SQL editor, then run:

CREATE DATABASE company_db;
USE company_db;

Creating a Table

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

A quick breakdown for beginners:

INT AUTO_INCREMENT PRIMARY KEY — a unique, self-incrementing ID for every row
VARCHAR(100) — a text field with a maximum length
UNIQUE — no two rows can share the same email
DECIMAL(10, 2) — a precise number with 2 decimal places, ideal for money
TIMESTAMP DEFAULT CURRENT_TIMESTAMP — automatically records when a row was created

Security Best Practice: For real projects, avoid connecting your Python application with the MySQL root account. Create a dedicated user with only the permissions it actually needs:
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON company_db.* TO 'app_user'@'localhost';
FLUSH PRIVILEGES;

Connecting Python to MySQL

Basic Connection Script

import mysql.connector
from mysql.connector import Error

try:
    connection = mysql.connector.connect(
        host="localhost",
        user="app_user",
        password="a-strong-password",
        database="company_db"
    )

    if connection.is_connected():
        print("Successfully connected to MySQL database")

except Error as e:
    print(f"Error connecting to MySQL: {e}")

Understanding the Connection Object

The connection object represents the live link between your Python program and the MySQL server. It holds the session information, handles authentication, and is what you use to commit or roll back changes.

Understanding the Cursor Object

A Cursor is the object that actually sends SQL commands to the database and retrieves results. Think of the connection as the phone line, and the cursor as your mouth and ears — the connection makes contact, the cursor does the talking and listening.

cursor = connection.cursor()
cursor.execute("SELECT DATABASE();")
result = cursor.fetchone()
print(f"Connected to database: {result[0]}")

Closing Connections Properly

Leaving connections open is one of the most common sources of bugs in beginner projects — it eventually leads to “too many connections” errors on the server.

Using try/except/finally

connection = None
try:
    connection = mysql.connector.connect(
        host="localhost", user="app_user",
        password="a-strong-password", database="company_db"
    )
    # ... do work here ...
except Error as e:
    print(f"Error: {e}")
finally:
    if connection and connection.is_connected():
        connection.close()
        print("Connection closed")

Using Python’s `with` Statement (Recommended)

Modern versions of mysql-connector-python support context managers, which close the connection and cursor automatically — even if an error occurs:

with mysql.connector.connect(
    host="localhost", user="app_user",
    password="a-strong-password", database="company_db"
) as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT DATABASE();")
        print(cursor.fetchone())
# Connection and cursor are automatically closed here

Best Practice: Prefer the with pattern in new code. It’s harder to “forget” to close something when Python is doing the cleanup for you.

If you find yourself repeating this connection setup across multiple files, it’s worth wrapping it in a small reusable function — or, once you’re comfortable with Python decorators, building a @with_db_connection decorator that opens and closes the connection automatically around any function that needs database access.

Storing Credentials Securely

Hardcoding a password directly into a .py file is one of the easiest ways to leak credentials — especially if that file ever ends up in a public GitHub repository.

Using Environment Variables

Install python-dotenv:

pip install python-dotenv

Create a .env file in your project root (and add .env to your .gitignore):

DB_HOST=localhost
DB_USER=app_user
DB_PASSWORD=a-strong-password
DB_NAME=company_db

Then load it in Python:

import os
from dotenv import load_dotenv
import mysql.connector

load_dotenv()

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

Why This Matters: If your credentials live in code, anyone with access to that code — including a future open-source contributor, a CI/CD log, or a leaked repository — has access to your database. Environment variables keep secrets out of version control entirely.

Executing SQL Queries from Python

CRUD stands for Create, Read, Update, Delete — the four basic operations every database-driven application performs.

INSERT — Adding Data

Inserting a Single Record

def add_employee(connection, name, email, department, salary):
    cursor = connection.cursor()
    query = """
        INSERT INTO employees (name, email, department, salary)
        VALUES (%s, %s, %s, %s)
    """
    cursor.execute(query, (name, email, department, salary))
    connection.commit()
    print(f"Inserted employee with ID {cursor.lastrowid}")
    cursor.close()

Notice the %s placeholders instead of directly inserting the variables into the string — that’s a parameterized query, and it’s not optional if you care about security (more on this shortly).

Inserting Multiple Records with executemany()

def add_multiple_employees(connection, employee_list):
    cursor = connection.cursor()
    query = """
        INSERT INTO employees (name, email, department, salary)
        VALUES (%s, %s, %s, %s)
    """
    cursor.executemany(query, employee_list)
    connection.commit()
    print(f"Inserted {cursor.rowcount} employees")
    cursor.close()

# Example usage:
employees = [
    ("Ayesha Khan", "ayesha@example.com", "Engineering", 85000.00),
    ("Bilal Ahmed", "bilal@example.com", "Marketing", 62000.00),
]

executemany() is far more efficient than looping over execute() for bulk inserts, since it batches the operation.

SELECT — Reading Data

fetchone() Explained

Returns a single row (or None if there isn’t one) — useful when you expect at most one match.

cursor = connection.cursor()
cursor.execute("SELECT * FROM employees WHERE email = %s", ("ayesha@example.com",))
employee = cursor.fetchone()
print(employee)
cursor.close()

fetchall() Explained

Returns every matching row as a list of tuples — be careful using this on very large tables (see the Performance Tips section).

cursor = connection.cursor()
cursor.execute("SELECT * FROM employees")
all_employees = cursor.fetchall()
for row in all_employees:
    print(row)
cursor.close()

fetchmany(size) Explained

Returns a limited batch of rows — useful for paginating through large result sets without loading everything into memory at once.

cursor = connection.cursor()
cursor.execute("SELECT * FROM employees")
batch = cursor.fetchmany(size=10)
cursor.close()

Filtering with WHERE Clauses

def get_employees_by_department(connection, department):
    cursor = connection.cursor()
    cursor.execute(
        "SELECT name, email, salary FROM employees WHERE department = %s",
        (department,)
    )
    results = cursor.fetchall()
    cursor.close()
    return results

Once you’ve fetched rows back into Python, you may want to do more than just print them — sorting, filtering, or summarizing the data. That’s exactly where the Pandas tutorial for beginners becomes useful: you can load fetchall() results straight into a DataFrame with pd.DataFrame(results, columns=[...]) and analyze them from there.

UPDATE — Modifying Data

def update_salary(connection, employee_id, new_salary):
    cursor = connection.cursor()
    query = "UPDATE employees SET salary = %s WHERE id = %s"
    cursor.execute(query, (new_salary, employee_id))
    connection.commit()
    print(f"Rows affected: {cursor.rowcount}")
    cursor.close()

DELETE — Removing Data

def delete_employee(connection, employee_id):
    cursor = connection.cursor()
    cursor.execute("DELETE FROM employees WHERE id = %s", (employee_id,))
    connection.commit()
    print(f"Rows affected: {cursor.rowcount}")
    cursor.close()

Best Practice: Always check cursor.rowcount after an UPDATE or DELETE. If it’s 0, your WHERE clause didn’t match anything — which usually means a typo or an ID that doesn’t exist, not a server error.

Parameterized Queries and Preventing SQL Injection

SQL Injection is one of the oldest and still most common web application vulnerabilities. It happens when user input is inserted directly into a SQL string, allowing an attacker to “inject” their own SQL commands.

A Vulnerable Example (Never Do This)

# DANGEROUS — do not use this pattern
email = input("Enter email: ")
query = f"SELECT * FROM employees WHERE email = '{email}'"
cursor.execute(query)

If someone enters ' OR '1'='1 as the email, the query becomes:

SELECT * FROM employees WHERE email = '' OR '1'='1'

That condition is always true, so the attacker retrieves every row in the table — or worse, depending on what else they inject.

The Safe Way: Parameterized Queries

email = input("Enter email: ")
query = "SELECT * FROM employees WHERE email = %s"
cursor.execute(query, (email,))

With %s placeholders, the mysql-connector-python driver sends the value separately from the SQL command itself. The database treats it strictly as data, never as executable SQL — so injection attempts simply fail to do anything malicious.

Security Rule: Never build SQL queries using f-strings, .format(), or string concatenation with user input. Always use %s placeholders and pass values as a tuple in cursor.execute(). This single habit prevents the vast majority of SQL injection vulnerabilities.

Working with Transactions, Commit, and Rollback

A transaction is a group of database operations that should succeed or fail together — this is part of what’s called the ACID model (Atomicity, Consistency, Isolation, Durability) that relational databases rely on.

Commit and Rollback Explained

connection.commit() — permanently saves all changes made since the last commit
connection.rollback() — undoes all changes made since the last commit, as if they never happened

Example: A Safe Multi-Step Operation

Imagine transferring a bonus amount from one employee’s record to another — both updates need to succeed, or neither should:

def transfer_bonus(connection, from_id, to_id, amount):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "UPDATE employees SET salary = salary - %s WHERE id = %s",
            (amount, from_id)
        )
        cursor.execute(
            "UPDATE employees SET salary = salary + %s WHERE id = %s",
            (amount, to_id)
        )
        connection.commit()
        print("Transfer completed successfully")
    except Error as e:
        connection.rollback()
        print(f"Transfer failed, changes rolled back: {e}")
    finally:
        cursor.close()

If the second UPDATE fails for any reason — a bad ID, a lost connection, a constraint violation — the rollback() call undoes the first update too, so the data never ends up in a half-finished, inconsistent state.

Autocommit Mode

By default, mysql-connector-python does not autocommit — you must call commit() explicitly. Some setups enable connection.autocommit = True, which commits every statement immediately. This is convenient for quick scripts, but it removes your ability to roll back multi-step operations, so avoid it for anything that performs more than one related write.

Exception Handling for Database Operations

Common Exceptions You’ll Encounter

Exception	Typical Cause
`mysql.connector.Error`	Base class for all connector errors
`IntegrityError`	A UNIQUE or constraint violation (e.g., duplicate email)
`OperationalError`	Connection lost, server unreachable, or access denied
`ProgrammingError`	A syntax error in your SQL, or a missing table/column

Writing Robust Error Handling

from mysql.connector import Error, IntegrityError

try:
    cursor.execute(
        "INSERT INTO employees (name, email, department, salary) VALUES (%s, %s, %s, %s)",
        ("Zain Malik", "ayesha@example.com", "Sales", 55000.00)
    )
    connection.commit()
except IntegrityError:
    print("That email is already in use — please use a different one.")
except Error as e:
    print(f"Unexpected database error: {e}")

Pro Tip: In production code, log errors with Python’s built-in logging module instead of print(). It gives you timestamps, severity levels, and the ability to write logs to a file for later debugging — and it pairs naturally with the troubleshooting habits covered in the Python debugging guide.

Connection Pooling for Better Performance

Opening a brand-new connection for every single query is slow — each connection involves a TCP handshake and authentication. Connection pooling solves this by keeping a small set of ready-to-use connections open and reusing them.

from mysql.connector import pooling

connection_pool = pooling.MySQLConnectionPool(
    pool_name="company_pool",
    pool_size=5,
    host="localhost",
    user="app_user",
    password="a-strong-password",
    database="company_db"
)

# Pull a connection from the pool whenever you need one:
connection = connection_pool.get_connection()
cursor = connection.cursor()
cursor.execute("SELECT * FROM employees")
print(cursor.fetchall())
cursor.close()
connection.close()  # returns the connection to the pool, doesn't actually close it

When to Use Pooling

Use it for web applications, APIs, or any program handling multiple simultaneous requests
Skip it for small one-off scripts or simple automation tasks where a single connection is opened, used, and closed

If your application handles many requests concurrently, pooling pairs naturally with Python’s concurrency tools — worth exploring further in the multithreading in Python beginner guide once you’re comfortable with the basics here.

Performance Tips and Best Practices

Best Practices Box

Reuse connections instead of opening and closing one per query

Always use parameterized queries — for security and because the driver can optimize repeated query patterns

Select only the columns you need — avoid SELECT * in production code; it transfers more data than necessary

Index frequently queried columns (like email in our example) so lookups stay fast as your table grows

Close cursors and connections promptly, or use context managers

Batch inserts with executemany() instead of looping execute() calls

Fetch in chunks with fetchmany() for very large result sets instead of loading everything via fetchall()

When you’re processing large fetched datasets, how Python manages memory becomes relevant too — large lists of tuples from fetchall() stay in memory until they’re garbage collected, so for genuinely big tables, fetching in batches is both a performance and a memory consideration. If you want to understand what’s happening under the hood, how Python handles memory is a useful deep dive.

Security Recommendations

Security Checklist

[ ] Credentials stored in environment variables, never hardcoded

[ ] All queries use %s parameterized placeholders

[ ] The application connects with a dedicated, least-privilege MySQL user — not root

[ ] SSL/TLS is enabled for any connection that isn’t strictly local

[ ] mysql-connector-python and MySQL Server are kept reasonably up to date

[ ] The database port (3306) isn’t exposed directly to the public internet

A 2026-Specific SSL Note

If you’re running Python 3.13 or 3.14 and connecting to a remote MySQL server over SSL, be aware that newer Python versions enforce stricter certificate validation (ssl.VERIFY_X509_STRICT) by default. Certificates that aren’t fully RFC-5280 compliant — which was sometimes tolerated on older Python versions — can now cause SSL handshake failures. If you hit unexpected SSL errors after upgrading Python, check your server’s certificate chain before assuming it’s a driver bug.

Common Beginner Mistakes

Common Mistakes Box

Forgetting connection.commit() after INSERT/UPDATE/DELETE — your changes silently vanish when the connection closes. Fix: always commit after writes, or wrap related writes in a transaction with explicit commit/rollback.

Building SQL with f-strings or .format() — opens the door to SQL injection. Fix: always use %s placeholders.

Not closing connections/cursors — eventually causes “too many connections” errors on the server. Fix: use try/finally or the with statement.

Hardcoding passwords directly in scripts — a serious security risk if the code is ever shared or committed to Git. Fix: use environment variables.

Calling fetchall() on huge tables — can exhaust memory on large datasets. Fix: use fetchmany() or add a LIMIT clause.

Mismatched data types between Python variables and MySQL columns (e.g., passing a string where a DECIMAL is expected) — causes confusing errors. Fix: validate and convert types before inserting.

Not handling exceptions — an unhandled OperationalError from a brief network blip crashes the entire script. Fix: wrap database calls in try/except and decide how to retry or fail gracefully.

If you’re chasing down a mistake and the error message itself is confusing, it’s often a basic Python syntax issue hiding underneath — the guide to fixing IndentationError in Python covers one of the most common beginner stumbling blocks.

Troubleshooting Common Connection Errors

Error Message	Likely Cause	Fix
`Access denied for user '...'@'localhost'`	Wrong username/password, or the user lacks privileges on that database	Double-check credentials; re-run the `GRANT` statement for that user
`Can't connect to MySQL server on 'localhost'`	MySQL service isn’t running, or you’re using the wrong port	Start the MySQL service; confirm it’s listening on port 3306
`Unknown database 'company_db'`	The database name is misspelled, or it was never created	Run `CREATE DATABASE company_db;` and verify with `SHOW DATABASES;`
`Too many connections`	Connections aren’t being closed, or the pool/server limit is too low	Close connections after use, or implement connection pooling
SSL certificate / handshake errors on Python 3.13+	Stricter SSL validation rejects a non-compliant certificate	Update or reissue the server’s SSL certificate to be RFC-5280 compliant
`ModuleNotFoundError: No module named 'mysql'`	Virtual environment not activated, or wrong package installed	Activate your venv; confirm `pip install mysql-connector-python` succeeded

Real-World Practical Project

Let’s tie everything together into a small, genuinely useful project.

Project Overview

A command-line tool that lets a user Add, View, Update, and Delete employee records — using everything covered above: parameterized queries, proper commits, exception handling, and clean connection management.

Project Structure

employee_cli/
├── .env
├── db_connection.py
├── employee_operations.py
└── main.py

Step 1 — db_connection.py

import os
from dotenv import load_dotenv
import mysql.connector
from mysql.connector import Error

load_dotenv()

def get_connection():
    try:
        return mysql.connector.connect(
            host=os.getenv("DB_HOST"),
            user=os.getenv("DB_USER"),
            password=os.getenv("DB_PASSWORD"),
            database=os.getenv("DB_NAME")
        )
    except Error as e:
        print(f"Could not connect to the database: {e}")
        return None

Step 2 — employee_operations.py

from mysql.connector import Error, IntegrityError

def add_employee(connection, name, email, department, salary):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "INSERT INTO employees (name, email, department, salary) VALUES (%s, %s, %s, %s)",
            (name, email, department, salary)
        )
        connection.commit()
        print(f"Added employee #{cursor.lastrowid}: {name}")
    except IntegrityError:
        print("That email is already in use.")
    except Error as e:
        connection.rollback()
        print(f"Error adding employee: {e}")
    finally:
        cursor.close()

def view_employees(connection):
    cursor = connection.cursor()
    cursor.execute("SELECT id, name, email, department, salary FROM employees")
    for row in cursor.fetchall():
        print(row)
    cursor.close()

def update_salary(connection, employee_id, new_salary):
    cursor = connection.cursor()
    try:
        cursor.execute(
            "UPDATE employees SET salary = %s WHERE id = %s",
            (new_salary, employee_id)
        )
        connection.commit()
        print("Updated" if cursor.rowcount else "No matching employee found")
    except Error as e:
        connection.rollback()
        print(f"Error updating employee: {e}")
    finally:
        cursor.close()

def delete_employee(connection, employee_id):
    cursor = connection.cursor()
    try:
        cursor.execute("DELETE FROM employees WHERE id = %s", (employee_id,))
        connection.commit()
        print("Deleted" if cursor.rowcount else "No matching employee found")
    except Error as e:
        connection.rollback()
        print(f"Error deleting employee: {e}")
    finally:
        cursor.close()

Step 3 — main.py

from db_connection import get_connection
from employee_operations import add_employee, view_employees, update_salary, delete_employee

def main():
    connection = get_connection()
    if not connection:
        return

    menu = """
1. Add employee
2. View all employees
3. Update salary
4. Delete employee
5. Exit
"""
    while True:
        print(menu)
        choice = input("Choose an option: ")

        if choice == "1":
            name = input("Name: ")
            email = input("Email: ")
            department = input("Department: ")
            salary = float(input("Salary: "))
            add_employee(connection, name, email, department, salary)
        elif choice == "2":
            view_employees(connection)
        elif choice == "3":
            emp_id = int(input("Employee ID: "))
            salary = float(input("New salary: "))
            update_salary(connection, emp_id, salary)
        elif choice == "4":
            emp_id = int(input("Employee ID to delete: "))
            delete_employee(connection, emp_id)
        elif choice == "5":
            connection.close()
            print("Goodbye!")
            break
        else:
            print("Invalid option, try again.")

if __name__ == "__main__":
    main()

This menu loop relies heavily on input() — if you want to go deeper on validating and cleaning that input, the guide to taking user input in Python is worth a follow-up read.

Stretch Goals

Add input validation (reject empty names, invalid emails, negative salaries)
Export records to a CSV file using techniques from reading and writing text files in Python
Turn this CLI tool into a real web app using Flask: the complete beginner’s guide (2026)
Add authentication so only logged-in admins can delete records, using how to create a login system in Flask
Expose the same CRUD operations as a proper web API with how to build a REST API using FastAPI

Beyond the Basics

Once raw SQL feels comfortable, a few natural next steps:

Move from raw queries to an ORM like SQLAlchemy once your project’s data model grows complex
Pair MySQL with a web framework — if you’re deciding between options, Django vs Flask: which Python framework? breaks down the tradeoffs
Automate database-backed workflows, like a script that periodically reorganizes exported reports using Python script to organize files into folders
Practice deliberately — working through 50 Python coding questions for practice and the top 20 Python interview questions for beginners (2026) will reinforce both your Python and database fundamentals, since database questions show up constantly in technical interviews

Interview Tips

If this topic comes up in a technical interview, interviewers are usually checking for three things:

Do you understand why parameterized queries matter (not just that you should use them, but what SQL injection actually is)
Can you explain transactions in plain language — that commit/rollback exist to keep related changes atomic
Do you know the tradeoffs between a raw driver like mysql-connector-python and an ORM like SQLAlchemy

Being able to walk through the connect → cursor → execute → commit → close lifecycle from memory, and explain why each step matters, demonstrates real understanding rather than memorized syntax.

FAQ

1. What is the best Python library to connect to MySQL in 2026? For most beginners and general-purpose projects, mysql-connector-python is the best starting point — it’s officially maintained by Oracle, requires no compiled dependencies, and fully implements Python’s DB-API 2.0 standard.

2. Is mysql-connector-python free to use? Yes. It’s released under the GPL license and is free to download and use, including in commercial projects, subject to that license’s terms.

3. Do I need MySQL Workbench to use Python with MySQL? No — you can do everything through the command line and Python alone. Workbench just makes designing tables, browsing data, and writing test queries visually easier, which is especially helpful while learning.

4. What’s the difference between PyMySQL and mysql-connector-python? Both are pure-Python DB-API 2.0 drivers. mysql-connector-python is Oracle’s official driver; PyMySQL is community-maintained and is the dependency several popular frameworks expect by default. Functionally, they’re very similar for everyday CRUD work.

5. How do I prevent SQL injection in Python? Always use parameterized queries with %s placeholders and pass values as a separate tuple argument to cursor.execute() — never build SQL strings with f-strings, .format(), or concatenation using untrusted input.

6. Why do I need to call commit() after an INSERT or UPDATE? By default, mysql-connector-python does not autocommit. Changes exist only in the current transaction until you call connection.commit() — skip it, and your changes are lost when the connection closes.

7. Can I connect Python to a remote MySQL database, not just localhost? Yes — just change the host parameter to the remote server’s address. For any connection outside a trusted local network, also enable SSL/TLS and avoid exposing port 3306 directly to the public internet.

8. What Python version do I need for mysql-connector-python? The current release line supports Python 3.10 through 3.14. Using an actively supported Python version is recommended for both security and compatibility.

9. Should I use raw SQL or an ORM like SQLAlchemy? For learning and smaller projects, raw SQL via mysql-connector-python builds a solid foundation. For larger applications with complex, evolving data models, an ORM like SQLAlchemy can reduce boilerplate — but it’s easier to use well once you understand what it’s doing underneath.

10. How do I fix “Access Denied” errors when connecting to MySQL? Verify the username and password are correct, confirm that user has been granted privileges on the target database with a GRANT statement, and make sure you’re connecting to the right host and port.

Key Takeaways

mysql-connector-python is the recommended, officially supported way to connect Python to a database (MySQL example) in 2026, with PyMySQL as a solid lightweight alternative.
Always use parameterized queries — never build SQL with string formatting or concatenation.
Call commit() after every write, and use rollback() to cleanly undo failed multi-step transactions.
Store credentials in environment variables, never directly in your source code.
Close connections and cursors reliably with try/finally or context managers, and consider connection pooling for anything handling concurrent traffic.
The core pattern — connect, get a cursor, execute, commit, close — is the same whether you’re writing a 20-line script or powering a full Flask or FastAPI application.

Conclusion

Connecting Python to MySQL isn’t just a one-off skill for a single project — it’s a foundation that underlies an enormous amount of real-world software, from small automation scripts to full production web applications. You now know how to install the right tools, choose the right library, write safe and parameterized queries, manage transactions correctly, and structure a real CRUD project from scratch.

The best way to make this stick is to build something with it. Take the Employee Management CLI tool from this guide, extend it with your own ideas, and don’t be afraid to break things in a local test database — that’s exactly what it’s there for. When you’re ready for the next step, turning this same logic into a real web application with Flask or a fast, modern API with FastAPI is a natural — and genuinely fun — next project.

External Resources

Python Official Documentation — the authoritative reference for Python syntax, the standard library, and version-specific changes.
MySQL Official Documentation — Oracle’s complete reference for MySQL Server installation, configuration, and SQL syntax.
MySQL Connector/Python Developer Guide — the official documentation for the driver used throughout this article, including installation options and API details.
PEP 249 – Python Database API Specification v2.0 — the standard that mysql-connector-python, PyMySQL, and virtually every other Python database driver implement, useful for understanding why the connection/cursor pattern looks the way it does.
MySQL Workbench Manual — the official guide to installing and using MySQL Workbench for visual database design and management.