Python and Databases: Connections, Parameters and Transactions

An AI application almost never sits next to a SQL editor. A training script pulls labelled examples out of a database, a chatbot logs every prompt, answer and token count into one, a feature service reads a customer's history in a few milliseconds before a model scores them. All of that is Python code talking to a database through a driver. This page is about doing that properly: the right parameters, real transactions, clean error handling, and code you can test.

You will use three drivers: sqlite3 (built into Python), PyMySQL for MySQL and psycopg2 for PostgreSQL, the last two against real servers. pandas and SQLAlchemy come on the next page; everything they do is built on what you learn here.

What you will learn

• The DB-API shape every Python driver shares: connect, cursor, execute, fetch, commit, close.
• Parameters done right (?, :name, %s), including lists of values.
• Transactions from Python: commit, rollback, with con: and the gotcha hiding in it.
• Batch inserts with executemany and reading big results in chunks.
• Connecting to MySQL and PostgreSQL with settings from environment variables, pooling, and testing database code.

One interface, many drivers: the DB-API

Python's database drivers all follow one specification, DB-API 2.0 (PEP 249). Learn its shape once and you can use any database:

connect(...)  ──►  connection  ──►  cursor()  ──►  execute(sql, params)
                      │                              │
                      │                              ├─ fetchone()   one row or None
                      │                              ├─ fetchmany(n) a list of up to n rows
                      │                              └─ fetchall()   every remaining row
                      ├─ commit()     make the changes permanent
                      ├─ rollback()   throw the changes away
                      └─ close()      give the connection back

What differs between drivers is mostly the placeholder style, and every driver tells you which one it uses:

import sqlite3
import pymysql
import psycopg2

for driver in (sqlite3, pymysql, psycopg2):
    print(f"{driver.__name__:8} apilevel={driver.apilevel} paramstyle={driver.paramstyle}")

sqlite3  apilevel=2.0 paramstyle=qmark
pymysql  apilevel=2.0 paramstyle=pyformat
psycopg2 apilevel=2.0 paramstyle=pyformat

The names are confusing: psycopg 3 is imported as psycopg, with no number, while the older version is psycopg2. New projects should prefer psycopg 3; this page uses psycopg2 because it is still in most existing code, and the calls you learn here look almost the same in both.

sqlite3: connect, execute, fetch

import sqlite3

con = sqlite3.connect("shop.db")            # opens the file (and creates it if it is missing!)
con.execute("PRAGMA foreign_keys = ON")     # SQLite checks foreign keys only when asked

cur = con.cursor()
cur.execute("SELECT product_id, name, price FROM products ORDER BY price DESC")

print([d[0] for d in cur.description])      # column names
print(cur.fetchone())                       # the next row, as a tuple
print(cur.fetchmany(2))                     # the next 2 rows, as a list
rest = cur.fetchall()                       # everything that is left
print(len(rest), "more rows, the last is", rest[-1])

['product_id', 'name', 'price']
(5, '27-inch Monitor', 18500)
[(7, 'AI Engineering Bootcamp', 15000), (10, 'Noise-Cancelling Headphones', 7500)]
8 more rows, the last is (4, 'Wireless Mouse', 900)

A cursor is a position in a result. Each fetch moves it forward, so fetchall() returned only the 8 rows that were left. con.execute(...) is a shortcut that makes a cursor for you (sqlite3 and psycopg 3 have it); PyMySQL and psycopg2 want con.cursor() first. A cursor is also iterable: for row in cur: fetches the rows one by one, which you will use later on this page.

Tuples are fast but anonymous. When you hand rows to other code (or turn them into JSON for an API or an LLM prompt), named access is clearer. sqlite3 has sqlite3.Row:

con.row_factory = sqlite3.Row
row = con.execute("SELECT customer_id, name, city FROM customers WHERE customer_id = 5").fetchone()
print(row["name"], row["city"])
print(dict(row))
con.row_factory = None                      # back to plain tuples

Sadia Chowdhury None
{'customer_id': 5, 'name': 'Sadia Chowdhury', 'city': None}

Notice that SQL NULL arrives in Python as None, and None sent as a parameter becomes NULL.

Parameters: never glue values into SQL

