sqlite3 Python Library : An Introduction

The sqlite3 Python library is a module that provides a lightweight and serverless relational database system integrated with Python. The library interfaces with SQLite version 3.7.15 and later. Here are some key points about the sqlite3 Python library:

  1. Embeddable: SQLite3 is an in-process database, which means it is linked into the application and runs in the same address space as the application.
  2. Serverless: Unlike many other database systems, SQLite3 doesn’t require a separate server to function. Its database consists of a single file on disk.
  3. Standard Library: The sqlite3 module is included in the standard Python library, so you don’t need to install it separately if you’re using Python 2.5 and above.
  4. DB-API 2.0 Compliant: The library provides an interface that’s compliant with Python’s DB-API 2.0 specification. This makes it relatively easy for developers to switch between different database systems in Python.
  5. Use Cases: It’s ideal for applications like embedded systems, testing, prototyping, or any scenario where a lightweight, serverless database is required.
  6. SQL Support: sqlite3 allows you to execute a wide range of SQL commands, enabling you to create, modify, query, and manage relational database tables.

Table of Contents

Installation

The sqlite3 module is part of the standard library for Python 2.5 and above. No separate installation is required. However, to ensure you have the latest version:

pip install pysqlite3

Basic Commands

Creating a Database

In Python, simply connecting to a non-existent database file will create it:

Python
import sqlite3
conn = sqlite3.connect('mydatabase.db')

Creating a Cursor:

Python
cursor = conn.cursor()

Creating a Table:

Python
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);
''')

Inserting Data:

Python
cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Mike', 28))
conn.commit()

Querying Data:

Python
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)
    
# output (1, 'Mike', 28)

Updating Data:

Python
cursor.execute("UPDATE users SET age = ? WHERE name = ?", (30, 'Mike'))
conn.commit()

Deleting Data:

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

Advanced Commands

Batch Inserts:

Python
users_data = [('Peter', 25), ('Sam', 32), ('David', 29)]
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", users_data)
conn.commit()

Transactions:

Python
with conn:
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ('Diana', 27))

Row Factories:

Retrieve rows as dictionary-like objects:

Python
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d

conn.row_factory = dict_factory
cursor = conn.cursor()

cursor.execute("SELECT * FROM users")
print(cursor.fetchone())  # Outputs: {'id': 1, 'name': 'Peter', 'age': 25}

FAQ

Q: What if I want the database in memory?
A: Use sqlite3.connect(":memory:") to create an in-memory database.

Q: How can I check if a table exists?
A: Use the following query:

SQL
SELECT name FROM sqlite_master WHERE type='table' AND name='YOUR_TABLE_NAME';

Q: Why am I seeing a Database is locked error?
A: This error occurs if multiple writes are attempted simultaneously. Ensure that you’re not keeping transactions open longer than necessary.

Q: How do I backup my SQLite database in Python?
A: Using the backup method:

Python
backup_con = sqlite3.connect('backup.db')
conn.backup(backup_con)
backup_con.close()

Tutorials

We use cookies to improve your experience on our website