SQLite

Language: C

Data / Database

SQLite was created in 2000 by D. Richard Hipp. Its goal was to provide a lightweight, reliable, and portable SQL database engine that can be embedded in applications. It has become one of the most widely deployed databases in the world, powering applications from browsers to embedded devices.

SQLite is a self-contained, serverless, zero-configuration, transactional SQL database engine. It is embedded in Python via the built-in `sqlite3` module, making it easy to store and query relational data without requiring a separate database server.

Installation

pip: Built-in with Python (no installation required)

conda: Included in standard Python distributions

Usage

Python's `sqlite3` module allows you to create SQLite databases, execute SQL queries, and manage transactions. It supports standard SQL syntax, parameterized queries, and in-memory databases for temporary storage.

Creating a database and table

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
conn.commit()
conn.close()

Creates an SQLite database file `example.db` and a `users` table if it doesn’t exist.

Inserting data

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute("INSERT INTO users (name, age) VALUES (?,?)", ('Alice', 25))
conn.commit()
conn.close()

Inserts a row into the `users` table using a parameterized query to prevent SQL injection.

Querying data

import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)
conn.close()

Fetches all rows from the `users` table and prints them.

Updating data

cursor.execute('UPDATE users SET age = ? WHERE name = ?', (26, 'Alice'))
conn.commit()

Updates the age of the user named Alice.

Deleting data

cursor.execute('DELETE FROM users WHERE name = ?', ('Alice',))
conn.commit()

Deletes the user named Alice from the table.

Using in-memory database

conn = sqlite3.connect(':memory:')

Creates a temporary database in RAM, useful for testing or temporary data storage.

Using transactions and context manager

with sqlite3.connect('example.db') as conn:
    cursor = conn.cursor()
    cursor.execute('INSERT INTO users (name, age) VALUES (?,?)', ('Bob', 30))

Uses a context manager to automatically commit transactions and close the connection safely.

Error Handling

sqlite3.OperationalError: Check SQL syntax, table existence, or database lock issues.

sqlite3.IntegrityError: Occurs when violating primary key or unique constraints. Ensure data integrity.

sqlite3.DatabaseError: General database error. Check connection, permissions, and file paths.

Best Practices

Always use parameterized queries to prevent SQL injection.

Use context managers (`with` statement) for automatic connection handling.

Commit transactions to save changes, and rollback in case of errors.

Use indexes on columns that are frequently queried for better performance.

Avoid storing large binary data directly; use BLOBs carefully or external files.

Official Docs Github