If you use the raw conn.commit() and conn.close() , ensure they are in a finally block or use try/finally .
: Returns all remaining rows in the result set as a list of tuples. 3. Safety: Avoiding SQL Injection Never use Python string formatting (like ) to insert variables into your queries. Always use the parameterized query style with placeholders. DigitalOcean ❌ Wrong (Dangerous):
For even better performance, use PRAGMA synchronous = OFF and PRAGMA journal_mode = MEMORY during bulk inserts (then reset them). But be aware of the trade‑off with durability. sqlite3 tutorial query python fixed
cursor.execute(''' CREATE TABLE IF NOT EXISTS posts ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER, title TEXT NOT NULL, content TEXT, FOREIGN KEY (user_id) REFERENCES users (id) ) ''')
Use .executemany() to batch-process a list of tuples in a single, optimized operation. If you use the raw conn
def transactional_operation(): try: with sqlite3.connect('my_database.db') as conn: cursor = conn.cursor()
Increase the busy timeout threshold when opening the connection so threads wait for locks to clear before crashing. Safety: Avoiding SQL Injection Never use Python string
Assume a departments table:
import sqlite3 def get_data_from_table(table_name): # FIXED: Hardcoded whitelist verification allowed_tables = ["users", "orders", "products"] if table_name not in allowed_tables: raise ValueError("Invalid table name restriction violated.") connection = sqlite3.connect("app.db") cursor = connection.cursor() # Safe because the input string is strictly verified first cursor.execute(f"SELECT * FROM table_name") return cursor.fetchall() Use code with caution. 6. Summary Checklist for Fixed Python SQLite3 Queries Using non-SQLite syntax features Convert to LEFT JOIN or standard SQLite types SQL Injection / Crashes Python string formatting ( f"var" ) Use ? placeholders and pass data as a tuple Data Not Saving Missing database commit Use with sqlite3.connect() context managers Database Is Locked Unclosed connections / concurrent writes Add timeout=10.0 to connect; close connections Dynamic Table Errors Putting ? placeholders on tables Whitelist table strings and use secure Python formatting
Let’s combine everything into a small script that demonstrates a complete query workflow – including the fixes we discussed.
