Python SQLite API Guide: Build Database Apps

Need a simple database for your Python project? Look no further than SQLite. It is a lightweight, serverless database engine. It stores the entire database in a single file on disk.

The sqlite3 module is Python's built-in API for working with SQLite. You do not need to install anything extra. This makes it perfect for prototypes, small tools, and learning.

This guide will show you how to use the Python SQLite API. You will learn to connect, create tables, and manage data.

Why Choose SQLite with Python?

SQLite has many benefits for Python developers. It requires zero configuration. There is no separate server process to manage.

The database is a single file. This makes it easy to share or backup. It supports standard SQL, so your skills transfer directly.

It is ideal for development, testing, and small to medium-sized applications. Think desktop apps, data analysis scripts, or simple web backends.

Getting Started: Connect to a Database

Your first step is to connect to a database. Use the connect() function. If the file does not exist, SQLite creates it automatically.

# Import the built-in sqlite3 module
import sqlite3

# Connect to a database file named 'mydatabase.db'
# If it doesn't exist, it will be created.
connection = sqlite3.connect('mydatabase.db')

# Always close the connection when done
connection.close()
    

You now have a connection object. This is your gateway to the database. Remember to close it to free resources.

Creating Tables with Cursors

To execute SQL commands, you need a cursor. A cursor is like a pointer that moves through your database results.

You create one from your connection. Then you use its execute() method to run SQL.

import sqlite3

conn = sqlite3.connect('mydatabase.db')
# Create a cursor object
cursor = conn.cursor()

# SQL command to create a 'users' table
create_table_sql = """
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);
"""

# Execute the SQL command
cursor.execute(create_table_sql)

# Commit the transaction to save changes
conn.commit()

conn.close()
print("Table 'users' created successfully.")
    

Table 'users' created successfully.
    

The CREATE TABLE IF NOT EXISTS command is safe. It only creates the table if it does not already exist. The conn.commit() call is crucial. It saves your changes to the database file.

Inserting Data into Your Table

Now let's add some records. Use the INSERT INTO SQL statement. You can insert one row at a time or many.

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# Insert a single record
cursor.execute("INSERT INTO users (username, email) VALUES (?, ?)", ('alice', '[email protected]'))

# Insert multiple records at once
new_users = [
    ('bob', '[email protected]'),
    ('charlie', '[email protected]')
]
cursor.executemany("INSERT INTO users (username, email) VALUES (?, ?)", new_users)

conn.commit()
conn.close()
print("Data inserted successfully.")
    

Notice the ? placeholders. This is the parameterized query style. It is the safest way to insert data. It prevents SQL injection attacks.

The executemany() method is efficient for multiple rows. It runs the same query with different parameters.

Querying and Fetching Data

Reading data is a core task. Use the SELECT statement. The cursor provides methods to get results.

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# Select all columns from the users table
cursor.execute("SELECT * FROM users")

# Fetch all results as a list of tuples
all_users = cursor.fetchall()
print("All Users:")
for user in all_users:
    print(f"ID: {user[0]}, Username: {user[1]}, Email: {user[2]}")

print("\n---\n")

# Select a specific user
cursor.execute("SELECT username, email FROM users WHERE id = ?", (2,))
# Fetch a single result
single_user = cursor.fetchone()
print(f"User with ID 2: {single_user}")

conn.close()
    

All Users:
ID: 1, Username: alice, Email: [email protected]
ID: 2, Username: bob, Email: [email protected]
ID: 3, Username: charlie, Email: [email protected]

---

User with ID 2: ('bob', '[email protected]')
    

Use fetchall() to get every matching row. Use fetchone() to get just the first result. The data comes back as tuples.

Updating and Deleting Records

You will often need to change or remove data. Use UPDATE and DELETE statements.

import sqlite3

conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()

# Update Alice's email
cursor.execute("UPDATE users SET email = ? WHERE username = ?", ('[email protected]', 'alice'))

# Delete user 'charlie'
cursor.execute("DELETE FROM users WHERE username = ?", ('charlie',))

conn.commit()

# Verify the changes
cursor.execute("SELECT * FROM users")
print("Updated User List:")
for user in cursor.fetchall():
    print(user)

conn.close()
    

Updated User List:
(1, 'alice', '[email protected]')
(2, 'bob', '[email protected]')
    

Always include a WHERE clause. Otherwise, you will update or delete all rows in the table. This is a common mistake.

Using Context Managers for Safety

Python's `with` statement is perfect for databases. It automatically handles closing connections and committing transactions.

import sqlite3

# Using 'with' ensures the connection is closed properly
with sqlite3.connect('mydatabase.db') as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    # conn.commit() is called automatically on success
    # conn.rollback() is called automatically on error
    for row in cursor.fetchall():
        print(row)
# Connection is closed automatically here
print("Connection closed.")
    

This method is cleaner and safer. You do not need to remember to call close(). It also helps with error handling.

Building an API with SQLite and Flask

SQLite is great for backend APIs. Combine it with a framework like Flask. You can create a simple REST API to manage your data.

For a more structured approach, you might