SQL from Python: sqlite3 and psycopg

A Python program runs SQL through a driver: a module that opens a connection to the database, sends it a statement with placeholders where the values go, hands the values over separately, and gives the rows back as Python tuples. Two drivers cover most work. sqlite3 comes with Python and runs SQLite inside your own process, on a single file. psycopg 3 is installed with pip and talks to a PostgreSQL server over a socket. Both follow the same standard, PEP 249, so the steps are the same: connect, execute with parameters, fetch, commit or roll back, close. What differs is the details that cause bugs: the placeholder style, when a transaction starts, and what a with block does to the connection. This lesson runs each step on the mart shop, shows the binding errors you will meet first, and runs the same report on PostgreSQL 18.

The shape every Python database driver shares

PEP 249, the Python Database API Specification 2.0, fixes the names. A module’s connect() returns a connection, which owns the transaction: commit(), rollback() and close(). A connection gives cursors, and a cursor’s execute(sql, params) runs one statement while fetchone(), fetchmany() and fetchall() read its rows. Both drivers add a shortcut, conn.execute(), which makes a cursor, runs the statement and returns the cursor. Each module also says which placeholder style it understands in its paramstyle attribute: "qmark" for sqlite3, which also accepts named placeholders, and "pyformat" for psycopg.

On both paths the SQL text travels with its placeholders and the values travel beside it. The database parses the statement first and only then receives the values, so a value can never change what the statement does.

A parameterised report with sqlite3

The examples share one module, mart.py, which builds the mart tables with a few more customers and orders than the practice lesson, in any connection it is given. It uses executemany(), which runs one INSERT once per tuple of a list:

"""The mart practice shop with a few more customers and orders, built in any sqlite3 connection."""

SCHEMA = """
CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  city        TEXT    NOT NULL
);
CREATE TABLE products (
  product_id  INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  category    TEXT    NOT NULL,
  price_paise INTEGER NOT NULL
);
CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
  status      TEXT    NOT NULL
);
CREATE TABLE order_items (
  order_id    INTEGER NOT NULL REFERENCES orders (order_id),
  product_id  INTEGER NOT NULL REFERENCES products (product_id),
  quantity    INTEGER NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);
"""

CUSTOMERS = [
    (1, "Asha Kulkarni", "Pune"), (2, "Vikram Nair", "Kochi"), (3, "Meera Rathore", "Jaipur"),
    (4, "Tenzin Dorjee", "Gangtok"), (5, "Farah Qureshi", "Pune"), (6, "Rohan Das", "Kochi"),
    (7, "Lakshmi Iyer", "Pune"), (8, "Arjun Mehta", "Jaipur"),
]
PRODUCTS = [
    (1, "Basmati rice 1 kg", "Grains", 14999), (2, "Toor dal 500 g", "Pulses", 8950),
    (3, "Masala chai 250 g", "Beverages", 12000), (4, "Coconut oil 1 L", "Oils", 21500),
    (5, "Ragi flour 1 kg", "Grains", 6800),
]
ORDERS = [
    (101, 1, "delivered"), (102, 2, "delivered"), (103, 1, "placed"), (104, 3, "cancelled"),
    (105, 5, "delivered"), (106, 7, "delivered"), (107, 5, "delivered"), (108, 6, "placed"),
    (109, 8, "delivered"), (110, 7, "cancelled"),
]
ORDER_ITEMS = [
    (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3), (105, 3, 1),
    (105, 4, 2), (106, 1, 1), (107, 5, 3), (108, 2, 2), (109, 1, 1), (109, 3, 1), (110, 4, 1),
]


def build(conn):
    """Creates the four tables and their rows, then commits."""
    conn.executescript(SCHEMA)
    conn.executemany("INSERT INTO customers VALUES (?, ?, ?)", CUSTOMERS)
    conn.executemany("INSERT INTO products VALUES (?, ?, ?, ?)", PRODUCTS)
    conn.executemany("INSERT INTO orders VALUES (?, ?, ?)", ORDERS)
    conn.executemany("INSERT INTO order_items VALUES (?, ?, ?)", ORDER_ITEMS)
    conn.commit()

The report below asks what each customer of one city spent on delivered orders. The city and the status are values, so they are passed as a tuple; the second query passes a dict to named placeholders and reads columns by name:

import sqlite3

from mart import build

conn = sqlite3.connect(":memory:")
build(conn)

