Skip to content
Tutorials Center
My learning

Topics

Lesson 103 of 149 ยท Data and the Web

MySQL and PostgreSQL from Python

Connect Python to MySQL and PostgreSQL. Learn the DB-API, choose a driver, use the right placeholders, manage transactions and pool connections.

In Databases with sqlite3 you stored data in a file with no server at all. Most production websites and services, though, keep their data in a database server: usually PostgreSQL or MySQL (or MySQL's close cousin, MariaDB). Many programs, often on many machines, connect to the server over the network at the same time, and the server keeps their reads and writes consistent.

The good news is that you already know most of what you need. Python's database drivers all follow one shared standard, the DB-API, so connect(), execute(), fetchall(), commit() and rollback() work the same way whether you talk to SQLite, PostgreSQL or MySQL. In this lesson you'll learn that standard properly, choose a driver for each server, build connection settings safely, use the right placeholder style, control transactions and reuse connections with a pool.

What runs here and what doesn't. The editors on this page can't reach a database server, so the PostgreSQL and MySQL code is shown read-only for you to run on your own machine. The live examples use sqlite3, which implements the very same DB-API, so the patterns you practice carry straight over.

The DB-API: one interface, many drivers

PEP 249, the Python Database API Specification v2.0, describes what every driver module provides. You'll recognize all of it:

Part What it does
module.connect(...) opens a connection (the arguments depend on the driver)
connection.cursor() creates a cursor, the object that runs statements and holds results
cursor.execute(sql, params) runs one statement with parameters
cursor.executemany(sql, rows) runs one statement for each parameter set
cursor.fetchone() / fetchmany(n) / fetchall() read result rows
cursor.description the names (and type info) of the result columns
cursor.rowcount how many rows the last statement changed or returned
connection.commit() / rollback() finish the current transaction
connection.close() / cursor.close() release resources
module.paramstyle which placeholder syntax the driver expects
module.Error, IntegrityError, โ€ฆ a standard hierarchy of exceptions

Because the interface is standard, code written against it is mostly portable. Here's the whole DB-API in action with sqlite3, including two parts the sqlite3 lesson skipped: cursor.description and fetchmany(), which reads a big result in batches instead of loading it all at once:

Python The DB-API with sqlite3

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

cursor.description is a sequence with one 7-item tuple per column; the first item is the column name, which is all most code needs. The zip() trick turns each row into a dictionary, which is roughly what the drivers' "dict cursors" (you'll see them below) do for you.

Catch the module's exceptions, not the driver's internals. driver.Error is the base class of every database error, driver.IntegrityError covers broken constraints, driver.OperationalError covers lost connections and similar trouble, and driver.ProgrammingError covers bad SQL. All DB-API drivers define these names.

Choosing a driver

The DB-API is a specification; each database needs its own driver package from PyPI. Install it into your project's virtual environment with pip (see Packages and pip):

Server Driver (import name) Install Notes
PostgreSQL psycopg 3 (psycopg) pip install "psycopg[binary]" The modern choice: sync and async, server-side parameters, COPY support
PostgreSQL psycopg2 (psycopg2) pip install psycopg2-binary The previous generation; still in many existing projects
PostgreSQL asyncpg (asyncpg) pip install asyncpg Very fast, async only, not DB-API style ($1 placeholders)
MySQL / MariaDB PyMySQL (pymysql) pip install pymysql Pure Python, easy to install anywhere
MySQL / MariaDB mysql-connector-python (mysql.connector) pip install mysql-connector-python Oracle's official driver, includes pooling
MySQL / MariaDB mysqlclient (MySQLdb) pip install mysqlclient C extension, fast, needs MySQL client libraries to build

The [binary] extra of psycopg installs a precompiled copy of libpq, PostgreSQL's client library, so you don't need a compiler. If you're unsure, start with psycopg 3 for PostgreSQL and PyMySQL for MySQL.

Connecting to the server

A server connection needs a host, a port (PostgreSQL uses 5432 by default, MySQL 3306), a database name, a user and a password. Here's the same query with each popular driver:

Python
# PostgreSQL with psycopg 3
import psycopg
from psycopg.rows import dict_row

with psycopg.connect("postgresql://shop_app:secret@localhost:5432/shop",
                     row_factory=dict_row) as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT id, name, price FROM products WHERE price < %s", (20,))
        for product in cursor.fetchall():
            print(product["name"], product["price"])
Python
# MySQL with PyMySQL
import pymysql

connection = pymysql.connect(host="localhost", port=3306, user="shop_app",
                             password="secret", database="shop", charset="utf8mb4",
                             cursorclass=pymysql.cursors.DictCursor)
