How to work with SQLite in python practical tips

Working with Databases in Python

Working with databases is an essential part of software development. SQLite is one of the easiest and most powerful ways to store data in a local project. This lightweight database is built into Python and does not require a separate server to operate.

SQLite Basics

Definition and Principles of Operation

SQLite is an embedded relational database. It does not require installing or running a separate server. All data is stored in a single file. This makes SQLite an ideal choice for prototyping, desktop, and mobile applications.

Main Advantages

SQLite has several important advantages:

• Built into Python via the sqlite3 module
• Requires no additional configuration
• Provides high performance for small to medium-sized projects
• Easy to use
• Supports standard SQL queries
• Small size and minimal system requirements

Areas of Application for SQLite in Python

Main Use Cases

SQLite is actively used in the following scenarios:

• Educational and practical training projects
• Local applications and scripts
• Small web applications, for example, with Flask
• Configuration storage systems
• Offline programs with local data storage
• Application prototyping
• Data caching

Benefits for Developers

Python developers gain many benefits from using SQLite. The database does not require additional dependencies. It allows you to quickly start working with relational data. SQLite supports transactions and ensures data reliability.

Getting Started with SQLite

Import and Connection

SQLite is built into the standard Python library. To work with it, simply import the appropriate module:

import sqlite3

Creating a Database Connection

Connecting to a Database File

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

If the database file does not exist, it will be created automatically.

Working with an In-Memory Database

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

Such a database exists only in RAM and is deleted when the program finishes.

Creating the Database Structure

Creating Tables

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

Understanding Data Types

SQLite supports the following main data types:

• INTEGER - whole numbers
• TEXT - text strings
• REAL - floating-point numbers
• BLOB - binary data
• NULL - empty values

Data Operations

Inserting Data

Adding a Single Record

cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", 
               ("Alice", 25, "alice@example.com"))
conn.commit()

Inserting Multiple Records

users = [
    ("Bob", 30, "bob@example.com"),
    ("Charlie", 22, "charlie@example.com"),
    ("Diana", 28, "diana@example.com")
]
cursor.executemany("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", users)
conn.commit()

Reading Data from the Database

Getting All Records

cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)

Using Dictionaries for Convenience

conn.row_factory = sqlite3.Row
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(dict(row))

Filtering Data

cursor.execute("SELECT * FROM users WHERE age > ?", (25,))
adult_users = cursor.fetchall()

Updating Existing Data

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

Deleting Data

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

Security and Best Practices

Protecting Against SQL Injection

Always use placeholders (?) instead of string formatting. This protects against SQL injection:

# Correct
cursor.execute("SELECT * FROM users WHERE name = ?", (username,))

# Incorrect
cursor.execute(f"SELECT * FROM users WHERE name = '{username}'")

Connection Management

Properly Closing the Connection

conn.close()

Using a Context Manager

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

Error Handling

Use try/except blocks to catch exceptions:

try:
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("Test", 25))
    conn.commit()
except sqlite3.IntegrityError:
    print("Error: data integrity violation")
except sqlite3.Error as e:
    print(f"Database error: {e}")

Working with Transactions

try:
    conn.execute("BEGIN")
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("User1", 30))
    cursor.execute("INSERT INTO users (name, age) VALUES (?, ?)", ("User2", 25))
    conn.commit()
except sqlite3.Error:
    conn.rollback()
    print("Transaction rolled back")

Practical Example

Complete Program for Managing Users

import sqlite3

def init_db():
    """Initializes the database"""
    with sqlite3.connect('example.db') as conn:
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                age INTEGER,
                email TEXT UNIQUE
            )
        ''')
        conn.commit()

def add_user(name, age, email):
    """Adds a new user"""
    with sqlite3.connect('example.db') as conn:
        cursor = conn.cursor()
        try:
            cursor.execute("INSERT INTO users (name, age, email) VALUES (?, ?, ?)", 
                         (name, age, email))
            conn.commit()
            print(f"User {name} added successfully")
        except sqlite3.IntegrityError:
            print("Error: user with this email already exists")

def get_users():
    """Gets all users"""
    with sqlite3.connect('example.db') as conn:
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users")
        return cursor.fetchall()

def update_user_age(user_id, new_age):
    """Updates the user's age"""
    with sqlite3.connect('example.db') as conn:
        cursor = conn.cursor()
        cursor.execute("UPDATE users SET age = ? WHERE id = ?", (new_age, user_id))
        conn.commit()

def delete_user(user_id):
    """Deletes a user"""
    with sqlite3.connect('example.db') as conn:
        cursor = conn.cursor()
        cursor.execute("DELETE FROM users WHERE id = ?", (user_id,))
        conn.commit()

# Using the program
init_db()
add_user("Екатерина", 29, "ekaterina@example.com")
add_user("Иван", 34, "ivan@example.com")

for user in get_users():
    print(dict(user))

SQLite Limitations

When Not to Use SQLite

SQLite has certain limitations:

• With a large number of concurrent writes
• For scalable web applications with high load
• When data encryption is required at the database level
• When replication and scaling are required
• For applications with multiple simultaneous connections

Alternatives for Large Projects

For large projects, it is recommended to use:

• PostgreSQL - a reliable database with advanced features
• MySQL - fast and popular in web development
• MongoDB - a document-oriented NoSQL database
• TinyDB - for microprojects without SQL

Optimizing SQLite Performance

Creating Indexes

cursor.execute("CREATE INDEX IF NOT EXISTS idx_user_email ON users(email)")

Using VACUUM for Optimization

cursor.execute("VACUUM")

Performance Tuning

cursor.execute("PRAGMA journal_mode = WAL")
cursor.execute("PRAGMA synchronous = NORMAL")
cursor.execute("PRAGMA cache_size = 1000")

SQLite in Python provides a powerful tool for working with data in local projects. Correct use of this technology allows you to create effective and reliable applications for storing and processing information.