# What each customer of a city spent on orders with a given status. The city and the
# status are values, so they travel as parameters, never inside the SQL text.
SPEND = """
SELECT c.name, count(DISTINCT o.order_id) AS orders,
       sum(i.quantity * p.price_paise) AS spent_paise
FROM customers c
JOIN orders o      ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p    ON p.product_id = i.product_id
WHERE c.city = ? AND o.status = ?
GROUP BY c.customer_id
ORDER BY spent_paise DESC, c.name
"""
for name, orders, spent in conn.execute(SPEND, ("Pune", "delivered")):
    print(f"{name:<14} {orders} order(s)  {spent / 100:>8.2f} rupees")

# The same placeholders by name, with a dict, and rows you can read by column name.
conn.row_factory = sqlite3.Row
row = conn.execute(
    "SELECT count(*) AS n, min(price_paise) AS cheapest FROM products WHERE category = :cat",
    {"cat": "Grains"},
).fetchone()
print(f"Grains: {row['n']} products, the cheapest at {row['cheapest']} paise")
print("columns:", row.keys())
conn.close()

Farah Qureshi  2 order(s)    754.00 rupees
Asha Kulkarni  1 order(s)    389.48 rupees
Lakshmi Iyer   1 order(s)    149.99 rupees
Grains: 2 products, the cheapest at 6800 paise
columns: ['n', 'cheapest']

Three things are worth copying from it:

• The values are a tuple, in placeholder order. ("Pune", "delivered") fills the two ? from left to right.
• A cursor is an iterator. The for loop takes the rows one at a time, so a large result never has to fit in memory at once; fetchall() would build the whole list first.
• sqlite3.Row gives names to columns. With conn.row_factory = sqlite3.Row, row["cheapest"] works as well as row[1], and row.keys() lists the column names, which are the aliases the query gave them.

The money stays in whole paise until the moment it is printed. spent / 100 happens in Python, after SQL has added exact integers, so no rounding error can creep into a total.

Placeholders, and the mistakes they catch

Every binding error has a clear message once you know what it means. Here are the six you are most likely to meet, each run on purpose, followed by the two fixes that come up most often:

import sqlite3

from mart import build

conn = sqlite3.connect(":memory:")
build(conn)

ATTEMPTS = [
    # A string is a sequence of characters, so "Pune" is four parameters.
    ("a string instead of a tuple", "SELECT name FROM customers WHERE city = ?", "Pune"),
    # Brackets alone do not make a tuple: ("Pune") is still the string "Pune".
    ("brackets without a comma", "SELECT name FROM customers WHERE city = ?", ("Pune")),
    ("a one-item tuple", "SELECT name FROM customers WHERE city = ?", ("Pune",)),
    # Named placeholders need a dict (an error since Python 3.14).
    ("a tuple for :name", "SELECT name FROM customers WHERE city = :city", ("Pune",)),
    # One placeholder takes one value, never a list.
    ("a list for one ?", "SELECT name FROM customers WHERE city IN (?)", (["Pune", "Kochi"],)),
    # Parameters stand for values only, never for table or column names.
    ("a table name as a parameter", "SELECT name FROM ? WHERE city = 'Pune'", ("customers",)),
]
for label, sql, params in ATTEMPTS:
    try:
        rows = conn.execute(sql, params).fetchall()
        print(f"{label}: {rows}")
    except sqlite3.Error as e:
        print(f"{label}: {type(e).__name__}: {e}")

# The fix for a list: one placeholder per value.
cities = ["Pune", "Kochi"]
marks = ", ".join("?" for _ in cities)
sql = f"SELECT name FROM customers WHERE city IN ({marks}) ORDER BY name"
print(sql)
print([name for (name,) in conn.execute(sql, cities)])

# The fix for a name: choose it from a fixed set, never from the input itself.
SORTABLE = {"name": "name", "city": "city, name"}
asked = "city"
order_by = SORTABLE.get(asked, "name")
print([name for (name,) in conn.execute(f"SELECT name FROM customers ORDER BY {order_by} LIMIT 3")])
conn.close()

