Databases from Python: SQLite, MySQL, PostgreSQL

Python talks to SQLite, MySQL and PostgreSQL through the same interface, the DB-API (PEP 249): open a connection, get a cursor, execute a statement, fetch rows, commit writes, close. The three notebooks differ only in the driver and the connection arguments. sqlite3 ships with Python and opens a file or :memory:; PyMySQL and mysql.connector need a MySQL server; psycopg2 needs a PostgreSQL server.

RAG systems keep structured facts in tables because a lookup beats a similarity search when the answer is one exact value. The Larkspur handbook says support tickets are answered within 4 hours on business plans and within 24 hours on starter plans. As a table:

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE plans (name TEXT PRIMARY KEY, support_hours INTEGER)")
with con:                                    # commits on success, rolls back on an exception
    con.executemany("INSERT INTO plans VALUES (?, ?)",
                    [("business", 4), ("starter", 24)])

plan = "starter"                             # from the customer's account
con.execute("SELECT support_hours FROM plans WHERE name = ?", (plan,)).fetchone()   # (24,)

Values go in as parameters. The ? tells the driver to send plan as data, separately from the SQL text. Build the same query with an f-string and the value becomes part of the SQL: with plan = "starter' OR '1'='1" it returns [(4,), (24,)], every plan in the table, while the parameterised query returns []. The placeholder differs by driver, and each driver publishes it as paramstyle:

The %s placeholder looks like Python string formatting, but the driver fills it in. Pass the values as the second argument to execute, never with the % operator.

Writes happen in a transaction. Nothing an INSERT or UPDATE changes is permanent until commit(), which is why notebook 04a calls connection.commit() after each write. with con: commits when the block succeeds and rolls back when it raises: inserting a new pro plan and then a duplicate starter in one block raises IntegrityError, and the table still has 2 rows. The with block does not close the connection; call close() yourself.

pandas sits on top. pd.read_sql(query, con, params=(2,)) runs a query and returns a DataFrame, and df.to_sql("runs", con) writes one. With T5.1's baseline rows in a runs table, SQL gives the same baseline as the pandas groupby in lesson 3 of T2.1:

SELECT k, ROUND(AVG(recall), 2), ROUND(AVG(precision), 2) FROM runs GROUP BY k;
-- (1, 0.33, 0.67), (2, 0.5, 0.5), (5, 0.5, 0.2), (12, 0.5, 0.08)

For MySQL and PostgreSQL, notebook 04b passes pd.read_sql a SQLAlchemy engine built from a connection URL (mysql+mysqlconnector://user:password@host/database), which manages the connections for you.

Credentials come from the environment. The MySQL and PostgreSQL notebooks put the server host, user and password in a cell so the demo runs as is. In your own code read them from environment variables (os.environ["PGPASSWORD"], or a .env file loaded at start-up), and give the application a database user that can only run the statements it needs. A support assistant that answers from the plans table needs SELECT on that table and nothing else.

SQL returns as a retriever in T8.3, where an agent writes the query itself from the customer's question (text-to-SQL), and as a memory store on SQLite in T10.2.

The code for this lesson is in these notebooks. Colab runs one in your browser: install the packages its first cell imports and set the API keys it reads. To run every notebook as written, clone the repository and use its uv environment.

• Use a table lookup when the answer is one exact value. SQLite, MySQL and PostgreSQL share the DB-API steps; only the driver, connection arguments and placeholder change.
• Pass values as the second argument to execute, never through an f-string or the % operator, so the driver sends them as data and they cannot rewrite the query.
• Writes count only after commit(), and with con: rolls back the whole block on an error without closing the connection. Keep credentials in the environment and grant only the statements the application needs.