You saw SQL injection on the page Security: Roles, SQL Injection, Sensitive Data and Backups. The rule from Python is simple: the SQL text is a constant, the values travel separately as parameters. The driver either sends them separately from the SQL (sqlite3, psycopg 3) or quotes them safely itself (PyMySQL, psycopg2), so they can never be read as SQL, and quoting, apostrophes (O'Brien) and dates just work.

city = "Dhaka"
rows = con.execute(
    "SELECT name FROM customers WHERE city = ? ORDER BY customer_id",
    (city,),                                # a tuple: note the comma
).fetchall()
print(rows)

rows = con.execute(
    "SELECT name, price FROM products WHERE price BETWEEN :low AND :high ORDER BY price",
    {"low": 2000, "high": 4000},
).fetchall()
print(rows)

ids = [3, 7, 11]
marks = ", ".join("?" * len(ids))           # "?, ?, ?": one placeholder per value
rows = con.execute(
    f"SELECT product_id, name FROM products WHERE product_id IN ({marks}) ORDER BY product_id",
    ids,
).fetchall()
print(rows)

[('Nadia Rahman',), ('Farhana Akter',), ('Mitu Das',)]
[('USB-C Hub', 2200), ('Hands-On Machine Learning', 2500), ('SQL Masterclass', 3000)]
[(3, 'Mechanical Keyboard'), (7, 'AI Engineering Bootcamp'), (11, 'Gift Card')]

The last query uses an f-string, and that is safe here because it only inserts question marks, never data. Placeholders stand for values only. A table or column name cannot be a parameter. If it has to vary, check it against a fixed list (if column not in {"price", "stock"}: raise ValueError) before putting it in the SQL.

Writing data, and when it becomes real

INSERT, UPDATE and DELETE work like SELECT, but the change is not permanent until you commit. In its default mode, Python's sqlite3 opens a transaction silently before the first data-changing statement. To see what that means, watch from a second connection, as if it were another program:

other = sqlite3.connect("shop.db")          # a second connection: think "another program"

cur = con.execute(
    "INSERT INTO customers (name, email, city, joined_on) VALUES (?, ?, ?, ?)",
    ("Rumana Begum", "rumana@example.com", "Barishal", "2026-06-15"),
)
print("new id:", cur.lastrowid, "| rows:", cur.rowcount, "| in transaction:", con.in_transaction)
print("other sees", other.execute("SELECT COUNT(*) FROM customers").fetchone()[0], "customers")

con.commit()
print("after commit, other sees", other.execute("SELECT COUNT(*) FROM customers").fetchone()[0], "customers")
other.close()

new id: 9 | rows: 1 | in transaction: True
other sees 8 customers
after commit, other sees 9 customers

• lastrowid is the id the database assigned; rowcount is how many rows the statement changed.
• Until the commit, the new customer existed only inside con's transaction. If the program had crashed there, it would be gone.
• rollback() throws away everything since the last commit:

cur = con.execute("UPDATE products SET price = price + 100 WHERE category_id = ?", (4,))
print(cur.rowcount, "rows changed")
con.rollback()                              # changed our mind
print(con.execute("SELECT name, price FROM products WHERE category_id = 4 ORDER BY product_id").fetchall())

2 rows changed
[('Laptop Stand', 1500), ('USB-C Hub', 2200)]

Python 3.12 added the autocommit attribute (also an argument: sqlite3.connect(..., autocommit=False)). With autocommit=False, sqlite3 follows the DB-API exactly: a transaction is always open, even for a SELECT, and you always commit or roll back. With autocommit=True, every statement commits on its own unless you write BEGIN yourself. The default is still the "legacy" mode used on this page (sqlite3.LEGACY_TRANSACTION_CONTROL, steered by isolation_level), which opens a transaction only before INSERT, UPDATE, DELETE or REPLACE; Python's documentation says the default will change to False in a future release. For the INSERT, UPDATE and DELETE on this page, both behave the same.

Transactions and error handling

An order is one order row plus its item rows. Either all of them are saved or none: half an order is corrupt data. Wrap the work in try, commit at the end, and roll back on any database error:

def place_order(con, customer_id, order_date, items):
    """items: a list of (product_id, quantity). All or nothing."""
    try:
        cur = con.execute(
            "INSERT INTO orders (customer_id, order_date, status) VALUES (?, ?, 'pending')",
            (customer_id, order_date),
        )
        order_id = cur.lastrowid
        for product_id, quantity in items:
            con.execute(
                "INSERT INTO order_items (order_id, product_id, quantity, unit_price) "
                "SELECT ?, product_id, ?, price FROM products WHERE product_id = ?",
                (order_id, quantity, product_id),
            )
        con.commit()
        return order_id
    except sqlite3.Error as exc:
        con.rollback()
        print("rolled back:", type(exc).__name__, "-", exc)
        return None

print(place_order(con, 8, "2026-06-20", [(1, 1), (4, 2)]))
print(place_order(con, 8, "2026-06-21", [(1, 1), (4, 0)]))      # quantity 0 breaks a CHECK
print(con.execute("SELECT order_id, customer_id, status FROM orders WHERE order_id > 14").fetchall())

15
rolled back: IntegrityError - CHECK constraint failed: quantity > 0
None
[(15, 8, 'pending')]

The second call inserted an order and one item before the CHECK failed, and the rollback removed both: only order 15 exists. Catching sqlite3.Error (not every Exception) means a real bug in your Python, such as a TypeError, still crashes loudly.

Every driver raises the same family of exceptions (each has its own copies: sqlite3.IntegrityError, pymysql.IntegrityError, psycopg2.IntegrityError), so the pattern carries over:

with con: commits, but does not close

The connection is also a context manager: with con: commits if the block succeeds and rolls back if it raises. That is shorter and harder to get wrong:

try:
    with con:
        con.execute("UPDATE products SET stock = stock - 1 WHERE product_id = 3")
        con.execute("UPDATE products SET stock = stock - 1 WHERE product_id = 9")   # stock is 0: CHECK fails
except sqlite3.IntegrityError as exc:
    print("IntegrityError:", exc)
print(con.execute("SELECT product_id, stock FROM products WHERE product_id IN (3, 9) ORDER BY product_id").fetchall())

IntegrityError: CHECK constraint failed: stock >= 0
[(3, 12), (9, 0)]

Product 3 still has 12: the first update was rolled back with the second. Now the gotcha. Many people assume with sqlite3.connect(...) as c: closes the connection, like with open(...) closes a file. It does not:

from contextlib import closing

with sqlite3.connect("shop.db") as c:
    print(c.execute("SELECT COUNT(*) FROM products").fetchone())
print(c.execute("SELECT 'still open'").fetchone())       # the with block did NOT close it
c.close()

with closing(sqlite3.connect("shop.db")) as c:           # closing() closes it at the end
    with c:                                              # and this commits or rolls back
        print(c.execute("SELECT COUNT(*) FROM orders").fetchone())

(11,)
('still open',)
(15,)

psycopg2 behaves the same way (with conn: ends the transaction, not the connection). psycopg 3 commits (or rolls back) and closes; PyMySQL closes without committing. Check what with does for each driver you use. In a script that opens one connection it hardly matters. In a web server or a long training job that opens one per request or per batch, unclosed connections pile up until the server refuses new ones.

Many rows: executemany and chunks

A chatbot writes a log row for every LLM call, and later you analyse thousands of them. Generate 5,000 fake calls (seeded, so the numbers are always the same) and insert them in one batch, in one transaction:

import random

con.execute("""
    CREATE TABLE llm_calls (
        call_id       INTEGER PRIMARY KEY,
        model         TEXT    NOT NULL,
        prompt_tokens INTEGER NOT NULL,
        output_tokens INTEGER NOT NULL
    )""")

random.seed(42)
models = ["chat-small", "chat-large", "embed-v1"]
rows = [(random.choice(models), random.randint(50, 2000), random.randint(0, 800)) for _ in range(5000)]

with con:
    con.executemany(
        "INSERT INTO llm_calls (model, prompt_tokens, output_tokens) VALUES (?, ?, ?)", rows
    )
print(con.execute("SELECT COUNT(*) FROM llm_calls").fetchone()[0], "rows")

5000 rows

In sqlite3, executemany prepares the statement once and runs it for every tuple. Other drivers optimise it differently: PyMySQL rewrites a simple INSERT … VALUES into multi-row inserts, psycopg 3 pipelines the statements, but psycopg2 just loops (use psycopg2.extras.execute_values there for speed). The bigger win is the single transaction: committing after every row forces the database to flush to disk 5,000 times, which can be a hundred times slower.

Reading works in the other direction. fetchall() on ten million rows tries to hold all of them in memory. fetchmany(n) lets you process a fixed-size batch at a time, and a small generator makes that pleasant:

def in_batches(cursor, size=1000):
    """Yield lists of at most `size` rows until the result is used up."""
    while True:
        batch = cursor.fetchmany(size)
        if not batch:
            return
        yield batch

cur = con.execute("SELECT model, prompt_tokens + output_tokens FROM llm_calls ORDER BY call_id")
totals, batches = {}, 0
for batch in in_batches(cur, 1000):
    batches += 1
    for model, tokens in batch:
        totals[model] = totals.get(model, 0) + tokens
print(batches, "batches")
for model in sorted(totals):
    print(f"{model:11} {totals[model]:>9,} tokens")

5 batches
chat-large  2,400,092 tokens
chat-small  2,282,629 tokens
embed-v1    2,416,957 tokens

A simple total like this belongs in SQL (GROUP BY model). Chunks are for work only Python can do on every row: computing an embedding for each product description, cleaning text before training, writing rows to a file.

With the server drivers, an ordinary cursor downloads the whole result during execute(), so fetchmany saves you nothing on memory. To really stream, use a server-side cursor: a named cursor in psycopg2 (shown below) or pymysql.cursors.SSCursor in PyMySQL.

MySQL and PostgreSQL from Python

Server connections need a host, port, user, password and database. Those are configuration, not code: they differ between your laptop, the test server and production, and the password must never be committed to git. The standard answer is environment variables, set by your shell, a .env file loaded with python-dotenv, Docker, or a secret manager in production:

export MYSQL_HOST=127.0.0.1 MYSQL_PORT=3306 MYSQL_USER=root MYSQL_PASSWORD='…' MYSQL_DATABASE=shop
export PGHOST=127.0.0.1 PGPORT=5432 PGUSER=postgres PGPASSWORD='…' PGDATABASE=shop

Load shop.sql into each server first (see Setup: Your Practice Database in the beginner tutorial). Then MySQL with PyMySQL:

import os
import pymysql

my = pymysql.connect(
    host=os.environ["MYSQL_HOST"],
    port=int(os.environ["MYSQL_PORT"]),
    user=os.environ["MYSQL_USER"],
    password=os.environ["MYSQL_PASSWORD"],
    database=os.environ["MYSQL_DATABASE"],
)
with my.cursor() as cur:                     # closes the cursor, not the connection
    cur.execute(
        "SELECT name, joined_on FROM customers WHERE city = %s ORDER BY customer_id",
        ("Dhaka",),
    )
    for name, joined_on in cur.fetchall():
        print(name, repr(joined_on))

    cur.execute("UPDATE products SET price = price * 2 WHERE product_id = %s", (1,))
    print("rows changed:", cur.rowcount)
my.rollback()                                # PyMySQL does not autocommit: undo it

with my.cursor(pymysql.cursors.DictCursor) as cur:      # rows as dictionaries
    cur.execute("SELECT product_id, price FROM products WHERE product_id = %s", (1,))
    print(cur.fetchone())
my.close()

Nadia Rahman datetime.date(2025, 11, 3)
Farhana Akter datetime.date(2026, 1, 9)
Mitu Das datetime.date(2026, 3, 30)
rows changed: 1
{'product_id': 1, 'price': 1200}

Two differences from SQLite already: the placeholder is %s (always %s, even for numbers, and never put quotes around it), and dates come back as real datetime.date objects, because MySQL has a real DATE type. In SQLite the same column is the string '2025-11-03'.

PostgreSQL with psycopg2 looks almost the same. Keep the settings in one dictionary so a connection and a pool can share them:

import psycopg2
import psycopg2.errors

pg_settings = {
    "host": os.environ["PGHOST"],
    "port": os.environ["PGPORT"],
    "user": os.environ["PGUSER"],
    "password": os.environ["PGPASSWORD"],
    "dbname": os.environ["PGDATABASE"],
}
pg = psycopg2.connect(**pg_settings)

with pg.cursor() as cur:
    cur.execute("SELECT name, price FROM products WHERE price > %(min)s ORDER BY price DESC", {"min": 10000})
    print(cur.fetchall())
    cur.execute("SELECT ROUND(AVG(price), 2) FROM products")
    print(repr(cur.fetchone()[0]))

[('27-inch Monitor', 18500), ('AI Engineering Bootcamp', 15000)]
Decimal('5254.55')

Exact numbers (NUMERIC, and AVG of integers) arrive as decimal.Decimal, not float, so no money is lost to rounding. json.dumps cannot serialise a Decimal: convert it with float() or str() before sending a row to an API or putting it in an LLM prompt.

PostgreSQL's aborted transactions

In PostgreSQL, one failed statement poisons the whole transaction. Every later statement is refused until you roll back. This surprises people who catch the error and carry on:

with pg.cursor() as cur:
    try:
        cur.execute("SELECT nme FROM customers")              # typo
    except psycopg2.errors.UndefinedColumn as exc:
        print("error", exc.pgcode, "-", str(exc).splitlines()[0])
    try:
        cur.execute("SELECT 1")
    except psycopg2.errors.InFailedSqlTransaction as exc:
        print("next statement:", str(exc).splitlines()[0])

pg.rollback()                                                 # end the failed transaction
with pg.cursor() as cur:
    cur.execute("SELECT COUNT(*) FROM orders")
    print("after rollback:", cur.fetchone())

error 42703 - column "nme" does not exist
next statement: current transaction is aborted, commands ignored until end of transaction block
after rollback: (14,)

psycopg2.errors has one class per PostgreSQL error code (UndefinedColumn is 42703, UniqueViolation is 23505), and each is a subclass of the standard DB-API ones, so except psycopg2.IntegrityError still catches a UniqueViolation.

Streaming with a server-side cursor

Give a psycopg2 cursor a name and the result stays on the server; Python pulls itersize rows per network round trip as you loop:

with pg.cursor(name="orders_stream") as cur:     # named = server-side
    cur.itersize = 5                             # rows per round trip
    cur.execute("SELECT order_id, status FROM orders ORDER BY order_id")
    seen = 0
    for order_id, status in cur:
        seen += 1
print(seen, "orders streamed, the last was", order_id, status)
pg.rollback()                                    # a named cursor lives inside a transaction
pg.close()

14 orders streamed, the last was 14 delivered

Connection pools

Opening a server connection costs a network handshake, authentication and a new server process (PostgreSQL) or thread (MySQL): often several milliseconds, sometimes far more. A web API that opens a fresh connection per request wastes that time on every call and can exhaust the server's connection limit under load. A pool keeps a few connections open and lends them out:

from psycopg2.pool import SimpleConnectionPool

pool = SimpleConnectionPool(minconn=1, maxconn=4, **pg_settings)

conn = pool.getconn()                    # borrow
try:
    with conn.cursor() as cur:
        cur.execute("SELECT COUNT(*) FROM customers")
        print("customers:", cur.fetchone()[0])
    conn.commit()
finally:
    pool.putconn(conn)                   # always give it back
pool.closeall()

customers: 8

Use ThreadedConnectionPool when several threads share the pool. (psycopg 3 has its own pool in the separate psycopg_pool package.) In practice most applications get pooling from SQLAlchemy (next page) or from a separate pooler such as PgBouncer in front of PostgreSQL.

Testing database code

Database code is code, and it breaks like code. The cheapest reliable test database is SQLite in memory: ":memory:" builds a private database that disappears when the connection closes, so each test starts clean and fast. A function that builds it is a fixture:

def make_test_db():
    """Fixture: a fresh in-memory shop database for one test."""
    db = sqlite3.connect(":memory:")
    db.execute("PRAGMA foreign_keys = ON")
    with open("shop.sql", encoding="utf-8") as f:
        db.executescript(f.read())
    return db

def test_good_order_is_saved():
    db = make_test_db()
    order_id = place_order(db, 8, "2026-06-20", [(1, 2)])
    assert db.execute("SELECT quantity, unit_price FROM order_items WHERE order_id = ?",
                      (order_id,)).fetchall() == [(2, 1200)]

def test_bad_order_leaves_nothing_behind():
    db = make_test_db()
    assert place_order(db, 8, "2026-06-21", [(1, 1), (4, 0)]) is None
    assert db.execute("SELECT COUNT(*) FROM orders").fetchone()[0] == 14

test_good_order_is_saved()
test_bad_order_leaves_nothing_behind()
print("both tests passed")

rolled back: IntegrityError - CHECK constraint failed: quantity > 0
both tests passed

With pytest you would not call the tests yourself: mark make_test_db with @pytest.fixture, take db as a test argument, and run pytest. Test against an in-memory SQLite for logic, and run a smaller set against a real MySQL or PostgreSQL (in Docker, in CI) for anything dialect-specific: placeholders, types, RETURNING, locking.

Where this shows up

• LLM apps log each call (model, tokens, latency, cost) with parameterised INSERTs, in batches so logging never slows the user down.
• Training jobs stream millions of labelled rows with server-side cursors instead of loading them all at once.
• APIs and feature services serve requests from a connection pool, with credentials injected as environment variables by the deployment platform.

Common mistakes

1. Building SQL with f-strings or +. It invites injection and breaks on the first apostrophe. Put values in parameters: con.execute("... WHERE name = ?", (name,)).
2. Using another driver's placeholder. %s means nothing to sqlite3, and ? means nothing to PyMySQL or psycopg2:
   PythonCopy

   try:
       con.execute("SELECT name FROM customers WHERE city = %s", ("Dhaka",))
   except sqlite3.OperationalError as exc:
       print("OperationalError:", exc)

   OutputCopy

   OperationalError: near "%": syntax error

3. Forgetting to commit. The program runs without an error, the data is visible inside it, and it is gone when the program exits. Use with con: around every unit of work so the commit cannot be forgotten.
4. Thinking with sqlite3.connect(...) closes the connection. It only ends the transaction. Wrap it in contextlib.closing(...) or call close() in a finally.
5. Connecting to a misspelled file. sqlite3.connect("shpo.db") silently creates a new, empty database, and you get no such table: customers. Open existing files with sqlite3.connect("file:shop.db?mode=rw", uri=True), which fails if the file is missing.

Try it yourself

1. Easy: write customers_in(con, city) that returns a list of customer names in a city, sorted by customer_id, using a parameter. Try it with "Dhaka".
2. Medium: write restock(con, product_id, amount) that adds amount to a product's stock inside with con: and raises ValueError if no product has that id (hint: rowcount). Restock product 9 by 10, then try product 99.
3. Hard: copy every customer from PostgreSQL into a new SQLite table pg_customers (same columns), using psycopg2 to read and executemany to write. Dates arrive as datetime.date; store them as ISO strings. Print the number of rows copied and the first row.

Answers

# 1. Easy
def customers_in(con, city):
    rows = con.execute(
        "SELECT name FROM customers WHERE city = ? ORDER BY customer_id", (city,)
    ).fetchall()
    return [name for (name,) in rows]

print(customers_in(con, "Dhaka"))

# 2. Medium
def restock(con, product_id, amount):
    with con:
        cur = con.execute(
            "UPDATE products SET stock = stock + ? WHERE product_id = ?", (amount, product_id)
        )
        if cur.rowcount == 0:
            raise ValueError(f"no product with id {product_id}")

restock(con, 9, 10)
print(con.execute("SELECT stock FROM products WHERE product_id = 9").fetchone())
try:
    restock(con, 99, 10)
except ValueError as exc:
    print(exc)

# 3. Hard
src = psycopg2.connect(**pg_settings)
try:
    with src.cursor() as cur:
        cur.execute("SELECT customer_id, name, email, city, joined_on FROM customers ORDER BY customer_id")
        rows = [(cid, name, email, city, joined.isoformat()) for cid, name, email, city, joined in cur]
finally:
    src.close()

with con:
    con.execute("CREATE TABLE pg_customers (customer_id INTEGER PRIMARY KEY, name TEXT, "
                "email TEXT, city TEXT, joined_on TEXT)")
    con.executemany("INSERT INTO pg_customers VALUES (?, ?, ?, ?, ?)", rows)
print(len(rows), "rows copied")
print(con.execute("SELECT * FROM pg_customers ORDER BY customer_id").fetchone())

Summary

• Every Python driver follows the DB-API: connect, cursor, execute, fetchone/fetchmany/fetchall, commit/rollback, close. The placeholder style is the main difference (? in sqlite3, %s in PyMySQL and psycopg2).
• Values always go in parameters; names that must vary go through an allow-list.
• Changes are real only after commit(). with con: commits or rolls back for you but does not close the connection.
• Insert in batches with executemany inside one transaction; read large results with fetchmany or, on servers, a server-side cursor.
• Server settings come from environment variables; pools reuse connections; in-memory SQLite fixtures make database code testable.

Next: pandas and SQLAlchemy: DataFrames In and Out of SQL puts a DataFrame on top of these connections, so a whole query result becomes a table you can analyse in one line, and introduces SQLAlchemy's engines, Core and ORM.