A Comprehensive Guide to Using SQLite3 in Python
This blog post is written for developers new to database programming with Python. We’ll walk you through using SQLite3, a lightweight database engine built into Python, with clear examples and explanations.
📦 What is SQLite3?
SQLite3 is a serverless, disk-based database that requires no setup, making it ideal for small to medium-sized applications or learning database concepts.
🔍 Why Use SQLite3 in Python?
It’s simple, fast, and included in Python’s standard library. You can use it for prototyping, desktop apps, or small projects without needing a full database server.
🛠️ Getting Started with SQLite3
Let’s dive into the core concepts with code examples and step-by-step breakdowns.
1. Connecting to a SQLite3 Database
To start, you connect to a database using the sqlite3.connect() function.
import sqlite3
# Connect to the database (or create it if it doesn't exist)
conn = sqlite3.connect('example.db')
Explanation:
import sqlite3: Imports the SQLite3 module.sqlite3.connect('example.db'): Connects to (or creates) a database file namedexample.db.
2. Creating a Cursor Object
A cursor object lets you execute SQL commands.
# Create a cursor object
cursor = conn.cursor()
Explanation:
conn.cursor(): Creates a cursor to send SQL queries and fetch results.
3. Creating a Table
Tables store data in a structured format. Use a CREATE TABLE statement.
# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
''')
Explanation:
CREATE TABLE IF NOT EXISTS: Only creates the table if it doesn’t exist.id INTEGER PRIMARY KEY: Auto-incrementing unique ID.name TEXT NOT NULL: Name field, cannot be empty.email TEXT NOT NULL UNIQUE: Email field, must be unique and non-empty.
4. Inserting Data
Add data using INSERT INTO statements.
# Insert a single row
cursor.execute("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')")
# Insert multiple rows
users = [('Bob', 'bob@example.com'), ('Charlie', 'charlie@example.com')]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users)
Explanation:
execute(): Inserts one row.executemany(): Inserts multiple rows efficiently using placeholders (?).
5. Querying Data
Retrieve data using SELECT statements.
# Select all rows from the users table
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
Example Output:
(1, 'Alice', 'alice@example.com')
(2, 'Bob', 'bob@example.com')
(3, 'Charlie', 'charlie@example.com')
Explanation:
SELECT * FROM users: Retrieves all rows.fetchall(): Returns all results as a list of tuples.
# Fetch one row
cursor.execute("SELECT * FROM users")
row = cursor.fetchone()
print(row)
Explanation:
fetchone(): Retrieves one row at a time.
6. Updating Data
Modify data with an UPDATE statement.
# Update a row
cursor.execute("UPDATE users SET email = 'new_email@example.com' WHERE name = 'Alice'")
Explanation:
UPDATE: Changes the email for the user named 'Alice'.WHERE: Ensures only the specified row is updated.
7. Deleting Data
Remove data with a DELETE statement.
# Delete a row
cursor.execute("DELETE FROM users WHERE name = 'Bob'")
Explanation:
DELETE: Removes the row where the name is 'Bob'.
8. Committing Changes
Save changes to the database with commit().
# Commit the changes
conn.commit()
Explanation:
commit(): Makes changes permanent.- Without this, changes are lost when the connection closes.
9. Handling Errors
Use try-except to handle database errors.
try:
# Attempt to insert a duplicate email
cursor.execute("INSERT INTO users (name, email) VALUES ('David', 'alice@example.com')")
except sqlite3.IntegrityError as e:
print(f"Error: {e}")
Example Output:
Error: UNIQUE constraint failed: users.email
Explanation:
try-except: Catches errors like duplicate emails.sqlite3.IntegrityError: Handles constraint violations.
10. Closing the Connection
Free resources by closing the connection.
# Close the connection
conn.close()
Explanation:
close(): Releases the database connection.
11. The Full Script
Here’s the complete SQLite3 example.
import sqlite3
# Connect to the database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
)
''')
# Insert data
cursor.execute("INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')")
users = [('Bob', 'bob@example.com'), ('Charlie', 'charlie@example.com')]
cursor.executemany("INSERT INTO users (name, email) VALUES (?, ?)", users)
# Commit the changes
conn.commit()
# Query data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
# Update data
cursor.execute("UPDATE users SET email = 'new_email@example.com' WHERE name = 'Alice'")
# Delete data
cursor.execute("DELETE FROM users WHERE name = 'Bob'")
# Commit again
conn.commit()
# Close the connection
conn.close()
Explanation:
This script combines all the steps: connecting, creating a table, inserting data, querying, updating, deleting, committing, and closing.
🧰 Summary
| Term | Meaning |
|---|---|
| sqlite3.connect() | Connects to or creates a database file |
| cursor() | Creates an object to execute SQL commands |
| execute() | Runs a single SQL query |
| executemany() | Runs a query for multiple rows efficiently |
| fetchall() | Retrieves all query results |
| fetchone() | Retrieves one query result |
| commit() | Saves changes to the database |
| close() | Closes the database connection |
💻 Conclusion
SQLite3 is a powerful, lightweight database engine that’s perfect for Python developers. With the examples above, you can start building applications that store and manage data efficiently. Experiment with these snippets, tweak them, and explore SQLite3 further. Happy coding!
Comments
Post a Comment