a string instead of a tuple: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.
brackets without a comma: ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 4 supplied.
a one-item tuple: [('Asha Kulkarni',), ('Farah Qureshi',), ('Lakshmi Iyer',)]
a tuple for :name: ProgrammingError: Binding 1 (':city') is a named parameter, but you supplied a sequence which requires nameless (qmark) placeholders.
a list for one ?: ProgrammingError: Error binding parameter 1: type 'list' is not supported
a table name as a parameter: OperationalError: near "?": syntax error
SELECT name FROM customers WHERE city IN (?, ?) ORDER BY name
['Asha Kulkarni', 'Farah Qureshi', 'Lakshmi Iyer', 'Rohan Das', 'Vikram Nair']
['Tenzin Dorjee', 'Arjun Mehta', 'Meera Rathore']

• A string is a sequence. execute(sql, "Pune") gives sqlite3 four values, one per character, for one placeholder. ("Pune") is the same string, because brackets alone do not make a tuple: write ("Pune",) or ["Pune"].
• Named placeholders need a mapping. Since Python 3.14, :city with a tuple is a ProgrammingError; earlier versions only warned. Pass a dict.
• One placeholder holds one value. For IN, build one ? per value, as the fix does, and pass the list itself.
• Names are not values. A placeholder can stand where a literal could, never for a table or a column, so FROM ? is a syntax error. When the user chooses a sort column, map their choice onto a fixed set of names in your own code, as SORTABLE does, and never put their text in the statement.

Commit, roll back and close

How sqlite3 handles transactions depends on one setting, autocommit, added in Python 3.12. Its default today is LEGACY_TRANSACTION_CONTROL, the behaviour of older versions: sqlite3 sends a hidden BEGIN before the first INSERT, UPDATE, DELETE or REPLACE, and nothing is saved until you call commit(). The example uses a database file, so that it can close the connection and look again:

import sqlite3
from contextlib import closing

from mart import build

# A database file, so that we can close it and look again.
with closing(sqlite3.connect("shop.db")) as conn:
    build(conn)
    print("autocommit is the legacy mode:", conn.autocommit == sqlite3.LEGACY_TRANSACTION_CONTROL)

    conn.execute("UPDATE products SET price_paise = 15999 WHERE product_id = 1")
    print("after an UPDATE, in a transaction:", conn.in_transaction)
# closing() closed the connection without a commit, so the UPDATE is gone.
with closing(sqlite3.connect("shop.db")) as conn:
    print("price after reopening:", conn.execute("SELECT price_paise FROM products WHERE product_id = 1").fetchone()[0])

    # "with conn" commits when the block ends normally ...
    with conn:
        conn.execute("UPDATE products SET price_paise = 15999 WHERE product_id = 1")
    # ... and rolls back when it ends with an exception: the CHECK on quantity fails.
    try:
        with conn:
            conn.execute("INSERT INTO orders VALUES (111, 2, 'placed')")
            conn.execute("INSERT INTO order_items VALUES (111, 3, 0)")
    except sqlite3.IntegrityError as e:
        print("rolled back:", e)
    print("order 111 exists:", conn.execute("SELECT count(*) FROM orders WHERE order_id = 111").fetchone()[0] == 1)
    # The with block does not close the connection: it still answers.
    print("still open:", conn.execute("SELECT price_paise FROM products WHERE product_id = 1").fetchone()[0])

# autocommit=False: always inside a transaction, as PEP 249 describes; commit() starts the next one.
with closing(sqlite3.connect("shop.db", autocommit=False)) as conn:
    print("autocommit=False, in a transaction before any statement:", conn.in_transaction)
    conn.execute("UPDATE products SET price_paise = 14999 WHERE product_id = 1")
    conn.commit()
    print("after commit(), in a transaction again:", conn.in_transaction)

autocommit is the legacy mode: True
after an UPDATE, in a transaction: True
price after reopening: 14999
rolled back: CHECK constraint failed: quantity > 0
order 111 exists: False
still open: 15999
autocommit=False, in a transaction before any statement: True
after commit(), in a transaction again: True

• Closing does not commit. The first UPDATE opened a transaction (in_transaction is True), and closing() closed the connection without a commit, so the price was back at 14999 when the file was opened again.
• with conn: manages the transaction, not the connection. It committed the second UPDATE, rolled back order 111 when its item broke the CHECK constraint, and left the connection open: the next query still answered. Close it yourself, or wrap it in contextlib.closing() as the example does.
• autocommit=False is the recommended setting. The Python documentation suggests it for new code: sqlite3 then keeps a transaction open at all times, as PEP 249 describes, and commit() starts the next one at once.

