SQLite is a lightweight, serverless database engine that is widely used for embedded database applications. Python provides built-in support for SQLite through the sqlite3 module, making it easy to interact with SQLite databases for small to medium-sized applications.
1. Why Use SQLite with Python?
Lightweight & Serverless – No separate server setup is needed.
Fast & Efficient – Suitable for small applications and local storage.
Cross-Platform – Works on Windows, Linux, and macOS.
Built into Python – No additional installation is required.
Useful for Prototyping – Quickly create and test databases before moving to larger systems like MySQL or PostgreSQL.
2. Installing SQLite
SQLite comes pre-installed with Python (version 3.x), but you can check its availability using:
python -c "import sqlite3; print(sqlite3.sqlite_version)"
If you need to install it manually:
pip install pysqlite3
3. Connecting to an SQLite Database
To start working with SQLite in Python, you need to establish a connection.
Example: Creating/Connecting to a Database
import sqlite3
# Connect to (or create) a database file
conn = sqlite3.connect("mydatabase.db")
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
print("Connected to SQLite database successfully!")
If the database does not exist, SQLite will create it automatically.
4. Creating a Table
Tables are used to store structured data.
Example: Creating a “Users” Table
cursor.execute("""
CREATE TABLE IF NOT EXISTS Users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
age INTEGER,
email TEXT UNIQUE
)
""")
conn.commit() # Save changes
print("Users table created successfully!")
AUTOINCREMENT ensures that id increases automatically.
TEXT, INTEGER define column data types.
NOT NULL ensures the field cannot be empty.
UNIQUE prevents duplicate email entries.
5. Inserting Data into SQLite
Once the table is created, you can insert records.
Example: Adding a User
cursor.execute("""
INSERT INTO Users (name, age, email) VALUES (?, ?, ?)
""", ("John Doe", 25, "john@example.com"))
conn.commit() # Save the changes
print("User added successfully!")
? Placeholders – Prevent SQL injection attacks.
commit() – Saves the changes to the database.
Inserting Multiple Records at Once
users = [
("Alice", 30, "alice@example.com"),
("Bob", 22, "bob@example.com"),
("Charlie", 28, "charlie@example.com")
]
cursor.executemany("INSERT INTO Users (name, age, email) VALUES (?, ?, ?)", users)
conn.commit()
print("Multiple users added successfully!")
executemany() inserts multiple records in one go.
6. Retrieving Data from SQLite
Fetching data from the database allows us to use and display it.
Example: Fetching All Users
cursor.execute("SELECT * FROM Users")
users = cursor.fetchall() # Fetch all results
for user in users:
print(user) # Each row is a tuple
fetchall() retrieves all records as a list of tuples.
Fetching a Single Record
cursor.execute("SELECT * FROM Users WHERE name = ?", ("Alice",))
user = cursor.fetchone() # Fetch first match
print(user)
fetchone() returns a single result.
Fetching Only Specific Columns
cursor.execute("SELECT name, age FROM Users")
names_and_ages = cursor.fetchall()
print(names_and_ages)
You can select only required columns to optimize performance.
7. Updating Data in SQLite
To modify existing records:
Example: Updating a User’s Age
cursor.execute("UPDATE Users SET age = ? WHERE name = ?", (35, "Alice"))
conn.commit()
print("User updated successfully!")
commit() ensures the update is saved.
8. Deleting Data from SQLite
To remove records:
Example: Deleting a User by Name
cursor.execute("DELETE FROM Users WHERE name = ?", ("Bob",))
conn.commit()
print("User deleted successfully!")
Deleting is permanent, so be cautious.
9. Using SQLite Transactions
SQLite supports transactions to ensure data consistency.
Example: Using Transactions
try:
conn.execute("BEGIN TRANSACTION")
cursor.execute("UPDATE Users SET age = 40 WHERE name = 'Charlie'")
cursor.execute("DELETE FROM Users WHERE name = 'Alice'")
conn.commit() # Commit if everything succeeds
print("Transaction completed successfully!")
except:
conn.rollback() # Rollback if any error occurs
print("Transaction failed! Rolling back changes.")
BEGIN TRANSACTION starts a safe transaction.
rollback() undoes changes if an error occurs.
10. Exporting and Importing SQLite Data
You can save and retrieve database records easily.
Exporting Data to CSV
import csv
cursor.execute("SELECT * FROM Users")
with open("users.csv", "w", newline="") as file:
writer = csv.writer(file)
writer.writerow([desc[0] for desc in cursor.description]) # Column names
writer.writerows(cursor.fetchall())
print("Data exported to users.csv!")
Importing Data from CSV to SQLite
with open("users.csv", "r") as file:
reader = csv.reader(file)
next(reader) # Skip header
cursor.executemany("INSERT INTO Users (id, name, age, email) VALUES (?, ?, ?, ?)", reader)
conn.commit()
print("Data imported successfully!")
Easily migrate SQLite data to/from CSV files.
11. Closing the Connection
After all operations, always close the connection.
conn.close()
print("Database connection closed.")
Closes the database properly to avoid memory leaks.
12. Advanced SQLite Features
| Feature | Description |
|---|---|
| Indexes | Improve query speed using CREATE INDEX |
| Joins | Combine data from multiple tables |
| Foreign Keys | Maintain relationships between tables |
| Full-Text Search (FTS5) | Advanced search capabilities |
| Triggers | Automatically execute actions on data changes |