all Python 3sqlite3term

sqlite3.Cursor

connections and queries

A cursor runs SQL statements and hands back the results: fetchone(), fetchmany(), fetchall() or plain iteration in a for loop.

Syntax

cur = conn.cursor(); cur.execute(sql, params); cur.fetchall()

Pass values through ? placeholders and a tuple of parameters, never an f-string: f"... WHERE name = '{name}'" opens the door to SQL injection, while ? escapes everything. A single parameter is still a tuple: ('ann',), not 'ann' — a three-letter string counts as three parameters and raises ProgrammingError. A cursor is consumed once: after fetchall() the next fetchone() returns None. Placeholders bind values, not table or column names.

Examples

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE users(name, secret)')
conn.execute("INSERT INTO users VALUES ('ann', 'pin1'), ('bob', 'pin2')")
name = "x' OR '1'='1"  # «имя» от злоумышленника
bad = conn.execute(f"SELECT secret FROM users WHERE name = '{name}'")
print(len(bad.fetchall()))  # → 2 — инъекция вытащила всё
good = conn.execute('SELECT secret FROM users WHERE name = ?', (name,))
print(len(good.fetchall()))  # → 0
conn.close()

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE u(name)')
cur.executemany('INSERT INTO u VALUES (?)', [('ann',), ('bob',)])
print(cur.rowcount)  # → 2
print(cur.execute('SELECT name FROM u').fetchall(), cur.fetchone())  # → [('ann',), ('bob',)] None
try:
    cur.execute('SELECT * FROM u WHERE name = ?', 'ann')
except sqlite3.ProgrammingError as e:
    print(e)  # → Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.
conn.close()

See also

Common errors

Open in the full glossarydocs.python.orgReport an inaccuracy