The sqlite3 module provides a built-in interface to SQLite, a lightweight, file-based (or in-memory) SQL database, requiring no separate database server.

Syntax

import sqlite3
conn = sqlite3.connect("data.db")
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")

Deep Dive Course

sqlite3.connect(path) opens, creating if necessary, a SQLite database file, or an in-memory database if you pass a special in-memory identifier, useful for tests that shouldn't touch the filesystem. A cursor, obtained from the connection, is used to execute SQL statements and fetch results; execute() takes a SQL string and, separately, a tuple of parameters to safely substitute into placeholders, which is the correct way to include variable data in a query, avoiding SQL injection. Changes made by INSERT/UPDATE/DELETE statements aren't persisted to the database file until you call conn.commit().

1Understanding sqlite3 Module

💡

Always pass variable data as parameters to execute(), using placeholder syntax, never by formatting it directly into the SQL string — building SQL with string formatting or f-strings is exactly how SQL injection vulnerabilities happen.

editor.html

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE users (id INTEGER, name TEXT)")
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
conn.commit()

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

localhost:3000

2Practical Example

Here is a real-world application of sqlite3 Module showing how it is used in production Python code.

editor.html

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE users (id INTEGER, name TEXT)")

user_id = 1
name = "Bob"
cursor.execute("INSERT INTO users VALUES (?, ?)", (user_id, name))
conn.commit()
print(cursor.execute("SELECT name FROM users WHERE id = ?", (1,)).fetchone())

localhost:3000

3Best Practices

Follow these guidelines when working with sqlite3 Module:

1. Always use parameterized queries, execute(sql, params), for any variable data, never string formatting or concatenation into the SQL text

2. Call conn.commit() after INSERT/UPDATE/DELETE statements, or changes won't be saved to the database file

3. Use a with block, or explicit close(), on the connection so it's properly closed and any pending transaction is handled

⚠️

Tip: Always pass variable data as parameters to execute(), using placeholder syntax, never by formatting it directly into the SQL string — building SQL with string formatting or f-strings is exactly how SQL injection vulnerabilities happen.

editor.html

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE users (id INTEGER, name TEXT)")
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
conn.commit()

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

localhost:3000

Examples

Example 01Basic Usage

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE users (id INTEGER, name TEXT)")
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
conn.commit()

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Example 02Advanced Example

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE users (id INTEGER, name TEXT)")

user_id = 1
name = "Bob"
cursor.execute("INSERT INTO users VALUES (?, ?)", (user_id, name))
conn.commit()
print(cursor.execute("SELECT name FROM users WHERE id = ?", (1,)).fetchone())

Best Practices

Always use parameterized queries, execute(sql, params), for any variable data, never string formatting or concatenation into the SQL text
Call conn.commit() after INSERT/UPDATE/DELETE statements, or changes won't be saved to the database file
Use a with block, or explicit close(), on the connection so it's properly closed and any pending transaction is handled

Interview Question

Why should query parameters always be passed as a separate tuple to execute(), instead of formatted directly into the SQL string?▼

Hint: Think about what a malicious value could do if it were inserted directly into the query text.

When variable data is formatted directly into a SQL string, any special SQL characters in that data, like a quote character, become part of the actual query structure, letting a malicious or malformed input change the query's meaning entirely, which is the SQL injection vulnerability. Passing data as separate parameters to execute() instead sends the SQL text and the values through distinct channels to the database driver, which safely substitutes the values as literal data, never as executable SQL syntax, regardless of what characters they contain.

Exercises

MediumPractice using sqlite3 Module in a real scenario.

View Solution

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("CREATE TABLE users (id INTEGER, name TEXT)")
cursor.execute("INSERT INTO users VALUES (1, 'Alice')")
conn.commit()

cursor.execute("SELECT * FROM users")
print(cursor.fetchall())

Frequently Asked Questions

Why should query parameters always be passed as a separate tuple to execute(), instead of formatted directly into the SQL string?▼

Related Functions

dictionarieswith-statementcustom-exceptions