Skip to main content

SQLite3 in Python: Tutorial & Examples

SQLite3 in Python is a lightweight, file-based SQL database engine that requires zero server setup. The sqlite3 module (built into Python since version 3.2) lets you create, query, and modify databases stored as a single file on your filesystem. SQLite3 is the most widely deployed database engine in the world, powering browsers, mobile apps, and millions of Python scripts.

In this tutorial, you'll create a real SQLite3 database, define tables, insert records, and retrieve data—everything a professional backend developer needs for local development and testing.

Getting Started: Create Your First SQLite3 Database​

A SQLite3 database is literally a single file. When you open it, Python creates the file if it doesn't exist. Here's a complete working example that creates a database with a contacts table and inserts a record:

import sqlite3

# Open a connection to the database file (creates it if missing)
conn = sqlite3.connect('contacts.db')
cursor = conn.cursor()

# Create a table with columns and types
cursor.execute('''
    CREATE TABLE IF NOT EXISTS contacts (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        phone TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
''')

# Insert a record
cursor.execute(
    'INSERT INTO contacts (name, email, phone) VALUES (?, ?, ?)',
    ('Alice Johnson', '[email protected]', '555-1234')
)

# Commit changes to disk
conn.commit()

print("Database created and record inserted.")
conn.close()

When you run this code, a contacts.db file appears in your current directory. The CREATE TABLE IF NOT EXISTS clause prevents errors if the table already exists. The ? placeholders (covered later) are the safe way to pass user input to SQL queries.

Reading Data: SELECT and fetchall()​

Once you have data, you retrieve it with SELECT queries. The cursor object provides three methods to fetch results:

import sqlite3

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

# Fetch all rows as a list of tuples
cursor.execute('SELECT * FROM contacts')
all_rows = cursor.fetchall()
for row in all_rows:
    print(f"ID: {row[0]}, Name: {row[1]}, Email: {row[2]}")

# Fetch one row at a time
cursor.execute('SELECT name, email FROM contacts WHERE name LIKE "A%"')
one_row = cursor.fetchone()
if one_row:
    print(f"First match: {one_row}")

# Fetch a specific number of rows
cursor.execute('SELECT * FROM contacts')
first_two = cursor.fetchmany(2)
for row in first_two:
    print(row)

conn.close()

fetchall() returns a list of all matching rows (as tuples); fetchone() returns one row or None if no match; fetchmany(n) returns up to n rows. Each tuple's elements correspond to the column order in your SELECT statement.

Updating and Deleting Records​

Modifying existing data requires UPDATE or DELETE statements, followed by conn.commit() to save changes:

import sqlite3

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

# Update a record
cursor.execute(
    'UPDATE contacts SET phone = ? WHERE name = ?',
    ('555-9999', 'Alice Johnson')
)

# Delete a record
cursor.execute('DELETE FROM contacts WHERE email = ?', ('[email protected]',))

# Verify changes
conn.commit()
print(f"Rows affected by last operation: {cursor.rowcount}")

conn.close()

The cursor.rowcount property returns how many rows the last INSERT, UPDATE, or DELETE affected—useful for validating that your operation succeeded.

How SQLite3 Data Types Work​

SQLite3 supports five core data types: NULL (empty), INTEGER (whole numbers), REAL (floating-point), TEXT (strings), and BLOB (binary data). Python automatically converts between Python types and SQLite3 types:

Python TypeSQLite3 Type
NoneNULL
intINTEGER
floatREAL
strTEXT
bytesBLOB

When you fetch a row, SQLite3 returns the values in their SQLite3 type, and Python converts them back. For example, an INTEGER column becomes a Python int; a TEXT column becomes a str.

Common Pitfalls: Forgetting to Commit​

The most common mistake is inserting data, closing the connection, then reopening the database and finding the data is gone. This happens because INSERT, UPDATE, and DELETE are not automatically persisted to disk—you must call conn.commit() first:

import sqlite3

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

cursor.execute('INSERT INTO contacts (name, email) VALUES (?, ?)',
               ('Bob Smith', '[email protected]'))

# WRONG: Closing without commit() — changes are lost
# conn.close()

# CORRECT: Commit before closing
conn.commit()
conn.close()

A good practice is to wrap your work in a context manager so Python automatically commits or rolls back on exception:

with sqlite3.connect('contacts.db') as conn:
    cursor = conn.cursor()
    cursor.execute('INSERT INTO contacts (name, email) VALUES (?, ?)',
                   ('Charlie Davis', '[email protected]'))
    # Automatically commits when exiting the with block

Key Takeaways​

  • SQLite3 is Python's built-in file-based SQL database—zero setup required.
  • Use CREATE TABLE to define the structure, INSERT to add data, and SELECT to retrieve.
  • Always call conn.commit() after INSERT, UPDATE, or DELETE to persist changes.
  • Use ? placeholders in queries (not f-strings) to prevent SQL injection.
  • fetchall() returns all rows; fetchone() returns one row; fetchmany(n) returns up to n rows.
  • SQLite3 automatically converts between Python types and SQLite3 types.

Frequently Asked Questions​

Where is my SQLite3 database file stored?​

SQLite3 creates a file in the directory where your Python script is located (or wherever you specify the path). If you pass 'contacts.db', it's in the current working directory. Use absolute paths like '/home/user/data/contacts.db' to control the location explicitly.

Can multiple Python programs access the same SQLite3 database at the same time?​

Yes, but with limitations. SQLite3 uses file-level locks, so concurrent writes can be slow and may cause LOCKED errors. For reliable concurrent access, use PostgreSQL. SQLite3 is optimized for a single writer and many readers.

How do I see what tables are in my database?​

Query the SQLite3 internal schema table:

cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
tables = cursor.fetchall()
print(tables)

What if my table definition is wrong?​

SQLite3 does not support ALTER TABLE in the way other databases do. The simplest fix is to drop the table and recreate it:

cursor.execute('DROP TABLE IF EXISTS contacts')
conn.commit()
# Then create the table again with the correct definition

Can I use SQLite3 for a web application with thousands of users?​

SQLite3 struggles with high concurrency because it locks the entire database file for writes. For web apps, use PostgreSQL or MySQL instead. SQLite3 is ideal for single-user applications, testing, and embedded systems.

Further Reading​