SQLite3 with Python: Built-in SQLite Database Guide

Introduction to SQLite and the sqlite3 Module

SQLite is a compact relational database management system that requires no separate server setup, making it ideal for small to medium-sized projects. In Python, SQLite support comes built-in through the sqlite3 module, allowing you to work with databases without installing any extra libraries.

The sqlite3 module makes creating and using databases feel as simple as working with regular files. This solution is especially valuable for prototyping, embedded systems, desktop applications, and educational purposes.

Installation and Compatibility

The sqlite3 module has been included in Python's standard library since version 2.5 and is available without any additional installation. It offers full compatibility with Windows, macOS, and Linux, providing an interface for working with SQLite version 3.

import sqlite3

Connecting to a Database and Creating a Connection

Creating a File-Based Database

conn = sqlite3.connect('my_database.db')

When you run this command, the database is automatically created if the specified file doesn't exist.

Creating an In-Memory Database

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

This option is especially handy for testing and temporary operations.

Additional Connection Parameters

conn = sqlite3.connect('database.db', 
                      check_same_thread=False,
                      detect_types=sqlite3.PARSE_DECLTYPES)

Creating Tables and Working with Data Schemas

Creating Tables

cursor = conn.cursor()
cursor.execute('''
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')
conn.commit()

Data Types in SQLite

SQLite supports five main data types:

• INTEGER - whole numbers
• REAL - floating-point numbers
• TEXT - text strings
• BLOB - binary data
• NULL - no value

SQLite uses dynamic typing, which offers flexibility but requires careful attention when handling data.

CRUD Operations (Create, Read, Update, Delete)

Inserting Data

# Insert a single record
cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", 
               ("John Smith", 30, "john@example.com"))

# Insert multiple records
users_data = [
    ("Jane Doe", 25, "jane@example.com"),
    ("Peter Jones", 35, "peter@example.com"),
    ("Anna Brown", 28, "anna@example.com")
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users_data)
conn.commit()

Reading Data

# Select all records
cursor.execute("SELECT * FROM users")
all_users = cursor.fetchall()

# Select with a condition
cursor.execute("SELECT name, age FROM users WHERE age > ?", (25,))
adult_users = cursor.fetchall()

# Select with sorting
cursor.execute("SELECT * FROM users ORDER BY age DESC")
sorted_users = cursor.fetchall()

Updating Data

cursor.execute("UPDATE users SET age = ? WHERE name = ?", (31, "John Smith"))
conn.commit()

# Get the number of rows affected
print(f"Rows updated: {cursor.rowcount}")

Deleting Data

cursor.execute("DELETE FROM users WHERE name =