Python sqlite3.Cursor.fetchone Method
Last modified April 15, 2025
This comprehensive guide explores Python's sqlite3.Cursor.fetchone method,
the primary way to retrieve single rows from SQLite query results. We'll cover basic
usage, parameters, and practical examples.
Basic Definitions
The fetchone method retrieves the next row from a query result set. It
returns a single sequence representing one row, or None when no more data is available.
Key characteristics: it's memory efficient for large result sets, maintains cursor position, and works with any SELECT query. It's part of the DB-API 2.0 specification.
Basic fetchone Usage
Here's the simplest usage of fetchone to retrieve a single row from
a database table.
import sqlite3
with sqlite3.connect('example.db') as conn:
with conn.cursor() as cur:
cur.execute("SELECT * FROM users WHERE id = 1")
row = cur.fetchone()
print(row) # (1, 'Alice', 30)
This example shows the basic workflow: connect, create cursor, execute query, fetch one row, and automatically close resources. The with statements ensure proper cleanup.
The method returns a tuple representing the row's columns in query order. None is returned if no rows match the query.
Fetching Multiple Rows with fetchone
fetchone can be called repeatedly to process a result set row by row.
This is memory efficient for large result sets.
import sqlite3
with sqlite3.connect('example.db') as conn:
with conn.cursor() as cur:
cur.execute("SELECT * FROM users ORDER BY name")
while True:
row = cur.fetchone()
if row is None:
break
print(f"User: {row[1]}, Age: {row[2]}")
This pattern processes each row as it's fetched, without loading all results into
memory. The loop continues until fetchone returns None.
This approach is ideal for large datasets where memory conservation is important.
Combining fetchone with Row Factory
Using a row factory with fetchone enables named column access for
better code readability.
import sqlite3
with sqlite3.connect('example.db') as conn:
conn.row_factory = sqlite3.Row
with conn.cursor() as cur:
cur.execute("SELECT * FROM users WHERE age > ?", (25,))
row = cur.fetchone()
if row:
print(f"{row['name']} is {row['age']} years old")
The sqlite3.Row factory provides both index and name-based access to
columns. This makes code more maintainable and less prone to errors.
Named access is especially valuable when working with tables that have many columns or when schema changes might occur.
Handling Empty Result Sets
fetchone returns None when no rows match the query. This example
shows proper handling of empty results.
import sqlite3
def get_user_age(user_id):
with sqlite3.connect('example.db') as conn:
with conn.cursor() as cur:
cur.execute("SELECT age FROM users WHERE id = ?", (user_id,))
row = cur.fetchone()
return row[0] if row else None
age = get_user_age(999)
print(f"User age: {age if age is not None else 'not found'}")
This example safely handles cases where the query returns no rows. The ternary operator checks if row exists before accessing its first column.
Always check for None when using fetchone to avoid AttributeError
exceptions.
Fetchone with Parameterized Queries
Parameterized queries with fetchone prevent SQL injection and
improve performance through query reuse.
import sqlite3
def authenticate(username, password):
with sqlite3.connect('users.db') as conn:
with conn.cursor() as cur:
query = "SELECT id FROM users WHERE username = ? AND password = ?"
cur.execute(query, (username, password))
return cur.fetchone() is not None
authenticated = authenticate('admin', 'secret123')
print("Login successful" if authenticated else "Invalid credentials")
This secure authentication example uses parameterized queries. The ? placeholders are replaced safely with user input values.
Parameterized queries are essential for security and should always be used with user-provided data.
Fetchone in Transactions
fetchone works within transactions, allowing row-by-row processing
while maintaining data consistency.
import sqlite3
with sqlite3.connect('inventory.db') as conn:
conn.execute("BEGIN")
try:
with conn.cursor() as cur:
cur.execute("SELECT id, quantity FROM products WHERE quantity < 5")
while True:
row = cur.fetchone()
if row is None:
break
print(f"Low stock: Product {row[0]} has {row[1]} units")
# Could update each product here
conn.commit()
except:
conn.rollback()
raise
This example processes low-stock products within a transaction. The explicit BEGIN ensures all fetches see a consistent database state.
Transactions are important when fetching data that might be changed by other database operations during processing.
Fetchone with Custom Types
SQLite can convert fetched data to Python types using converters or detect_types.
import sqlite3
import json
from datetime import datetime
def parse_date(text):
return datetime.strptime(text, '%Y-%m-%d').date()
with sqlite3.connect('data.db', detect_types=sqlite3.PARSE_DECLTYPES) as conn:
conn.execute("CREATE TABLE IF NOT EXISTS events (id INTEGER, details TEXT, event_date DATE)")
conn.execute("INSERT INTO events VALUES (1, '{\"type\":\"meeting\"}', '2025-04-20')")
conn.commit()
with conn.cursor() as cur:
cur.execute("SELECT * FROM events")
row = cur.fetchone()
print(f"Event on {row[2]}: {json.loads(row[1])['type']}")
This example shows type conversion during fetch. The DATE column is converted to a Python date object, and JSON text is parsed.
Type detection and conversion can simplify data handling but may impact performance for very large result sets.
Best Practices
- Always use with statements: Ensures proper resource cleanup
- Check for None: Handle empty result sets gracefully
- Use parameterized queries: Prevent SQL injection
- Consider memory usage: fetchone is better than fetchall for large data
- Use row factories: For more readable column access
Source References
Author
List all Python tutorials.