In this comprehensive tutorial, you’ll learn how to use sqlite3 in Python to perform queries correctly, avoid common mistakes, and apply to the most frequent problems. Whether you’re a beginner or an experienced coder looking for a reliable reference, this guide will make your SQLite3 queries “fixed” for good.
import sqlite3 from datetime import datetime
In this tutorial, we’ll focus on – retrieving and manipulating data – and we’ll show you how to fix the frequent pitfalls that make beginners (and sometimes experts) pull their hair out.
def create_tables(): conn = sqlite3.connect('my_database.db') cursor = conn.cursor() # Create users table cursor.execute(''' CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE, age INTEGER, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ''') sqlite3 tutorial query python fixed
When retrieving records using a fixed SELECT structure, you have three options to fetch your results: fetchone() , fetchall() , and iterating over the cursor directly. Example: Filtering Data with Fixed Parameters
# Create table cursor.execute(''' CREATE TABLE IF NOT EXISTS employees ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, salary REAL ) ''')
Retrieve all rows from the users table:
@contextmanager def db_connection(db_path="app.db"): conn = sqlite3.connect(db_path) conn.row_factory = sqlite3.Row try: yield conn conn.commit() except: conn.rollback() raise finally: conn.close()
# Create products table cursor.execute(''' CREATE TABLE IF NOT EXISTS products ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, price REAL NOT NULL, stock INTEGER DEFAULT 0 ) ''')
I'll structure:
# Query users from last 7 days seven_days_ago = (datetime.now() - timedelta(days=7)).isoformat() cursor.execute( "SELECT * FROM users WHERE created_at > ?", (seven_days_ago,) )
All queries are parameterized, connections are safe, and indexes are used for performance. This is the “fixed” way to work with SQLite3 in Python.
Alex ran the script. The terminal remained silent for a heartbeat before printing: Success! The book is in the shelf. In this comprehensive tutorial, you’ll learn how to
| Problem | Most Likely Fix | |----------------------------------|------------------------------------------------------| | No data appears after INSERT | Did you call connection.commit() ? | | sqlite3.OperationalError near “?” | You used named placeholders incorrectly, or you forgot that ? is the only placeholder (unless you enable paramstyle='named' ). | | sqlite3.ProgrammingError – Incorrect number of bindings | Your parameter tuple has the wrong length. Check the number of ? in the query. | | Query runs but returns wrong rows | Check your WHERE clause logic – use AND / OR properly. Also watch out for NULLs: column = NULL is always false, use IS NULL . | | Slow performance on large table | Add an index on columns used in WHERE or JOIN . | | Database file is locked | You have another process (or another connection in write mode) using the same file. Close all connections or use a context manager. |