Introduction to SQLite
SQLite is a lightweight, file-based database engine that doesn’t require a separate server process. It’s embedded into your application, making it ideal for small to medium-sized applications where data persistence is required. Python’s built-in sqlite3
module provides a convenient interface to interact with SQLite databases.
Getting Started
Importing the sqlite3
module:
import sqlite3
Creating a Database Connection:
conn = sqlite3.connect('mydatabase.db')
This line creates a database named mydatabase.db
in the current directory. If the database already exists, it will open it instead. To create an in-memory database, use ':memory:'
as the database name.
Creating a Cursor:
cursor = conn.cursor()
A cursor is used to execute SQL statements. It’s like a pointer to the database.
Creating Tables
cursor.execute('''CREATE TABLE customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
address TEXT,
city TEXT,
postalcode TEXT,
country TEXT
)''')
This code creates a table named customers
with several columns. Note the use of triple quotes for multi-line strings and the AUTOINCREMENT
keyword for automatically generating primary key values.
Inserting Data
cursor.execute("INSERT INTO customers (name, address, city, postalcode, country) VALUES ('John Doe', '301 Main St', 'New York', '10001', 'USA')")
This code inserts a new record into the customers
table.
Retrieving Data
cursor.execute("SELECT * FROM customers")
rows = cursor.fetchall()
for row in rows:
print(row)
This code selects all records from the customers
table and prints them to the console.
Updating Data
cursor.execute("UPDATE customers SET address = '405 Main St' WHERE id = 1")
This code updates the address for the customer with ID 1.
Deleting Data
cursor.execute("DELETE FROM customers WHERE id = 2")
This code deletes the customer with ID 2 from the database.
Committing Changes
conn.commit()
This line commits the changes made to the database.
Closing the Connection
conn.close()
This line closes the database connection.
Error Handling
import sqlite3
try:
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
# ... your code ...
except sqlite3.Error as e:
print("Error:", e)
finally:
if conn:
conn.close()
Advanced Topics
- Parameterized Queries: To prevent SQL injection, use parameterized queries:Python
cursor.execute("INSERT INTO customers VALUES (?, ?, ?, ?, ?)", (name, address, city, postalcode, country))
- Transactions: Group multiple SQL statements into a transaction using
begin_transaction()
andcommit()
orrollback()
. - Creating Indexes: Improve query performance by creating indexes on frequently searched columns:Python
cursor.execute("CREATE INDEX idx_name ON customers(name)")
- Using SQLite Functions: Create custom SQL functions in Python:Python
def add(x, y): return x + y conn.create_function("add", 2, add) cursor.execute("SELECT add(1, 2)")
- SQLite Browser: Use a graphical tool like SQLite Browser to explore your database.
Best Practices
- Use clear and meaningful table and column names.
- Normalize your database design to avoid data redundancy.
- Index columns that are frequently searched.
- Use parameterized queries to prevent SQL injection.
- Commit changes regularly to avoid data loss.
- Close the database connection when finished.
Conclusion
SQLite is a versatile and easy-to-use database for Python applications. By understanding the basic concepts and best practices, you can effectively store, retrieve, and manage data within your projects.