SQLite with Python: A Beginner's Guide to Databases

Have you ever tried to use a CSV file as a mini-database? You start with 10 rows and 3 columns. Six months later, you have 14 files, some with 600 columns, and Excel takes five minutes to open one of them. I have been there. The good news is that Python already ships with a small, fast, free database called SQLite. In this python sqlite3 tutorial, you will build a real database from zero, store data, and read it back without installing a server.

You Need a Database (But Setting One Up Sounds Scary)

A lot of people avoid databases because the word sounds like something an IT department manages. But the mess you feel with CSVs is exactly the problem databases solve. You do not need a heavy server. You do not need to learn a separate program. If you have used messy CSV files, you know the pain of duplicate columns, missing values, and files named final_version2.csv. A database gives you one place to store data, plus a fast way to ask questions about it. This SQLite Python tutorial assumes you know nothing about SQL or databases. By the end, you will have a working book list database and a few lines of code to read it back.

What Is SQLite, Really?

So what is sqlite? It is a database stored in a single file on your computer. Think of it like an Excel workbook with a built-in index. Each table is a sheet. Each row is a record. Each column is a field. The big difference is that you talk to SQLite with Python code instead of pointing and clicking in a spreadsheet.

A server database like MySQL or PostgreSQL runs as a separate program. Your Python code has to connect to that program over a network, and someone has to install, configure, and secure the server. SQLite skips all of that. The database engine is built into Python itself through the sqlite3 module. No server, no password, no configuration file.

For a single user running a Python script on a laptop or a small home server, SQLite is often the right tool.

Your First Python SQLite3 Database in 10 Seconds

The sqlite3 module has been part of Python since Python 2.5, so you do not need to install SQLite. You just import the module and connect to a file. If the file does not exist, SQLite creates it for you.

import sqlite3

# Connect to (or create) a database file named books.db
conn = sqlite3.connect("books.db")

print("Connected to books.db")

conn.close()

Run that code in a terminal or .py file, and you will find books.db in the same folder as your script. That file is your entire database.

If you want a temporary database that disappears when the program ends, use the special name :memory:.

import sqlite3

# This database lives only in RAM while the script runs
conn = sqlite3.connect(":memory:")

print("This database exists only in memory.")

conn.close()

That is useful for testing code without leaving files behind.

Create a Table in Python SQLite3

A table holds the actual rows of data. Before you insert anything, you need to define the columns. This is where a small amount of SQL appears, but you do not need to memorize it right away.

import sqlite3

conn = sqlite3.connect("books.db")
cursor = conn.cursor()

# Create a table to hold book information
cursor.execute("""
CREATE TABLE IF NOT EXISTS books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    year INTEGER,
    rating REAL
)
""")

# Commit the statement so it is saved in the file
conn.commit()
conn.close()

print("Table is ready.")

The id column is the primary key. It gives every row a unique number automatically. Even if two books have the same title, the id keeps them separate. The AUTOINCREMENT keyword means SQLite fills in the next integer for you when you insert a row.

Add Data

Now you can put real rows into the table. Always use ? placeholders when you pass values from Python into SQL. This is the official way to prevent SQL injection. If a book title contains a weird character or even SQL code, the placeholder treats it as plain data, not a command.

import sqlite3

conn = sqlite3.connect("books.db")
cursor = conn.cursor()

# Insert one book using ? placeholders
cursor.execute(
    "INSERT INTO books (title, author, year, rating) VALUES (?, ?, ?, ?)",
    ("The Martian", "Andy Weir", 2014, 4.5),
)

conn.commit()
conn.close()

print("One book added.")

You can add several rows at once with executemany:

import sqlite3

conn = sqlite3.connect("books.db")
cursor = conn.cursor()

books = [
    ("Project Hail Mary", "Andy Weir", 2021, 4.7),
    ("Dune", "Frank Herbert", 1965, 4.3),
    ("Neuromancer", "William Gibson", 1984, 4.0),
]

# Insert all three books in one call
cursor.executemany(
    "INSERT INTO books (title, author, year, rating) VALUES (?, ?, ?, ?)",
    books,
)

conn.commit()
conn.close()

print("Three more books added.")

This is the same pattern that a search for "python store data sqlite" usually leads to: connect, insert, commit, close.

Read Data Back