try:
    with connection.cursor() as cursor:
        cursor.execute("SELECT id, name, price FROM products WHERE price < %s", (20,))
        for product in cursor.fetchall():
            print(product["name"], product["price"])
finally:
    connection.close()
Python
# MySQL with mysql-connector-python
import mysql.connector

connection = mysql.connector.connect(host="localhost", user="shop_app",
                                     password="secret", database="shop")
cursor = connection.cursor(dictionary=True)
cursor.execute("SELECT id, name, price FROM products WHERE price < %s", (20,))
print(cursor.fetchall())
cursor.close()
connection.close()

Notice how similar they are. The differences are in the details: how you ask for dictionary rows (row_factory=dict_row, DictCursor, dictionary=True), and what the connection's with block does (more on that in the transactions section). For MySQL, always ask for the utf8mb4 character set: MySQL's older utf8 stores at most three bytes per character, so emoji and some rarer characters don't fit.

Keep credentials out of your code

Never hard-code a real password in source code that lands in Git. The usual approach is a single database URL in an environment variable, such as DATABASE_URL=postgresql://user:password@host:5432/dbname, which your program reads at startup. Python's urllib.parse can split such a URL into the separate arguments that drivers like PyMySQL expect:

Python Database settings from a URL

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Two details matter here. Special characters in a password must be percent-encoded inside a URL (@ becomes %40, : becomes %3A), and unquote() decodes them again. And the masked() helper exists because connection settings end up in logs and error reports far more often than you'd expect; never print the password itself.

Placeholder styles: ? versus %s

Every driver passes values separately from the SQL text, which is what protects you from SQL injection. But they don't all use the same placeholder syntax. The module attribute paramstyle tells you which one a driver expects:

paramstyle Placeholder Example Used by
qmark ? WHERE id = ? sqlite3
named :name WHERE id = :id sqlite3, oracledb
format %s WHERE id = %s mysqlclient
pyformat %s or %(name)s WHERE id = %(id)s psycopg, PyMySQL, mysql-connector
numeric :1 WHERE id = :1 rarely used today

The %s style looks like old-style Python string formatting, and that confuses many people. It is not string formatting: you pass the values as the second argument to execute() and the driver handles them safely. Three rules keep you out of trouble:

  • Write %s for every type (numbers and dates too), never %d or %f.
  • Never put quotes around the placeholder: WHERE name = %s, not WHERE name = '%s'.
  • In psycopg and PyMySQL, a literal percent sign in a query that has parameters must be doubled: WHERE code LIKE 'A%%' AND price < %s.

If you write SQL that must work with several drivers, keep it in one style and convert it. This helper turns :name placeholders into each style, then runs the qmark version on SQLite to prove it works:

Python One query, every placeholder style

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Real projects rarely write this converter themselves: an ORM or query builder such as SQLAlchemy lets you write :name once and translates it for whichever driver sits underneath.

Transactions on a database server

The DB-API rule is the same everywhere: a transaction starts automatically with your first statement, and nothing is permanent until you call commit(). Call rollback() to undo everything since the last commit. Closing a connection without committing throws the changes away.

What differs is the convenience layer around that rule, and the differences are easy to trip over:

Driver with connection: does Autocommit switch
sqlite3 commits or rolls back; does not close autocommit= argument (3.12+)
psycopg 3 commits or rolls back, then closes connection.autocommit = True
PyMySQL closes the connection only autocommit=True in connect()
mysql-connector closes the connection only connection.autocommit = True

psycopg 3 also has an explicit block that's great for grouping statements:

Python
with psycopg.connect(DATABASE_URL) as connection:
    with connection.transaction():           # COMMIT on success, ROLLBACK on error
        connection.execute("UPDATE accounts SET balance = balance - %s WHERE id = %s",
                           (amount, sender_id))
        connection.execute("UPDATE accounts SET balance = balance + %s WHERE id = %s",
                           (amount, receiver_id))

To stay portable, you can write the commit-or-rollback logic yourself with try/except, using nothing but DB-API calls. This context manager works with any driver:

Python A portable transaction helper

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The second order first took a mug, then failed on the lamp, and the rollback put the mug back. On a server this matters even more, because other connections are working at the same time. Keep transactions short: an open transaction can hold locks that make other users wait.

Getting the id of a new row

After an INSERT, you usually need the new row's id. MySQL drivers give you cursor.lastrowid, just like sqlite3. PostgreSQL drivers don't fill lastrowid for normal tables; instead you ask for the value with RETURNING, which reads like a SELECT attached to the INSERT:

Python
# PostgreSQL
cursor.execute("INSERT INTO customers (name, email) VALUES (%s, %s) RETURNING id",
               ("Ana", "[email protected]"))