A failed statement in SQLite undoes only itself. The transaction stays open, and a commit() afterwards would keep everything that worked before it. That is why the rollback has to come from your code, and with conn: is the simplest way to make sure it does.

The SQLite inside Python is a version too

The SQL your program may use depends on the SQLite library that Python was built with, not on the Python version. This example prints both and tries three features that arrived in different SQLite releases:

import sqlite3
import sys

# The SQLite library inside Python, not the Python version, decides which SQL works.
print(f"Python {sys.version_info.major}.{sys.version_info.minor}.{sys.version_info.micro}, SQLite {sqlite3.sqlite_version}")

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
FEATURES = [
    ("RETURNING (SQLite 3.35)", "INSERT INTO t (name) VALUES ('Asha') RETURNING id"),
    ("string_agg() (SQLite 3.44)", "SELECT string_agg(name, ', ') FROM t"),
    ("concat() (SQLite 3.44)", "SELECT concat('a', 'b')"),
]
for label, sql in FEATURES:
    try:
        print(f"{label}: works, {conn.execute(sql).fetchall()}")
    except sqlite3.OperationalError as e:
        print(f"{label}: {e}")
conn.close()

Python 3.14.8, SQLite 3.51.0
RETURNING (SQLite 3.35): works, [(1,)]
string_agg() (SQLite 3.44): works, [('Asha',)]
concat() (SQLite 3.44): works, [('ab',)]

Python 3.14.2, SQLite 3.39.0
RETURNING (SQLite 3.35): works, [(1,)]
string_agg() (SQLite 3.44): no such function: string_agg
concat() (SQLite 3.44): no such function: concat

PostgreSQL with psycopg 3

Psycopg 3 has the same shape with PostgreSQL’s habits. You install it with pip install "psycopg[binary]", connect with psycopg.connect("dbname=mart user=app") and run conn.execute(sql, params) exactly as before. What changes:

Psycopg 3 also sends the statement and the values to the server separately, using PostgreSQL’s extended query protocol: your %s placeholders reach the server as $1, $2 and so on. PostgreSQL’s own PREPARE and EXECUTE commands do the same from the psql shell, so this session runs the report of report.py the way psycopg would send it, on a throwaway PostgreSQL 18.6 server:

-- What psycopg sends for conn.execute(SPEND, ("Pune", "delivered")): the statement with $1 and $2, and the
-- values apart. PREPARE and EXECUTE do the same from psql.
PREPARE spend(text, text) AS
SELECT c.name, count(DISTINCT o.order_id) AS orders,
       sum(i.quantity * p.price_paise) AS spent_paise
FROM customers c
JOIN orders o      ON o.customer_id = c.customer_id
JOIN order_items i ON i.order_id = o.order_id
JOIN products p    ON p.product_id = i.product_id
WHERE c.city = $1 AND o.status = $2
GROUP BY c.customer_id
ORDER BY spent_paise DESC, c.name;

EXECUTE spend('Pune', 'delivered');
-- A value that looks like SQL is still only a value: no city has this name.
EXECUTE spend('Pune'' OR ''1''=''1', 'delivered');

-- After an error PostgreSQL refuses every statement until the transaction ends, and
-- COMMIT then rolls back: the order inserted before the error is gone too.
BEGIN;
INSERT INTO orders VALUES (111, 2, 'placed');
INSERT INTO order_items VALUES (111, 3, 0);
SELECT count(*) AS orders FROM orders;
COMMIT;
SELECT count(*) AS orders FROM orders;

-- The mart tables and rows of mart.py, in PostgreSQL.
CREATE TABLE customers (customer_id integer PRIMARY KEY, name text NOT NULL, city text NOT NULL);
CREATE TABLE products (product_id integer PRIMARY KEY, name text NOT NULL, category text NOT NULL,
                       price_paise integer NOT NULL);
CREATE TABLE orders (order_id integer PRIMARY KEY, customer_id integer NOT NULL REFERENCES customers,
                     status text NOT NULL);
CREATE TABLE order_items (order_id integer NOT NULL REFERENCES orders, product_id integer NOT NULL REFERENCES products,
                          quantity integer NOT NULL CHECK (quantity > 0), PRIMARY KEY (order_id, product_id));
