With a connection in hand, the actual work begins: running SELECT, INSERT, UPDATE, and DELETE statements from Python. The DB-API provides two methods (execute and executemany), three ways to read results (fetchone, fetchall, fetchmany), and a transaction model that decides whether writes survive a crash. This lesson covers all of that, plus the patterns that come up often: bulk inserts, getting back the id of a row that was just inserted, paginating large result sets, and building dynamic queries without falling into SQL injection.

The DB-API pattern is the same regardless of the target database.

Every example in this lesson builds on this pattern.

A SELECT returns rows. The cursor holds a pointer into the result set, and rows are read out as needed.

Three styles, three trade-offs. fetchone returns one row or None. fetchall loads every remaining row into a list. Iterating the cursor reads row by row in constant memory and fits large result sets.

fetchall() on a result set with a million rows pulls every row into a Python list before the code sees the first one. Switching to iteration (for row in cur) or fetchmany(1000) keeps memory roughly constant and lets the application start processing earlier.

When the query returns no rows, fetchone() returns None and fetchall() returns []. Check for None before unpacking, or the code will raise TypeError: 'NoneType' is not subscriptable on the next access.

Every value in a query that came from outside the code (a user, a file, a network response) must go in as a parameter, not as part of the SQL string. Here is the practical reason.

The wrong form pastes the value directly into the SQL. The injected text closes the string, ends the statement with ), runs a destructive DROP TABLE, and comments out the rest. The right form sends the value separately, and the database stores the entire string (including the quotes and semicolons) as a single text value, which is harmless.

Parameter binding also handles type conversion automatically. Python None becomes SQL NULL. Python bool becomes the database's boolean (or 0/1 in SQLite). Python datetime becomes an ISO 8601 string (or a native timestamp in PostgreSQL via psycopg). None of it needs manual formatting.

Placeholder syntax differs per driver. The DB-API spec leaves it open and each driver picks one of five styles.

The %s in psycopg and mysql-connector-python looks like printf-style formatting, but it isn't. It's a placeholder, and the driver still does proper escaping. Never use Python's % operator or f-strings to fill those placeholders.

A common pattern with a dict of values is the named form:

The named form makes the SQL self-documenting and allows reordering the dict freely without touching the SQL. For short queries, positional is fine. For long queries with many columns, named placeholders are easier to maintain.

A basic INSERT writes one row. The DB-API's execute runs it.

Columns left unspecified use their declared defaults (or NULL if there's no default and the column allows it). When the SQL lists columns, they can be reordered freely; when the column list is omitted, values must be provided for every column in the table in the order the schema defines.

The explicit column list is the safer style. It survives schema additions: if a description column gets added later with a default, the explicit form still works without changes. The implicit form breaks because the column count no longer matches.

For inserting many rows, use executemany. It runs the same SQL against a sequence of parameter tuples in a single batch.

The difference between executemany and a loop matters more as the row count grows. A loop with one commit per row is the slowest combination: each commit forces a disk sync. A loop with one commit at the end (no executemany) is much faster because all the writes happen in one transaction. executemany inside one transaction is faster still because the driver can batch the statement preparation and send fewer round trips to the server.

A rough benchmark for 10,000 inserts on a local PostgreSQL:

For large bulk loads (millions of rows), PostgreSQL's COPY command (or MySQL's LOAD DATA) is faster than any DB-API loop because it skips per-row parsing. psycopg exposes COPY through cur.copy(...). For day-to-day bulk inserts of thousands of rows at a time, executemany is the right balance of speed and simplicity.

Commit frequency is a major performance lever for bulk writes. A commit per row is up to 100x slower than one commit per batch on databases with fsync. For very large batches, commit every few thousand rows so a crash doesn't lose hours of work.

A common need: after inserting a row with an auto-generated primary key, the application wants the value the database assigned. Each database exposes this differently.

For SQLite, the cursor has a lastrowid attribute that holds the id of the last inserted row.

lastrowid is a DB-API attribute, so most drivers support it. The catch is that for executemany, lastrowid is the id of the last row inserted, not all of them. For one-by-one inserts, it works as expected.

For PostgreSQL, the idiomatic way is to add a RETURNING clause to the INSERT. The clause turns the insert into a statement that returns rows, so the result is read back with fetchone.

RETURNING is more flexible than lastrowid because it can return any column (including computed values from triggers or defaults), and it works correctly with bulk inserts: RETURNING id after an executemany-style INSERT ... VALUES (...), (...), (...) returns all the new ids. Most other databases now support RETURNING too: PostgreSQL has had it since 8.2, SQLite added it in 3.35, MariaDB has it, and SQL Server has the equivalent OUTPUT clause.

For MySQL specifically, lastrowid (or cursor.lastrowid in mysql-connector-python) is the conventional approach because MySQL's RETURNING support is recent and not always available.

The table below summarizes the common pattern per database:

UPDATE changes values in existing rows. DELETE removes them. Both follow the same execute pattern as INSERT and both return a row count through cur.rowcount.

