SQLite in Python: Complete Guide for Beginners

Working with SQLite in Python: Complete Guide for Beginners and Experienced Developers

Working with databases is one of the most important tasks in application development. If you need a lightweight, embedded solution without setting up separate servers, SQLite is the perfect choice for your needs.

With Python, thanks to the built-in sqlite3 module, you can quickly create, modify, and manage databases directly from your code. In this article, you'll learn how to connect to SQLite, perform data insert (INSERT), update (UPDATE) operations, and much more.

What Is SQLite and Why Choose It

SQLite is an embedded relational database that stores all information in a single file. It requires no separate server and is ideal for prototyping, small projects, and mobile applications.

SQLite is widely used across various areas of software development. Many popular applications, including browsers, messaging apps, and mobile apps, rely on SQLite for local data storage.

Advantages of SQLite

• No server installation required
• High performance
• Easy to use
• Excellent integration with Python via the sqlite3 module
• Cross-platform and portable
• ACID transaction support
• Minimal resource consumption

Limitations of SQLite

• Does not support concurrent writes from multiple users
• Limited performance with large datasets
• No built-in replication
• Limited capabilities for complex multi-user applications

Connecting to a SQLite Database in Python

Basic Connection

import sqlite3

# Connect to the database (if the file doesn't exist, it will be created)
conn = sqlite3.connect('my_database.db')

# Create a cursor to execute SQL queries
cursor = conn.cursor()

Connecting to an In-Memory Database

For temporary data storage or testing, you can use an in-memory database:

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

Configuring Connection Settings

conn = sqlite3.connect('my_database.db', timeout=10)
conn.execute('PRAGMA foreign_keys = ON')  # Enable foreign keys

Creating a Table in SQLite

Creating a Simple Table

cursor.execute('''
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER
    )
''')
conn.commit()

Creating a Table with Additional Constraints

cursor.execute('''
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        price REAL CHECK(price > 0),
        category TEXT DEFAULT 'uncategorized',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')
conn.commit()

Creating Indexes for Optimization

cursor.execute('''
    CREATE INDEX IF NOT EXISTS idx_users_name ON users(name)
''')
conn.commit()

Inserting Data into a Table — Python SQLite3 INSERT

Inserting a Single Record

One of the most common questions is how to insert data into a SQLite table:

cursor.execute('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', ('John', 30))
conn.commit()

Using question marks protects against SQL injection and is the recommended approach.

Bulk Data Insertion

users = [('Alex