Python course menu
Python Database Connections: sqlite3, Parameterised Queries and Transactions
Connect Python to a SQL database: create tables and insert rows with sqlite3, run safe parameterised queries, fetch results as tuples or dictionaries, avoid SQL injection, use transactions, and connect to MySQL or PostgreSQL.
On this page
Most real applications keep their data in a database. Python talks to databases by sending SQL statements through a driver. Python ships with sqlite3, a driver for SQLite, a complete SQL database stored in a single file, which makes it perfect for learning and for small apps. The same patterns work with PostgreSQL and MySQL.
If SQL itself is new to you, the SQL course covers the queries used here.
Connect, create, insert, query#
import sqlite3
conn = sqlite3.connect("shop.db") # creates the file if it does not exist
cur = conn.cursor()
cur.execute("""
CREATE TABLE IF NOT EXISTS customers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city TEXT
)
""")
cur.executemany(
"INSERT INTO customers (name, city) VALUES (?, ?)",
[("Asha", "Mumbai"), ("Ravi", "Delhi"), ("Meera", "Pune")],
)
conn.commit() # save the changes
for row in cur.execute("SELECT id, name, city FROM customers ORDER BY name"):
print(row)
conn.close()(1, 'Asha', 'Mumbai')
(3, 'Meera', 'Pune')
(2, 'Ravi', 'Delhi')Fetching results#
The next examples use an open connection to the same three customers (reconnect with conn = sqlite3.connect("shop.db") after the close() above):
city = "Delhi"
row = conn.execute("SELECT name FROM customers WHERE city = ?", (city,)).fetchone()
print(row, row[0])
print(conn.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
print(conn.execute("SELECT name FROM customers WHERE city = 'Nowhere'").fetchone())('Ravi',) Ravi
3
Nonefetchone()returns one row as a tuple, orNoneif there are no rows.fetchall()returns a list of all rows.- A connection can call
execute()directly, without creating a cursor first.
Note the parameters are passed as a tuple: (city,) with a trailing comma.
Rows as dictionaries#
Set row_factory to read columns by name:
conn.row_factory = sqlite3.Row
r = conn.execute("SELECT * FROM customers").fetchone()
print(r["name"], r["city"], dict(r))Asha Mumbai {'id': 1, 'name': 'Asha', 'city': 'Mumbai'}Never build SQL with f-strings: SQL injection#
Inserting user input directly into SQL lets an attacker change the query:
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE users (name TEXT, is_admin INTEGER)")
conn.execute("INSERT INTO users VALUES ('asha', 0), ('admin', 1)")
user_input = "x' OR '1'='1" # what an attacker might type into a login form
unsafe = f"SELECT name FROM users WHERE name = '{user_input}'"
print(unsafe)
print(conn.execute(unsafe).fetchall())
print(conn.execute("SELECT name FROM users WHERE name = ?", (user_input,)).fetchall())SELECT name FROM users WHERE name = 'x' OR '1'='1'
[('asha',), ('admin',)]
[]The f-string version returned every user, including the admin, because the input rewrote the WHERE clause. With a ? placeholder, the driver treats the input purely as a value, so it matches nobody.
Transactions#
Using the connection as a context manager commits if the block succeeds and rolls back if it raises an exception, which is exactly what a money transfer needs:
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE accounts (name TEXT PRIMARY KEY, balance INTEGER CHECK (balance >= 0))")
conn.execute("INSERT INTO accounts VALUES ('Asha', 1000), ('Ravi', 200)")
conn.commit()
def transfer(src, dst, amount):
try:
with conn:
conn.execute("UPDATE accounts SET balance = balance + ? WHERE name = ?", (amount, dst))
conn.execute("UPDATE accounts SET balance = balance - ? WHERE name = ?", (amount, src))
print("transfer ok")
except sqlite3.IntegrityError as e:
print("transfer failed:", e)
transfer("Asha", "Ravi", 300)
transfer("Ravi", "Asha", 5000)
print(conn.execute("SELECT * FROM accounts ORDER BY name").fetchall())transfer ok
transfer failed: CHECK constraint failed: balance >= 0
[('Asha', 700), ('Ravi', 500)]In the failed transfer, Asha had already been credited 5,000 before Ravi's debit was rejected. Because both updates were in one transaction, the credit was rolled back too. See SQL Transactions for the theory.
Other databases#
The same DB-API pattern (connect, cursor, execute with parameters, commit) works across drivers:
# PostgreSQL with psycopg (version 3)
import psycopg
with psycopg.connect("dbname=shop user=app password=...") as conn:
rows = conn.execute("SELECT name FROM customers WHERE city = %s", ("Pune",)).fetchall()Keep connection details such as passwords in environment variables, not in code. Larger applications often use an ORM like SQLAlchemy or Django's ORM, which maps tables to Python classes.
Common mistakes#
Practice#
Practice exercise
Create an in-memory orders table with columns id, customer and amount, insert four orders (Asha 250, Ravi 560, Asha 120, Meera 90), then print each customer whose total is over 100 as Asha: 2 orders, ₹370, highest total first.
Show hint
Use executemany with placeholders, then a SELECT ... GROUP BY customer HAVING SUM(amount) > 100 ORDER BY total DESC, and unpack each row in a for loop.
Show solution
import sqlite3
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount INTEGER)")
conn.executemany(
"INSERT INTO orders (customer, amount) VALUES (?, ?)",
[("Asha", 250), ("Ravi", 560), ("Asha", 120), ("Meera", 90)],
)
rows = conn.execute("""
SELECT customer, COUNT(*) AS orders, SUM(amount) AS total
FROM orders
GROUP BY customer
HAVING SUM(amount) > 100
ORDER BY total DESC
""").fetchall()
for customer, n, total in rows:
print(f"{customer}: {n} orders, ₹{total}")Ravi: 1 orders, ₹560
Asha: 2 orders, ₹370The database does the grouping and filtering; Python just formats the result. (A polished version would print "1 order" in the singular.)
Key takeaway#
Finished this lesson?