INSERT INTO customers VALUES (1, 'Asha Kulkarni', 'Pune'), (2, 'Vikram Nair', 'Kochi'), (3, 'Meera Rathore', 'Jaipur'),
  (4, 'Tenzin Dorjee', 'Gangtok'), (5, 'Farah Qureshi', 'Pune'), (6, 'Rohan Das', 'Kochi'), (7, 'Lakshmi Iyer', 'Pune'),
  (8, 'Arjun Mehta', 'Jaipur');
INSERT INTO products VALUES (1, 'Basmati rice 1 kg', 'Grains', 14999), (2, 'Toor dal 500 g', 'Pulses', 8950),
  (3, 'Masala chai 250 g', 'Beverages', 12000), (4, 'Coconut oil 1 L', 'Oils', 21500), (5, 'Ragi flour 1 kg', 'Grains', 6800);
INSERT INTO orders VALUES (101, 1, 'delivered'), (102, 2, 'delivered'), (103, 1, 'placed'), (104, 3, 'cancelled'),
  (105, 5, 'delivered'), (106, 7, 'delivered'), (107, 5, 'delivered'), (108, 6, 'placed'), (109, 8, 'delivered'),
  (110, 7, 'cancelled');
INSERT INTO order_items VALUES (101, 1, 2), (101, 2, 1), (102, 4, 1), (103, 3, 2), (103, 5, 1), (104, 2, 3), (105, 3, 1),
  (105, 4, 2), (106, 1, 1), (107, 5, 3), (108, 2, 2), (109, 1, 1), (109, 3, 1), (110, 4, 1);

from pg_session import server

# The report of report.py on PostgreSQL 18, with the placeholders psycopg sends.
with server() as psql:
    psql("mart.sql", quiet=True)
    psql("report.sql")

# PostgreSQL 18.6
$ psql -X -q -f mart.sql
$ psql -X -f report.sql
PREPARE
     name      | orders | spent_paise 
---------------+--------+-------------
 Farah Qureshi |      2 |       75400
 Asha Kulkarni |      1 |       38948
 Lakshmi Iyer  |      1 |       14999
(3 rows)

 name | orders | spent_paise 
------+--------+-------------
(0 rows)

BEGIN
INSERT 0 1
psql:report.sql:22: ERROR:  new row for relation "order_items" violates check constraint "order_items_quantity_check"
DETAIL:  Failing row contains (111, 3, 0).
psql:report.sql:23: ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK
 orders 
--------
     10
(1 row)

The report returns the same three customers and totals as sqlite3 did, from $1 and $2 instead of ?. The second EXECUTE passes a city of Pune' OR '1'='1, and PostgreSQL looks for a city with exactly that name and finds none. Then the transaction: the first INSERT worked, the second broke the CHECK constraint, and from then on PostgreSQL refused even a SELECT with “current transaction is aborted”. The COMMIT came back as ROLLBACK, and the count shows 10 orders: order 111 was rolled back with everything else. In psycopg the refused statement raises InFailedSqlTransaction, and the connection is usable again only after conn.rollback().

Server-side binding has one limit to know about. Parameters work in SELECT, INSERT, UPDATE and DELETE, but not in SET, NOTIFY or statements that define the schema: there, psycopg’s documentation points you to functions such as set_config() or to building the statement with psycopg.sql.

Connections in a program that keeps running

A script can open one connection, do its work and close it. A web service handles many requests at once and should not open a new connection for each one, because connecting to a server takes time. Psycopg’s pool, in the separate psycopg_pool package, keeps a set of open connections: with pool.connection() as conn: borrows one, commits or rolls back at the end of the block, and returns it to the pool. With sqlite3, give each thread its own connection; by default a connection refuses to be used from another thread, and each one waits up to 5 seconds for a lock before it raises “database is locked”.

Your turn

"""Reports on the mart shop."""


def top_spenders(conn, city, n):
    """The n customers of `city` who spent most on delivered orders: [(name, spent_paise), ...]."""
    return []

import sqlite3

from shop import top_spenders

SCHEMA = """
CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT NOT NULL);
CREATE TABLE products (product_id INTEGER PRIMARY KEY, name TEXT NOT NULL, category TEXT NOT NULL,
                       price_paise INTEGER NOT NULL);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, status TEXT NOT NULL);
CREATE TABLE order_items (order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL,
                          PRIMARY KEY (order_id, product_id));
"""


