Python SQLite3 Database Tutorial

Quick start: Python sqlite3 example 🔝

The example above runs entirely in memory. For a persistent database, continue with the next section. The sqlite3 module is included with standard Python builds.

Creating and connecting to a SQLite database 🔝

Connect to a file-based SQLite database

SQLite creates the database file when it does not already exist. Use a dedicated practice database rather than a production file.

import sqlite3
my_conn = sqlite3.connect('plus2net_sqlite_practice.db')
my_conn.execute('PRAGMA foreign_keys = ON')
print('Database connection ready')

Expected output:

Database connection ready

Use an absolute path if needed; the relative filename above writes to the current working directory. See SQLite connection details and SQLite with Google Drive in Colab.

Creating a student table 🔝

Create the student table

Use CREATE TABLE IF NOT EXISTS to avoid an error when the table already exists. INTEGER PRIMARY KEY supplies automatically generated numeric IDs when omitted.

 Copy
my_conn.execute('''CREATE TABLE IF NOT EXISTS student (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    class TEXT,
    mark INTEGER CHECK (mark BETWEEN 0 AND 100),
    gender TEXT
)''')
my_conn.commit()
print('Student table ready')

Expected output:

Student table ready

Adding records to the table 🔝

Insert sample student records

Use placeholders and executemany() for multiple rows. The example includes recognizable student records from this tutorial; the linked full dataset contains more rows.

 Copy
students = [
    (1, 'John Deo', 'Four', 75, 'female'),
    (2, 'Max Ruin', 'Three', 85, 'male'),
    (3, 'Arnold', 'Three', 55, 'male'),
    (4, 'Krish Star', 'Four', 60, 'female'),
    (34, 'Gain Toe', 'Seven', 69, 'male'),
    (35, 'Rows Noump', 'Six', 88, 'female')
]
my_conn.executemany('''INSERT OR IGNORE INTO student
    (id, name, class, mark, gender)
    VALUES (?, ?, ?, ?, ?)''', students)
my_conn.commit()
print('Sample records available:', my_conn.execute('SELECT COUNT(*) FROM student').fetchone()[0])

Expected output (example):

Sample records available: 6 (on a new practice database)

INSERT OR IGNORE skips an existing record with the same primary key. If you have previously added more records, the count may be higher.

Full student dataset (35 rows) The examples here use six rows so results stay easy to verify.

Reading and filtering records 🔝

Display student records

fetchall() returns a list of tuples. ORDER BY keeps the demonstration output predictable.

rows = my_conn.execute('SELECT id, name, class, mark, gender FROM student ORDER BY id').fetchall()
for row in rows:
    print(row)

Expected output:

