SQLite: A Lightweight Database Engine for Modern Applications
When it comes to compact, ultra-reliable, and high-performance database management software, SQLite stands out as one of the most popular engines. Unlike server-based counterparts like MySQL or PostgreSQL, SQLite is a self-contained, serverless, zero-configuration database engine. Designed to be lightweight yet powerful, SQLite is widely used in embedded systems, mobile applications, and even as a developer’s go-to tool for simple, local databases. Its implementation is simple enough to embed directly into your application, making it an indispensable tool for developers.
In this blog post, we’ll explore what SQLite is, its top features, APIs, and how you can build a generic application to showcase its capabilities.
What is SQLite?
SQLite is an open-source, software library that implements a SQL database engine. SQLite is different from other relational databases because it does not have a dedicated server process. Instead, it reads and writes directly to ordinary disk files. Its key features include:
- Lightweight: No setup or administration is required.
- Self-contained: All database features are in a single library file.
- Cross-platform: Works seamlessly on Windows, Linux, iOS, Android, and more.
- Standard-compliant: Implements most of the SQL-92 standard.
- Atomic transactions: Ensures data integrity even in the event of hardware failures.
SQLite is widely adopted in various industries for use cases ranging from IoT devices to mobile apps like WhatsApp, where it powers local data storage.
Useful SQLite APIs with Explanations and Code Snippets
To get started with SQLite, you’ll often use its APIs for database creation, manipulation, and interaction. Below, we’ve compiled a comprehensive list of at least 20 SQLite APIs with code snippets in Python using the sqlite3 library.
1. sqlite3.connect()
Creates a connection to an SQLite database file. If the database file doesn’t exist, it creates one.
import sqlite3
# Create or connect to a database
connection = sqlite3.connect("example.db")
print("Database connected successfully!")
2. cursor()
Creates a cursor object to execute SQL queries.
cursor = connection.cursor()
print("Cursor is ready to execute SQL statements.")
3. execute()
Executes an SQL query. For example, creating a table:
cursor.execute('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER)''')
print("Table created successfully!")
4. executemany()
Executes a parameterized query multiple times.
users = [(1, 'Alice', 25), (2, 'Bob', 30), (3, 'Charlie', 22)]
cursor.executemany("INSERT INTO users (id, name, age) VALUES (?, ?, ?)", users)
print("Data inserted successfully!")
5. fetchone()
Fetches a single row of query results.
cursor.execute("SELECT * FROM users WHERE id=1")
row = cursor.fetchone()
print(row) # Output: (1, 'Alice', 25)
6. fetchall()
Fetches all rows of query results.
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
print(row)
7. rowcount
Returns the number of rows modified or fetched by the last SQL statement.
cursor.execute("DELETE FROM users WHERE age > 25")
print(f"{cursor.rowcount} rows deleted.")
8. lastrowid
Gets the ID of the last inserted row.
cursor.execute("INSERT INTO users (name, age) VALUES ('David', 29)")
print(f"Last inserted row ID: {cursor.lastrowid}")
9. commit()
Commits the current transaction.
connection.commit()
print("Transaction committed.")
10. rollback()
Rolls back the current transaction in case of an error.
try:
cursor.execute("UPDATE users SET age = 100 WHERE name = 'Alice'")
raise Exception("Simulated error.") # Simulating an error
except:
connection.rollback()
print("Transaction rolled back!")
11. close()
Closes the connection to the database.
connection.close()
print("Database connection closed.")
12. ATTACH
Attaches another database to the current connection.
cursor.execute("ATTACH DATABASE 'extra.db' AS extra")
print("Database extra.db attached successfully.")
13. PRAGMA
Used to query or modify database-level settings.
cursor.execute("PRAGMA journal_mode=WAL")
print("Journal mode set to WAL.")
14. VACUUM
Reduces the size of the database file.
cursor.execute("VACUUM")
print("Database optimized.")
15. ALTER TABLE
Used to modify a table structure.
cursor.execute("ALTER TABLE users ADD COLUMN email TEXT")
print("Column added successfully!")
16. REPLACE
Inserts a new row or replaces an old one if a conflict occurs.
cursor.execute("REPLACE INTO users(id, name, age) VALUES (1, 'Alicia', 28)")
print("Row replaced.")
17. DUMP
Outputs the database as a text file for backup purposes.
backup_file = "backup.sql"
with open(backup_file, "w") as f:
for line in connection.iterdump():
f.write(f"{line}\n")
print("Database exported to backup.sql.")
18. DETACH
Detaches a database previously attached.
cursor.execute("DETACH DATABASE extra")
print("Extra database detached.")
19. backup()
Copies data from one connection to another.
dest_conn = sqlite3.connect("destination.db")
connection.backup(dest_conn)
dest_conn.close()
print("Database backup completed.")
20. set_trace_callback()
Helps debug SQL queries by tracing their execution.
def trace(sql):
print(f"Executing SQL: {sql}")
connection.set_trace_callback(trace)
cursor.execute("SELECT * FROM users")
A Generic SQLite Application: To-Do App
Here’s a simple to-do application that demonstrates the use of SQLite:
import sqlite3
# Database setup
connection = sqlite3.connect("todo_app.db")
cursor = connection.cursor()
# Create table
cursor.execute('''CREATE TABLE IF NOT EXISTS tasks (
id INTEGER PRIMARY KEY AUTOINCREMENT,
task TEXT NOT NULL,
completed INTEGER NOT NULL DEFAULT 0)''')
# Add a task
def add_task(task):
cursor.execute("INSERT INTO tasks (task) VALUES (?)", (task,))
connection.commit()
print("Task added!")
# List all tasks
def list_tasks():
cursor.execute("SELECT id, task, completed FROM tasks")
rows = cursor.fetchall()
for row in rows:
status = "Done" if row[2] else "Pending"
print(f"{row[0]}. {row[1]} [{status}]")
# Mark a task as completed
def complete_task(task_id):
cursor.execute("UPDATE tasks SET completed = 1 WHERE id = ?", (task_id,))
connection.commit()
print("Task marked as completed.")
# Main menu
while True:
print("\nTo-Do App:")
print("1. Add Task")
print("2. List Tasks")
print("3. Complete Task")
print("4. Exit")
choice = int(input("Choose an option: "))
if choice == 1:
new_task = input("Enter your task: ")
add_task(new_task)
elif choice == 2:
list_tasks()
elif choice == 3:
task_id = int(input("Enter task ID to complete: "))
complete_task(task_id)
elif choice == 4:
break
else:
print("Invalid option. Try again!")
# Closing connection
connection.close()
Conclusion
In this blog post, we introduced SQLite, explained its key features, and explored 20+ useful APIs with code examples. SQLite is an incredibly versatile tool for developers at all levels and can be used to build anything from simple applications to complex systems. If you’re looking for a quick, efficient, and reliable database for your projects, SQLite is undoubtedly worth considering!