Reading data is where SQLite starts to feel better than a folder full of CSVs. You can ask for all rows, filter them, and sort them with a short SQL statement.

import sqlite3

conn = sqlite3.connect("books.db")
cursor = conn.cursor()

# Select everything and order by rating from highest to lowest
cursor.execute("SELECT * FROM books ORDER BY rating DESC")
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

If you only want books by one author, add a WHERE filter:

import sqlite3

conn = sqlite3.connect("books.db")
cursor = conn.cursor()

# Get only Andy Weir's books
cursor.execute("SELECT title, year FROM books WHERE author = ?", ("Andy Weir",))
rows = cursor.fetchall()

for row in rows:
    print(row)

conn.close()

SELECT statements do not need commit() because they do not change the file. They only ask questions.

Update and Delete

These operations modify data, so you call commit() after each one. The WHERE part tells SQLite which row to change. If you skip WHERE, you will affect every row.

import sqlite3

conn = sqlite3.connect("books.db")
cursor = conn.cursor()

# Update the rating for The Martian
cursor.execute(
    "UPDATE books SET rating = ? WHERE title = ?",
    (4.8, "The Martian"),
)

conn.commit()
conn.close()

print("Rating updated.")

Deleting one row works the same way. Use the primary key when you can, because an id is unique.

import sqlite3

conn = sqlite3.connect("books.db")
cursor = conn.cursor()

# Delete the row where id is 3
cursor.execute("DELETE FROM books WHERE id = ?", (3,))

conn.commit()
conn.close()

print("Book deleted.")

Bonus: Read a Database with pandas

If you like working with DataFrames, pandas can read a SQLite table directly into one line of code. First install pandas with pip install pandas. Then use pd.read_sql_query and pass it a SQL string and a connection object.

import sqlite3
import pandas as pd

conn = sqlite3.connect("books.db")

# Read the books table into a pandas DataFrame
df = pd.read_sql_query("SELECT * FROM books", conn)

print(df.head())

conn.close()

That gives you a DataFrame you can filter, sort, export, or plot. If you come from CSV files, this feels familiar. If you want to learn more about DataFrames, see the CSV and pandas guide. The same pandas workflow also shows up when you work with Excel files in Python.

When NOT to Use SQLite

SQLite is great, but it has limits. The main one is writes. SQLite allows only one writer at a time. If you are building a website where hundreds of people try to add rows at the same moment, some writes will wait or fail. That is when you reach for a server database like PostgreSQL or MySQL.

SQLite also stores everything in one file. For a personal book list, that is perfect. For a huge production system with terabyte-scale data and multiple application servers, a server database has better tools for replication, permissions, and performance. The rule of thumb: single user, local file, Python script → SQLite. Many users, network, high write volume → server database.

FAQ

Do I need to install SQLite?

No. The sqlite3 module is part of Python's standard library. If Python is installed, SQLite is already available. You just write import sqlite3 and start using it.

Where is the database file stored?

When you call sqlite3.connect("books.db"), the file is created in the current working directory, which is usually the same folder as your Python script. If you pass an absolute path like /your/path/books.db, it is stored there. You can also use :memory: for a temporary database that disappears when the program ends.

Can SQLite handle a million rows?

Yes. As of 2026, SQLite handles a million rows on a normal laptop without trouble. The file size stays manageable, and queries with an indexed primary key are fast. The limit is less about total rows and more about how many people are trying to write at the same time.

Is SQLite free to use?

Yes. SQLite is public domain. That means it costs nothing to download, use, distribute, or include in a commercial project. There are no royalties or licenses to buy.

What's the difference between SQLite and MySQL?

SQLite is a file-based database built into your application. MySQL is a separate server program that your application connects to. SQLite requires no installation or administration, while MySQL needs a server process, user accounts, and a running daemon. For local scripts and single-user projects, SQLite is simpler. For multi-user websites and frequent concurrent writes, MySQL is often the better fit.

Next Steps

• Practice reading real data with the CSV and pandas guide.
• Combine SQLite with spreadsheet automation in the Python Excel tutorial.
• Automate local file backups with Python automated backups. All code in this article was tested and runs successfully on Python 3.8.10 (Ubuntu 20.04) with sqlite3 (Python standard library) and pandas 2.0.3 — verified August 2026.