new_id = cursor.fetchone()[0]

MySQL 8 has no RETURNING (MariaDB does), while SQLite has supported it since version 3.35. That makes it easy to try:

Python INSERT ... RETURNING

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

RETURNING works with UPDATE and DELETE too, which saves a second round trip to the server to see what changed.

Connection pools

Opening a connection to a database server is slow compared with running a simple query. The client and server exchange several network messages, check the password and often set up encryption (TLS). A web app that opened a fresh connection for every request would spend a big share of its time just saying hello.

A connection pool keeps a few connections open and lends them out. Code borrows a connection, uses it briefly and gives it back. The pool also caps the total, so a traffic spike can't open more connections than the server allows (PostgreSQL's default max_connections is 100, MySQL's is 151).

To see the idea, here's a tiny pool built on queue.Queue. It works with any DB-API connect function:

Python A tiny connection pool

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Six borrowings were served by only two connections. When both are in use, the next borrower waits briefly and then gets a clear error instead of overloading the server. Real pools add health checks (replacing connections the server dropped), a minimum and maximum size, and thread safety. Use one of them instead of your own:

Python
# PostgreSQL: pip install psycopg_pool
from psycopg_pool import ConnectionPool

pool = ConnectionPool(DATABASE_URL, min_size=2, max_size=10)

def product_count() -> int:
    with pool.connection() as connection:            # borrow, commit, give back
        return connection.execute("SELECT count(*) FROM products").fetchone()[0]
Python
# MySQL: pooling built into mysql-connector-python
from mysql.connector import pooling

pool = pooling.MySQLConnectionPool(pool_name="web", pool_size=5, host="localhost",
                                   user="shop_app", password=PASSWORD, database="shop")
connection = pool.get_connection()
try:
    ...
finally:
    connection.close()                              # returns it to the pool

Web frameworks and ORMs usually manage the pool for you: SQLAlchemy creates one per engine, and Django can keep connections open between requests. In large deployments, a separate pooling server such as PgBouncer sits between many app processes and PostgreSQL.

Good habits for server databases

  • Always use parameters. The %s placeholders are just as safe as ?; string formatting is just as dangerous.
  • Give your app its own database user with only the rights it needs (for example, no DROP TABLE), and keep the password in the environment.
  • Close what you open: use with blocks or try/finally for cursors and connections.
  • Keep transactions short and never wait for user input while one is open.
  • Fetch only what you need: select specific columns, filter with WHERE and use LIMIT, instead of loading whole tables into Python.
  • Use a local server for development. Docker makes it a one-liner, for example docker run -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why can code written for sqlite3 often be moved to psycopg or PyMySQL with small changes?

    The DB-API standard defines the shared interface. The SQL dialect and the placeholder style can still differ.
  2. 2. Which line is correct for psycopg or PyMySQL?

    These drivers use the pyformat style, so %s is the placeholder for every type, without quotes, and the values go in the second argument. The first option is string formatting (unsafe), and ? is the sqlite3 style.
  3. 3. What does with psycopg.connect(url) as connection: do when the block ends without an error?

    In psycopg 3 the connection's context manager commits (or rolls back on an exception) and then closes. sqlite3's version commits but leaves the connection open.
  4. 4. What is the main reason to use a connection pool in a web app?

    Opening a connection takes several network round trips and authentication. A pool reuses open connections and limits the total the app can use.
  5. 5. How do you get the id of a row you just inserted into PostgreSQL?

    RETURNING sends back values from the inserted row in the same statement. SELECT MAX(id) is unreliable when other connections insert at the same time.

Summary

  • PostgreSQL and MySQL drivers follow the DB-API: connect(), cursors, execute(), fetchone()/fetchmany()/fetchall(), commit(), rollback() and a standard exception hierarchy.
  • Good defaults: psycopg 3 for PostgreSQL and PyMySQL or mysql-connector-python for MySQL; ask for utf8mb4 with MySQL.
  • Read connection settings from an environment variable such as DATABASE_URL, percent-encode special characters and never log the password.
  • Check paramstyle: sqlite3 uses ?, the PostgreSQL and MySQL drivers use %s or %(name)s, always passed as parameters, never formatted in.
  • Transactions start automatically; what a connection's with block does (commit, close, or both) depends on the driver.
  • Get new ids with lastrowid (MySQL, SQLite) or RETURNING (PostgreSQL, SQLite, MariaDB).
  • Connection pools reuse open connections and cap their number; use psycopg_pool, the driver's pool, or your framework's.

Next: ORMs with SQLAlchemy, where you'll work with tables as Python classes and let SQLAlchemy write the SQL.