rowcount is the number of rows affected by the last execute. For UPDATE it's the matched-and-changed count. For DELETE it's the removed count. For SELECT, the value depends on the driver: SQLite reports -1 until all rows are fetched, while PostgreSQL gives the actual count immediately. Don't rely on rowcount for SELECT; use len(rows) after fetchall() instead.

A common bug with UPDATE and DELETE is forgetting the WHERE clause. DELETE FROM products (no WHERE) deletes every row in the table. UPDATE products SET price = 0 (no WHERE) zeros every product's price. The database will not prevent this; the statement is valid SQL. Wrap dangerous statements in a transaction so they can be rolled back, and prefer parameterized queries with explicit conditions.

The check in the helper function catches "missing id" and "the product doesn't exist" before they turn into untraceable failures. For bulk operations, log the row count to keep an audit trail.

Every write goes through a transaction. A transaction is the unit that either succeeds entirely or rolls back entirely. If three INSERTs are in one transaction and the third one fails, the first two are undone too, and the table looks like none of them ever ran.

The DB-API model defaults to "implicit transactions": the first write opens a transaction, and it stays open until conn.commit() or conn.rollback() is called. That's why every write example in this lesson commits at the end.

If either statement had raised (say, a constraint violation), the except block would roll back, and the account balance would still be 100 with no order. Without the try/except/rollback, a partial failure would leave the database in an inconsistent state.

The cleaner form is the connection context manager. sqlite3, psycopg, and most modern drivers support with conn: to commit on success and roll back on exception.

The semantics differ slightly per driver (psycopg2 and sqlite3 keep the connection open after the block; psycopg v3 closes it). Check the driver's docs, then use the idiom that fits.

A different model is auto-commit. In auto-commit mode, every statement is its own transaction: write it, and it's immediately durable. Some drivers expose this as autocommit=True. It fits read-only work and one-off scripts where atomicity doesn't matter. It's the wrong fit when multi-statement units of work need to be atomic.

When in doubt, leave auto-commit off and write explicit transactions. The performance overhead is minimal, and the correctness benefit is large.

When a result set is too big to send to the user (or to load into memory) all at once, paginate it. The basic SQL pattern uses LIMIT and OFFSET.

LIMIT 5 OFFSET 10 means "skip the first 10 rows, then take 5." Combined with ORDER BY (always include one, or pagination is non-deterministic), it produces stable pages.

OFFSET has a hidden cost. To return page 1000 of 10-per-page results, the database has to scan and discard the first 9990 rows, which gets slower as the offset grows. For deep pagination on large tables, keyset pagination (also called cursor pagination) is much faster: instead of "skip 9990 rows," ask "give me the next 10 rows after the last id seen."

The query uses the index on id to jump straight to the right starting point, regardless of how far into the result set the caller is. Page 1000 costs the same as page 1.

LIMIT 10 OFFSET 100000 is roughly O(offset). The database scans through the first 100,000 rows before returning the 10 requested. Keyset pagination is O(log n) per page when the order-by column is indexed. Use OFFSET for small offsets and keyset for deep pagination.

Sometimes the query depends on user input: filter by name, by minimum price, by category. The temptation is to build the SQL with f-strings. Don't. Use parameter placeholders for values, and build the SQL structure separately for the parts that vary.

The structure (which columns the WHERE clause references) is built from a fixed set of strings the function owns. The values go in as parameters. A user can pass any name or price; none of it touches the SQL string.

If the column to sort by is user-controlled (a "sort by price, name, or stock" dropdown, for example), validate it against an allow-list. Column names can't be parameterized.

The f-string interpolates only values that have been checked against an allow-list, so it can't carry untrusted SQL. The list of allowed columns is small and lives in code. This pattern applies any time the user controls a column name, table name, or direction; parameter binding can't help, but a strict allow-list can.

For multi-value filters (WHERE id IN (...) with a variable-length list), generate the placeholders dynamically.

The placeholders string is "?,?,?" for three ids. The values still go in as parameters, so the function is safe even when the ids came from user input. PostgreSQL has a cleaner WHERE id = ANY(%s) form that takes a list directly, but the IN (?, ?, ?) shape is portable across drivers.

The end-to-end flow for a typical query, from Python to database and back:

The driver handles parameter binding. The database parses the SQL once and can reuse the compiled form for the same SQL with different parameters (a technique called prepared statements). Most drivers do this transparently.

Pulling the lesson together: a small order service that creates an order, attaches items to it, computes the total, and returns the order id. The function uses parameter binding for every value, executemany for the items, lastrowid to get the new order id, and a transaction so a half-finished order is never committed.

Every read is parameter-bound. The whole sequence (look up prices, create the order, insert items, decrement stock) is wrapped in with conn:, so a failure anywhere in the middle leaves the database untouched. If the stock check raises ValueError, the order row is rolled back and the stock isn't decremented. That's the atomicity guarantee transactions provide, applied to a real workflow.