Python MySQL Tutorial

A driver is the Python package that speaks MySQL's network protocol. Install it into your project's virtual environment:

python -m pip install mysql-connector-python

The package installs the client library only. It doesn't install or start a MySQL server; that's a separate piece of software, for example MySQL Community Server, a managed cloud database, or docker run -e MYSQL_ROOT_PASSWORD=... -p 3306:3306 mysql:8.

Other drivers exist: PyMySQL (pure Python) and mysqlclient (a C extension used by Django). They follow the same Python database API (PEP 249), so connect(), cursor(), execute(), fetchone(), commit() and rollback() work almost identically. For a quick experiment without any server, Python's built-in sqlite3 module follows the same pattern, though it uses ? placeholders instead of %s.

mysql.connector.connect() takes the server address, credentials and database name. Read them from environment variables rather than writing them in the code:

import os
import mysql.connector

connection = mysql.connector.connect(
    host=os.environ["MYSQL_HOST"],
    port=int(os.getenv("MYSQL_PORT", "3306")),
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"],
)

Connection errors raise mysql.connector.Error subclasses with useful messages, such as access denied or unknown database. Create a dedicated database user for your application with only the permissions it needs, never the root account.

Open a connection, do a unit of work, and close it. Long-running applications reuse connections through a pool (mysql.connector.pooling.MySQLConnectionPool) or an ORM such as SQLAlchemy, rather than connecting for every query. The database connections lesson discusses this in more depth.

Never build SQL by inserting values into the string with f-strings, + or %. Pass values separately as parameters, and let the driver send them safely:

member_id = 7
cursor.execute("SELECT name, email FROM members WHERE member_id = %s", (member_id,))
row = cursor.fetchone()        # a tuple, or None if no row matched

Connector/Python uses %s as the placeholder for every type, numbers included, and the parameters must be a tuple (note the comma in (member_id,)) or a list. Named placeholders also work: %(member_id)s with a dictionary.

Parameters stop SQL injection. If a search box value such as x' OR '1'='1 were pasted into the SQL text, it would change the query's meaning; as a parameter, it's only ever treated as a value. Placeholders can stand only for values, not for table or column names. If those must vary, choose them from a fixed allow-list in your code.

Read results with fetchone(), fetchall() or by looping over the cursor. connection.cursor(dictionary=True) returns rows as dictionaries keyed by column name.

Changes made with INSERT, UPDATE and DELETE happen inside a transaction. Connector/Python turns autocommit off by default, so nothing is saved until you call connection.commit(); connection.rollback() abandons the pending changes:

try:
    cursor.execute("UPDATE books SET on_loan = 1 WHERE book_id = %s", (42,))
    cursor.execute("INSERT INTO loans (book_id, member_id) VALUES (%s, %s)", (42, 7))
    connection.commit()          # both changes saved together
except mysql.connector.Error:
    connection.rollback()        # neither change saved
    raise

Grouping related changes in one transaction keeps the data consistent: a book is never marked as on loan without a matching loan record. Setting connection.autocommit = True makes every statement commit immediately, which is convenient for scripts but loses this protection.

Always close cursors and connections, even after errors, with try/finally as in the example below. Unclosed connections hold server resources and can exhaust the server's connection limit.

Task: use a local sample MySQL database and parameterized loan queries, documenting set-up and clean-up. Create the schema in the MySQL client first:

CREATE DATABASE library_demo;
USE library_demo;
CREATE TABLE loans (loan_id INT AUTO_INCREMENT PRIMARY KEY, member_id INT, title VARCHAR(100));

Then, with MYSQL_DATABASE=library_demo and the other variables set:

import os
import mysql.connector

conn = mysql.connector.connect(host=os.environ["MYSQL_HOST"], user=os.environ["MYSQL_USER"],
                               password=os.environ["MYSQL_PASSWORD"], database=os.environ["MYSQL_DATABASE"])
try:
    cur = conn.cursor()
    cur.execute("INSERT INTO loans (member_id, title) VALUES (%s, %s)", (7, "River Atlas"))
    conn.commit()
    cur.execute("SELECT title FROM loans WHERE member_id = %s", (7,))
    print(cur.fetchall())          # [('River Atlas',)]
    cur.close()
finally:
    conn.close()

Clean up afterwards with DROP DATABASE library_demo;. For the worked check, a member identifier should enter a SQL query as a bound parameter.

The MySQL Connector/Python developer guide documents connection options, cursors and errors, and PEP 249 defines the common database API. Continue with SQL CRUD in Python and database connections. Practise with the Python exercises and Python quiz.