(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
(4, 'Krish Star', 'Four', 60, 'female')
(34, 'Gain Toe', 'Seven', 69, 'male')
(35, 'Rows Noump', 'Six', 88, 'female')

Filter and sort records with WHERE and ORDER BY

Pass search values separately instead of building SQL by concatenating user input.

 Copy
minimum_mark = 75
rows = my_conn.execute('''SELECT name, mark FROM student
    WHERE mark >= ? ORDER BY mark DESC, name''', (minimum_mark,)).fetchall()
for row in rows:
    print(row)

Expected output:

('Rows Noump', 88)
('Max Ruin', 85)
('John Deo', 75)

Get a single matching row

fetchone() retrieves the first record, or returns None if there is no match.

row = my_conn.execute(
    'SELECT name, mark FROM student WHERE id = ?', (2,)
).fetchone()
print(row)

Expected output:

('Max Ruin', 85)

Inspecting table structure 🔝

View the table structure with PRAGMA

PRAGMA table_info() reports the column position, name, declared type, NOT NULL flag, default value, and primary-key flag.

for row in my_conn.execute('PRAGMA table_info(student)'):
    print(row)

Expected output:

(0, 'id', 'INTEGER', 0, None, 1)
(1, 'name', 'TEXT', 1, None, 0)
(2, 'class', 'TEXT', 0, None, 0)
(3, 'mark', 'INTEGER', 0, None, 0)
(4, 'gender', 'TEXT', 0, None, 0)

Collect column names as a Python list

Each PRAGMA result contains the column name at index 1.

columns = [row[1] for row in my_conn.execute('PRAGMA table_info(student)')]
print(columns)

Expected output:

['id', 'name', 'class', 'mark', 'gender']

Counting and grouping records 🔝

Count rows using COUNT(*)

COUNT(*) counts the records in a table. Add WHERE when only a subset should be counted.

total = my_conn.execute('SELECT COUNT(*) FROM student').fetchone()[0]
print('Total student records:', total)

Expected output (example):

Total student records: 6 (on a new practice database)

Count students by class

GROUP BY summarizes records across categories.

rows = my_conn.execute('''SELECT class, COUNT(*) AS total FROM student
    GROUP BY class ORDER BY class''').fetchall()
for row in rows:
    print(row)

Expected output:

('Four', 2)
('Seven', 1)
('Six', 1)
('Three', 2)

Inserting, updating and deleting records 🔝

Insert a single row and obtain its ID

Use cursor.lastrowid after INSERT. The name is chosen for the dedicated practice database, not for a live student table.

 Copy
cursor = my_conn.execute(
    'INSERT INTO student (name, class, mark, gender) VALUES (?, ?, ?, ?)',
    ('New Student', 'Four', 45, 'female')
)
my_conn.commit()
print('Inserted row ID:', cursor.lastrowid)

Expected output (example):

Inserted row ID: 36 (with the initial sample records)

The generated ID can differ if you have run the INSERT example before.

Update selected records

Update only matching rows with WHERE; rowcount indicates how many records matched.

 Copy
cursor = my_conn.execute(
    'UPDATE student SET mark = ? WHERE name = ?',
    (90, 'Max Ruin')
)
my_conn.commit()
print('Rows updated:', cursor.rowcount)

Expected output:

Rows updated: 1

Delete a chosen record

DELETE without WHERE removes every row. This example deletes only the practice row inserted earlier.

 Copy
cursor = my_conn.execute(
    'DELETE FROM student WHERE name = ?', ('New Student',)
)
my_conn.commit()
print('Rows deleted:', cursor.rowcount)

Expected output:

Rows deleted: 1

Find rows affected using changes()

SQLite changes() reports rows changed by the most recent INSERT, UPDATE, or DELETE statement on this connection.

changed = my_conn.execute('SELECT changes()').fetchone()[0]
print('Rows affected by previous statement:', changed)

Expected output:

Rows affected by previous statement: 1

Handle write errors and roll back

A CHECK constraint rejects invalid marks. The context manager commits valid transactions and rolls back after an exception.

 Copy
try:
    with my_conn:
        my_conn.execute(
            'INSERT INTO student (name, mark) VALUES (?, ?)',
            ('Invalid Mark', 150)
        )
except sqlite3.IntegrityError as error:
    print('Rejected invalid mark:', type(error).__name__)

Expected output:

Rejected invalid mark: IntegrityError

Listing SQLite database tables 🔝

List database tables

sqlite_master describes database objects. This query shows user-created tables.

 Copy
rows = my_conn.execute('''SELECT name FROM sqlite_master
    WHERE type = 'table' AND name NOT LIKE 'sqlite_%'
    ORDER BY name''').fetchall()
for row in rows:
    print(row[0])

Expected output:

student

Count records in each table safely

Table identifiers cannot be SQL parameters; properly quote database identifiers when composing a statement from table names.

 Copy
table_names = [row[0] for row in my_conn.execute(
    "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
)]
for table_name in table_names:
    quoted_name = '"' + table_name.replace('"', '""') + '"'
    total = my_conn.execute('SELECT COUNT(*) FROM ' + quoted_name).fetchone()[0]
    print(table_name, ':', total)

Expected output:

student : 6 (after the insert/update/delete sequence)

Practical SQLite features 🔝

Read rows as dictionaries with sqlite3.Row

Set row_factory to sqlite3.Row when you need access by column name, such as row["name"].

my_conn.row_factory = sqlite3.Row
row = my_conn.execute('SELECT id, name FROM student WHERE id = ?', (1,)).fetchone()
print(dict(row))
my_conn.row_factory = None

Expected output:

{'id': 1, 'name': 'John Deo'}

Use a transaction with SAVEPOINT

SAVEPOINT allows testing a change and undoing it without changing the permanent practice data.

 Copy
my_conn.execute('SAVEPOINT practice_demo')
my_conn.execute('UPDATE student SET mark = 99 WHERE id = 1')
print('Temporary mark:', my_conn.execute(
    'SELECT mark FROM student WHERE id = 1').fetchone()[0])
my_conn.execute('ROLLBACK TO SAVEPOINT practice_demo')
my_conn.execute('RELEASE SAVEPOINT practice_demo')
print('Restored mark:', my_conn.execute(
    'SELECT mark FROM student WHERE id = 1').fetchone()[0])

Expected output:

Temporary mark: 99
Restored mark: 75

Work with SQLite dates

SQLite stores common date values as ISO-format text and provides functions such as date() for calculations.

today = my_conn.execute("SELECT date('now')").fetchone()[0]
next_week = my_conn.execute("SELECT date('now', '+7 days')").fetchone()[0]
print('Today:', today)
print('After seven days:', next_week)

Expected output (example):

Today: YYYY-MM-DD
After seven days: YYYY-MM-DD (seven days later)

For further use, see date queries, date table SQL dump and BLOB binary data.

Using SQLAlchemy with SQLite 🔝

Install SQLAlchemy

SQLAlchemy is optional. It is useful when one application works with several SQL databases.

pip install sqlalchemy

Connect and run SQL using SQLAlchemy 2.x

Use text() for SQL statements and a context manager for the connection. This reads the practice database created above.

 Copy
from sqlalchemy import create_engine, text
engine = create_engine('sqlite:///plus2net_sqlite_practice.db')
with engine.connect() as connection:
    rows = connection.execute(
        text('SELECT name, mark FROM student WHERE mark >= :minimum'),
        {'minimum': 80}
    ).fetchall()
    for row in rows:
        print(row)

Expected output:

('Max Ruin', 90)
('Rows Noump', 88)

Handle SQLAlchemy database errors

Catch SQLAlchemyError where an application needs to report errors without exposing connection credentials.

 Copy
from sqlalchemy.exc import SQLAlchemyError
try:
    with engine.connect() as connection:
        result = connection.execute(text('SELECT COUNT(*) FROM student'))
        print('Total:', result.scalar_one())
except SQLAlchemyError as error:
    print('Database operation failed:', type(error).__name__)

Expected output (example):

Total: 6 (on a new practice database)

More examples: SQLAlchemy insert and SQLAlchemy delete.

Exporting SQLite data for Excel 🔝

Export database records to CSV

The built-in csv module produces a spreadsheet-friendly CSV without extra packages. For .xlsx files, follow the related Excel tutorial.

 Copy
import csv
rows = my_conn.execute('SELECT id, name, class, mark, gender FROM student ORDER BY id')
with open('student_export.csv', 'w', newline='', encoding='utf-8') as file:
    writer = csv.writer(file)
    writer.writerow(['id', 'name', 'class', 'mark', 'gender'])
    writer.writerows(rows)
print('Created student_export.csv')

Expected output:

Created student_export.csv

For Excel workbook output and two-way data transfer, see SQLite and Excel with Pandas and SQLite and Google Sheets.

Closing and removing databases 🔝

Close the connection

Close the connection after finishing the database tasks.

my_conn.close()
print('Database connection closed')

Expected output:

Database connection closed

Remove a separate example database file

A SQLite database is stored in a file. Only delete a database when you are sure it is no longer needed. This safely demonstrates deletion on a separate temporary example file.

 Copy
from pathlib import Path
example_path = Path('sqlite_delete_example.db')
example_connection = sqlite3.connect(str(example_path))
example_connection.close()
example_path.unlink()
print('Example database file removed')

Expected output:

Example database file removed

SQLite tutorial reference and next steps 🔝

Use these shorter tutorials to learn individual SQL commands before combining them in a complete program.

SQLite with Google Drive in Colab Download sample database and notebooks (.zip)

SQLite is also useful inside graphical tools. Explore Tkinter database applications and the Python webpage auditor.

Common errors and troubleshooting 🔝

sqlite3.OperationalError: no such table

Run the CREATE TABLE example first and confirm that you connected to the correct database file.

sqlite3.IntegrityError

A primary-key, NOT NULL, CHECK, or foreign-key constraint was violated. Review the inserted values.

sqlite3.ProgrammingError: Cannot operate on a closed database

Create a new connection or run database operations before closing the connection.

database is locked

Another connection may have an uncommitted transaction. Commit or roll back, close idle connections, and avoid long-running writes.

Changes are not saved

Call commit() after INSERT, UPDATE or DELETE, or use with my_conn: for transaction handling.

no such module named sqlalchemy

Use pip install sqlalchemy in the Python environment running the script.

Frequently asked questions 🔝

Do I need to install SQLite separately for Python?

No. The sqlite3 Python module is included with standard Python installations. A separate database server is unnecessary.

Where does SQLite store the database?

A file-based SQLite connection stores data in the specified file. The :memory: option creates a temporary database that disappears when its connection closes.

How do I stop SQL injection?

Use bound placeholders (?) for values. Do not concatenate values into SQL queries. Quote validated table and column identifiers if dynamic identifiers are unavoidable.

Why should I call commit()?

Write operations can be part of a transaction. commit() makes changes persistent, while rollback() undoes uncommitted changes.

Can I use SQLite with Pandas or Excel?

Yes. Use Pandas read_sql_query() to create a DataFrame, and export with to_excel(). You can also use Python's built-in csv module, as shown above.

Is SQLite suitable for a web application?

SQLite is useful for small services and moderate concurrency. For high write concurrency or distributed server workloads, consider a server-based database.

Related Python tutorials 🔝

Continue with SQLite connections, inserting data, SELECT queries, webpage auditing with SQLite, SQLite command-line management, or Python file management.

See also SQL COUNT() and SQLite through PHP.