def shop(customers, orders, items):
    """An in-memory mart with two products (100 and 250 paise) and the given rows."""
    conn = sqlite3.connect(":memory:")
    conn.executescript(SCHEMA)
    conn.executemany("INSERT INTO products VALUES (?, ?, ?, ?)", [(1, "Rice", "Grains", 100), (2, "Oil", "Oils", 250)])
    conn.executemany("INSERT INTO customers VALUES (?, ?, ?)", customers)
    conn.executemany("INSERT INTO orders VALUES (?, ?, ?)", orders)
    conn.executemany("INSERT INTO order_items VALUES (?, ?, ?)", items)
    return conn


def test_biggest_spender_first():
    """Delivered orders of one city, biggest spender first"""
    conn = shop(
        [(1, "Asha", "Pune"), (2, "Ravi", "Pune"), (3, "Meera", "Jaipur")],
        [(10, 1, "delivered"), (11, 2, "delivered"), (12, 3, "delivered")],
        [(10, 1, 2), (11, 2, 1), (11, 1, 1), (12, 2, 4)],
    )
    assert top_spenders(conn, "Pune", 5) == [("Ravi", 350), ("Asha", 200)]


def test_only_delivered_orders_count():
    """Placed and cancelled orders add nothing, and a customer without a delivered order is left out"""
    conn = shop(
        [(1, "Asha", "Pune"), (2, "Ravi", "Pune")],
        [(10, 1, "delivered"), (11, 1, "cancelled"), (12, 2, "placed")],
        [(10, 1, 1), (11, 2, 3), (12, 2, 2)],
    )
    assert top_spenders(conn, "Pune", 5) == [("Asha", 100)]


def test_ties_and_n():
    """Equal spenders come in name order, and n limits the rows"""
    conn = shop(
        [(1, "Zoya", "Kochi"), (2, "Anil", "Kochi"), (3, "Mohan", "Kochi")],
        [(10, 1, "delivered"), (11, 2, "delivered"), (12, 3, "delivered")],
        [(10, 2, 1), (11, 2, 1), (12, 1, 1)],
    )
    assert top_spenders(conn, "Kochi", 2) == [("Anil", 250), ("Zoya", 250)]
    assert top_spenders(conn, "Kochi", 0) == []


def test_city_is_a_value():
    """A city that looks like SQL matches no customer and breaks nothing"""
    conn = shop([(1, "Asha", "Pune")], [(10, 1, "delivered")], [(10, 1, 1)])
    assert top_spenders(conn, "Pune' OR '1'='1", 5) == []
    assert top_spenders(conn, "Pune", 5) == [("Asha", 100)]

Interview questions

Fresher · What does with conn: do for an sqlite3 connection, and how is psycopg 3 different?

For sqlite3 the block manages the transaction: if it ends normally the open transaction is committed, and if it ends with an exception the transaction is rolled back and the exception carries on. It does not close the connection, so a program still calls close() or wraps the connection in contextlib.closing(). In psycopg 3, with psycopg.connect(...) as conn: commits or rolls back in the same way and then closes the connection; a block that must commit as one unit while the connection stays open is with conn.transaction():. Mixing up the two habits leaves sqlite3 connections open or makes psycopg code reconnect for every block.

Fresher · Why does conn.execute("... WHERE city = ?", ("Pune")) fail, and how do you pass one value?

The second argument must be a sequence of values, one per placeholder. ("Pune") is not a tuple: the brackets only group, so it is the string "Pune", and a string is a sequence of its characters. sqlite3 therefore sees four values for one placeholder and raises ProgrammingError: Incorrect number of bindings supplied. A one-item tuple needs a comma, ("Pune",), or use a list, ["Pune"]. Psycopg has the same rule for %s. With named placeholders, pass a dict instead: {"city": "Pune"} for :city in sqlite3 or %(city)s in psycopg.

Key takeaways

• Python drivers follow PEP 249: connect, execute with parameters, fetch, commit or roll back, close.
• sqlite3 uses ? and :name; psycopg uses %s and %(name)s. Values always travel apart from the SQL text, and names of tables and columns come from a fixed set in your code.
• In sqlite3’s default mode nothing is saved until commit(), with conn: commits or rolls back but does not close, and autocommit=False is the recommended setting for new code.
• In psycopg 3 every statement starts a transaction, a failed statement blocks the rest until rollback(), and the connection’s with block closes it.
• Check sqlite3.sqlite_version where your code runs: the browser’s SQLite is older than a current Python’s.