43.SQLite & SQL Basics with sqlite3
SQLite is a lightweight, disk-based database that doesn’t require a separate server process. It is widely used for embedded database systems and is part of the Python standard library via the sqlite3 module.
What is SQLite?
SQLite is a self-contained, serverless SQL database engine. It stores the entire database as a single file on disk and supports most of the SQL standard. It is ideal for small to medium-sized applications, prototyping, and testing.
Connecting to SQLite
To connect to an SQLite database, use the sqlite3.connect() method. If the file does not exist, it will be created automatically.
Example:
import sqlite3
conn = sqlite3.connect(‘example.db’)
cursor = conn.cursor()
Creating Tables
You can create tables using standard SQL syntax with the cursor.execute() method.
Example:
cursor.execute(”’CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)”’)
CRUD Operations
You can create tables using standard SQL syntax with the cursor.execute() method.
Example:
cursor.execute(”’CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT,
age INTEGER
)”’)
Read (Query Data)
cursor.execute(“SELECT * FROM users”)
rows = cursor.fetchall()
for row in rows:
print(row)
Update Data
cursor.execute(“UPDATE users SET age = ? WHERE name = ?”, (31, “Alice”))
conn.commit()
Delete Data
cursor.execute(“DELETE FROM users WHERE name = ?”, (“Alice”,))
conn.commit()
Use Cases
- Mobile and embedded applications
- Prototyping and testing
- Applications with moderate database needs
- Local storage for desktop apps
Best Practices
- Always close the connection using conn.close()
- Use parameterized queries to prevent SQL injection
- Handle exceptions using try-except blocks
- Use transactions for multiple related operations
Common Pitfalls
- Forgetting to commit changes
- Not closing the connection
- Using string formatting instead of parameterized queries
- Assuming SQLite is suitable for high-concurrency applications