If your project keeps JSON files or a CSV around for state, whether if it’s for user lists, caches, last-run timestamps - whatever - then you're probably going to want to migrate to using a database.
Python’s massive ecosystem includes a database that is found inside iPhones, browsers, and lots of embedded software that exists… everywhere.
This database is SQLite - it’s lightweight and gives you all of the benefits you’d expect with SQL: joins, indexes, transactions, etc. What you don’t get is a server, network access, or multiple writers at the same time (at the end you’ll understand why this is the case).
This makes for a great “starting point” if you’re wanting to learn more about databases.
» Knowing SQL is not a prerequisite to reading this, but having working knowledge of what it is helps! If you want to learn more about SQL or databases in general, reply to this email and let me know!
Writing to a SQLite Database
When writing data, you’ll call the execute function:
import sqlite3
# SQL to create a table
create_table_sql = """CREATE TABLE IF NOT EXISTS events (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
kind TEXT NOT NULL,
occurred TEXT NOT NULL
)"""
# Insert data into the database. The ? are "placeholders"
insert_sql = "INSERT INTO events (user_id, kind, occurred) VALUES (?, ?, ?)"
with sqlite3.connect("app.db") as connection:
# Create the table
connection.execute(create_table_sql)
# Insert the data.
connection.execute(
insert_sql,
(42, "login", "2026-04-28T10:15:00")
)I won’t get into the weeds of what things like INTEGER PRIMARY KEY and TEXT NOT NULL is, but we’re first creating the table if it doesn’t exist, then inserting data into the database.
If we were to look at a database viewer, such as DBCode, we can see that the data is inserted into our SQLite database:
Data being displayed inside of a database
» As mentioned before, sqlite3 is part of the standard library, so you don’t need to install it via pip or uv.
Reading data from a SQLite Database
While you could see the data in a database viewer tool like above, this isn’t particularly useful for your Python code - it’s more for humans.
So in order for us to read the data we just placed in from the database, we need code to do this:
select_stmt = """SELECT user_id, kind
FROM events
WHERE occurred > ?"""
with sqlite3.connect("app.db") as conn:
rows = conn.execute(
select_stmt,
("2026-04-01",),
).fetchall()
conn.close() # See "important" note below
for user_id, kind in rows:
print(user_id, kind) # 42 loginThe execute() function returns a cursor that you can iterate or call fetchall() (which fetches all rows that satisfy your SELECT statement).
🛑 Important: you’re probably not used to handling closing files when you’re using the with statement. The sqlite3 package is different - you must close the connection once you are complete by calling conn.close() before you exit out of the context manager.
When would you use SQLite?
There’s multiple reasons to use a SQLite database:
You need a local cache for expensive computations.
You want to save data off and not load it into memory.
Outside of simple data management that only one person would use, you’ll want to reach for a server database (Postgres, MySQL) when:
You more than one process that needs to write at the same time.
The database needs to live on a different machine from the app.
You need user accounts and per-user permissions.
SQLite has issues with concurrent writes since it’s serverless - if you have 2 separate processes wanting to write to the file, you may get a database is locked error. There are settings that help (such as WAL), but they don’t change the underlying fact: with SQLite, you get one writer at a time.
Happy coding!
📧 Join the Python Snacks Newsletter! 🐍
Want even more Python-related content that’s useful? Here’s 3 reasons why you should subscribe the Python Snacks newsletter:
Get Ahead in Python with bite-sized Python tips and tricks delivered straight to your inbox, like the one above.
Exclusive Subscriber Perks: Receive a curated selection of up to 6 high-impact Python resources, tips, and exclusive insights with each email.
Get Smarter with Python in under 5 minutes. Your next Python breakthrough could just an email away.
You can unsubscribe at any time.
Interested in starting a newsletter or a blog?
Do you have a wealth of knowledge and insights to share with the world? Starting your own newsletter or blog is an excellent way to establish yourself as an authority in your field, connect with a like-minded community, and open up new opportunities.
If TikTok, Twitter, Facebook, or other social media platforms were to get banned, you’d lose all your followers. This is why you should start a newsletter: you own your audience.
This article may contain affiliate links. Affiliate links come at no cost to you and support the costs of this blog. Should you purchase a product/service from an affiliate link, it will come at no additional cost to you.
