How does SQLite work in a python?

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

Working with databases is one of the most important tasks in application development. If you need a lightweight, built-in solution without having to install separate servers, SQLite is perfect for your needs.

In conjunction with Python, thanks to the standard sqlite3 module, you can very quickly create, modify, and manage databases directly from code. In this article, you will learn how to connect to SQLite, perform insert, update, and more operations.

What is SQLite and why choose it?

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

SQLite is widely used in various areas of software development. Many popular applications, including browsers, messengers, and mobile apps, use SQLite for local data storage.

Advantages of SQLite

• No server installation required
• High speed
• 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 simultaneous writes from multiple users
• Limited performance when working with large amounts of data
• No built-in replication
• Limited capabilities for complex multi-user applications

Connecting to an SQLite Database in Python

Basic connection

import sqlite3

# Connecting to the database (if the file does not exist - it will be created)
conn = sqlite3.connect('my_database.db')

# Creating 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 parameters

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 frequent requests is how to insert data into an SQLite table:

cursor.execute('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', ('Иван', 30))
conn.commit()

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

Bulk data insertion

users = [('Алексей', 25), ('Мария', 28), ('Сергей', 35)]
cursor.executemany('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', users)
conn.commit()

Insertion with duplicate ignoring

cursor.execute('''
    INSERT OR IGNORE INTO users (name, age) VALUES (?, ?)
''', ('Иван', 30))
conn.commit()

Insertion with update on conflict

cursor.execute('''
    INSERT OR REPLACE INTO users (name, age) VALUES (?, ?)
''', ('Иван', 32))
conn.commit()

Reading Data from a Table

Selecting all data

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

Selection with conditions

cursor.execute('SELECT * FROM users WHERE age > ?', (25,))
rows = cursor.fetchall()
for row in rows:
    print(f"Имя: {row[1]}, Возраст: {row[2]}")

Paged selection

cursor.execute('SELECT * FROM users LIMIT ? OFFSET ?', (10, 0))
rows = cursor.fetchall()

Sorting results

cursor.execute('SELECT * FROM users ORDER BY age DESC')
rows = cursor.fetchall()

Updating Data - python sqlite update / update sqlite3 python

Updating a single record

To change data, use the UPDATE operator:

cursor.execute('''
    UPDATE users SET age = ? WHERE name = ?
''', (32, 'Иван'))
conn.commit()

Updating multiple records

cursor.execute('''
    UPDATE users SET age = age + 1 WHERE age < ?
''', (30,))
conn.commit()

Updating with record existence check

cursor.execute('''
    UPDATE users SET age = ? WHERE name = ? AND EXISTS (
        SELECT 1 FROM users WHERE name = ?
    )
''', (35, 'Алексей', 'Алексей'))
conn.commit()

Deleting Data from a Table

Deleting a specific record

cursor.execute('''
    DELETE FROM users WHERE name = ?
''', ('Сергей',))
conn.commit()

Deleting by condition

cursor.execute('''
    DELETE FROM users WHERE age < ?
''', (18,))
conn.commit()

Clearing the entire table

cursor.execute('DELETE FROM users')
conn.commit()

Handling Errors When Working with a Database

Proper closing of connections

conn.close()

Using try-except constructs

try:
    cursor.execute('SELECT * FROM users')
    print(cursor.fetchall())
except sqlite3.Error as e:
    print(f"Ошибка: {e}")
except sqlite3.IntegrityError as e:
    print(f"Ошибка целостности данных: {e}")

Using a context manager

with sqlite3.connect('my_database.db') as conn:
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM users')
    print(cursor.fetchall())

The context manager will automatically close the connection, even if an error occurs.

Working with Transactions

Manual transaction management

try:
    conn.execute('BEGIN TRANSACTION')
    cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Тест', 25))
    cursor.execute('UPDATE users SET age = ? WHERE name = ?', (26, 'Тест'))
    conn.commit()
except sqlite3.Error:
    conn.rollback()
    print("Транзакция отменена")

Using savepoint

conn.execute('SAVEPOINT sp1')
try:
    cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Тест2', 30))
    conn.execute('RELEASE SAVEPOINT sp1')
except sqlite3.Error:
    conn.execute('ROLLBACK TO SAVEPOINT sp1')

Optimizing Performance

Using prepared statements

stmt = cursor.execute('SELECT * FROM users WHERE age > ?')
for age_limit in [20, 30, 40]:
    cursor.execute(stmt, (age_limit,))
    print(cursor.fetchall())

Setting pragmas to increase performance

cursor.execute('PRAGMA synchronous = OFF')
cursor.execute('PRAGMA journal_mode = MEMORY')
cursor.execute('PRAGMA cache_size = 10000')

Frequently Asked Questions

Can I use SQLite for large projects?

SQLite is suitable for small to medium-sized projects. For large, high-load applications, it is better to use PostgreSQL or MySQL.

How to check if a table exists before creating it?

Use CREATE TABLE IF NOT EXISTS as shown above, or check through the system table:

cursor.execute('''
    SELECT name FROM sqlite_master WHERE type='table' AND name='users'
''')
if cursor.fetchone():
    print("Таблица существует")

How to avoid SQL injection?

Use parameterized queries with question marks instead of substituting strings through f-strings or concatenation.

How to get the number of records in a table?

cursor.execute('SELECT COUNT(*) FROM users')
count = cursor.fetchone()[0]
print(f'Количество записей: {count}')

How to get the ID of the last inserted record?

cursor.execute('''
    INSERT INTO users (name, age) VALUES (?, ?)
''', ('Екатерина', 27))
conn.commit()
last_id = cursor.lastrowid
print(f"ID новой записи: {last_id}")

How to work with NULL values?

cursor.execute('SELECT * FROM users WHERE name IS NULL')
null_users = cursor.fetchall()

cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', (None, 25))

Conclusion

Working with databases in Python using SQLite is simple and convenient. You learned how to perform basic operations - insert, update, delete, and select data. Using the standard sqlite3 module allows you to quickly organize data storage without complex server configuration.

SQLite is an excellent choice for learning the basics of working with databases, creating prototypes, and small applications. The knowledge gained is easily transferred to other database management systems.

Now you can easily cope with the tasks of working with databases in your projects, using the powerful capabilities of SQLite and Python.