How to Use sqlite3 Module in Python: Simple Guide
Use the
sqlite3 module in Python by importing it, creating a connection to a database with sqlite3.connect(), and then creating a cursor to execute SQL commands. After running queries, commit changes with connection.commit() and close the connection with connection.close().Syntax
The basic steps to use the sqlite3 module are:
- Import the module:
import sqlite3 - Create a connection:
conn = sqlite3.connect('database_name.db')opens or creates a database file. - Create a cursor:
cursor = conn.cursor()lets you run SQL commands. - Execute SQL commands: Use
cursor.execute(sql_query)to run queries. - Commit changes: Use
conn.commit()to save changes. - Close connection: Use
conn.close()when done.
python
import sqlite3 # Connect to database (creates file if not exists) conn = sqlite3.connect('example.db') # Create a cursor object cursor = conn.cursor() # Execute SQL command cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)') # Commit changes conn.commit() # Close connection conn.close()
Example
This example shows how to create a table, insert data, and fetch it using sqlite3.
python
import sqlite3 # Connect to the database conn = sqlite3.connect(':memory:') # Use in-memory database for demo cursor = conn.cursor() # Create table cursor.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)') # Insert data cursor.execute('INSERT INTO users (name) VALUES (?)', ('Alice',)) cursor.execute('INSERT INTO users (name) VALUES (?)', ('Bob',)) # Commit changes conn.commit() # Query data cursor.execute('SELECT * FROM users') rows = cursor.fetchall() # Print results for row in rows: print(row) # Close connection conn.close()
Output
(1, 'Alice')
(2, 'Bob')
Common Pitfalls
Common mistakes when using sqlite3 include:
- Forgetting to commit changes after insert/update/delete, so data is not saved.
- Not closing the connection, which can lock the database file.
- Using string formatting to build SQL queries, which can cause SQL injection risks. Always use parameterized queries with
?placeholders. - Assuming the database file exists;
connect()creates it if missing.
python
import sqlite3 conn = sqlite3.connect('test.db') cursor = conn.cursor() # Wrong: vulnerable to SQL injection user_input = "Alice'); DROP TABLE users; --" cursor.execute(f"INSERT INTO users (name) VALUES ('{user_input}')") # Don't do this! # Right: use parameters to avoid injection cursor.execute('INSERT INTO users (name) VALUES (?)', (user_input,)) conn.commit() conn.close()
Quick Reference
Here is a quick summary of key sqlite3 methods:
| Method | Description |
|---|---|
| sqlite3.connect(database) | Open or create a database file and return a connection. |
| connection.cursor() | Create a cursor object to execute SQL commands. |
| cursor.execute(sql, params) | Run an SQL command with optional parameters. |
| connection.commit() | Save changes made to the database. |
| connection.close() | Close the database connection. |
| cursor.fetchall() | Fetch all rows from the last executed query. |
| cursor.fetchone() | Fetch the next row from the last executed query. |
Key Takeaways
Always create a connection and cursor before running SQL commands with sqlite3.
Use parameterized queries with ? placeholders to avoid SQL injection.
Call commit() to save changes after insert, update, or delete operations.
Close the connection when done to free resources and avoid locking the database.
sqlite3.connect() creates the database file if it does not exist.