Introduction to SQLite in Python

SQLite is a lightweight, fast and embedded SQL database engine. SQLite is perfect for small to medium-sized applications, prototyping, embedded systems and local data storage in Python applications because it doesn't require a separate server process like other relational database management systems (RDBMS) like MySQL or PostgreSQL.

Features of SQLite:

1. Serverless
2. Self-Contained
3. Zero-Configuration
4. Transactional
5. Single-Database

We don't need to install anything additional to get started because Python has built-in support for SQLite through the sqlite3 module. Let's understance each of the features in detail.

Serverless

Generally, an RDBMS such as MySQL, PostgreSQL, etc., needs a separate server process to operate. The applications that want to access the database server use TCP/IP protocol to send and receive requests and it is called client/server architecture. The diagram below illustrates the working of relational databases:

SQLite does not require a server to run. SQLite database read and write directly from the database files stored on disk and applications interact with that SQLite database. It is one of SQLite's biggest advantages is that it is serverless. Here's what that means:

• There is no separate server process to manage. The SQLite engine is embedded directly into the application.
• All you need is the SQLite database file, your program can read from and write to it without connecting to a remote service.
• This reduces overhead, setup complexity and dependencies, making SQLite perfect for desktop apps, mobile apps, IoT devices or lightweight data-driven Python scripts.

Self-Contained

SQLite is self-contained, here's what it means:

• It has no external dependencies. Everything it needs is included in a single library.
• The entire database (schema + data) is stored in a single .sqlite or .db file.
• This file can be copied, backed up, shared or moved like any other document.

Key benefits of being Self-Contained:

• Portability: Move the database file across machines or platforms without worrying about data loss or corruption.
• Integration: Easily bundle SQLite databases within applications, especially Python packages or tools.

Zero-Configuration

Unlike other databases, SQLite requires zero setup:

• No configuration files or startup services are needed.
• You can start using SQLite as soon as you import the sqlite3 module in Python.
• We can simply connect to a database file and if it doesn’t exist, SQLite automatically creates it.

Example: Connecting to a SQLite Database in Python

import sqlite3

conn = sqlite3.connect('example.db')  # Creates a new database file if it doesn’t exist
cursor = conn.cursor()

Transactional

• SQLite supports full ACID (Atomicity, Consistency, Isolation, Durability) transactions:
• Every operation in SQLite is atomic, i.e changes are either fully applied or not applied at all.
• By default, SQLite wraps commands like INSERT, UPDATE and DELETE inside implicit transactions, ensuring data integrity.

We can also manage transactions explicitly using:

• BEGIN
• COMMIT
• ROLLBACKs.

Transaction Control Example:

conn.execute("BEGIN")
# perform database operations
conn.commit()  # or conn.rollback() if something fails

Single-Database

SQLite uses a single-file database architecture, meaning:

• The entire database, i.e. tables, indexes, triggers and data, lives in a single file only.
• This simplifies database management, as there's no need to manage multiple configuration or log files.
• The file can be used across different platforms, tools and programming languages.

Benefits of Single-File Storage:

• Easy to deploy and backup.
• Makes testing and debugging easier.
• Supports concurrent read operations (though writes are serialized).

Working of SQLite in Python

Python includes built-in support for SQLite via the sqlite3 module, which conforms to the Python Database API Specification v2.0 (PEP 249).

Working:

• We write Python code.
• The sqlite3 module handles connections, queries, transactions, etc.
• It interacts directly with the .db file (no server needed).

Advanced Features in SQLite with Python

While SQLite is simple to use, it offers several advanced features:

Parameterized Queries

To prevent SQL injection:

cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

Row Factory

Fetch rows as dictionaries:

conn.row_factory = sqlite3.Row

In-Memory Databases

For temporary, fast operations:

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

Using with Statement for Safe Resource Handling:

with sqlite3.connect('example.db') as conn:
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")

Explanation:

import sqlite3: imports Python’s built-in SQLite module, which allows interaction with SQLite databases.

sqlite3.connect('example.db'): establishes a connection to a SQLite database file named example.db.

• If the file does not exist, SQLite will automatically create it.
• This method returns a connection object that lets you interact with the database.

conn.cursor(): creates a cursor object from the connection, which is used to execute SQL queries and fetch results.

To learn more about different types of relation databses, refer to: MySQL, PostgreSQL.