
If you are a software developer, I believe you must know or even have used an extremely light-weighted Database – SQLite. It has almost all the features you need as a relational database, but everything is saved in a single file. On the official site, here are some scenarios that you could use SQLite.
- Embedded devices and IoT
- Data Analysis
- Data Transferring
- File archive and/or data container
- Internal or temporary databases
- Stand-in for an enterprise database during demos or testing
- Education, training and testing
- Experimental SQL language extensions
There are more reasons that you may want to use SQLite, please check out the documentation.
Most importantly, SQLite is built-in into a Python library. In other words, you don’t need to install any server-side/client-side software, and you don’t need to keep something running as a service, as long as you imported the library in Python and start coding, then you have a relational database management system!
Import and Usage

When we say "built-in", it means that you don’t even need to run pip install
to acquire the library. Simply import it by:
import sqlite3 as sl
Create a Connection to DB
Don’t be bothered with the drivers, connection strings and so on. You can create an SQLite database and have a connection object as simple as:
con = sl.connect('my-test.db')
After we run this line of code, we have created the database and connected it to it already. This is because the database we asked Python to connect to is not existing so that it automatically created an empty one. Otherwise, we can use the same code to connect to an existing database.

Create a Table
Then, let’s create a table.
with con:
con.execute("""
CREATE TABLE USER (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT,
age INTEGER
);
""")
In this USER
table, we added three columns. As you can see, SQLite is indeed lightweight, but it supports all the basic features a regular RDBMS should have, such as the data type, nullable, primary key and auto-increment.
After running this code, we should have created a table already, although it outputs nothing.
Insert Records
Let’s insert some records into the USER
table we just created, which can also prove that we indeed created it.
Suppose we want to insert multiple entries in one go. SQLite in Python can achieve this easily.
sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
data = [
(1, 'Alice', 21),
(2, 'Bob', 22),
(3, 'Chris', 23)
]
We need to define the SQL statement with question marks ?
as a placeholder. Then, let’s create some sample data to be inserted. With the connection object, we can then insert these sample rows.
with con:
con.executemany(sql, data)
It didn’t complain after we’ve run the code, so it was successful.
Query the Table
Now, it’s time to verify everything we have done tangibly. Let’s query the table to get the sample rows back.
with con:
data = con.execute("SELECT * FROM USER WHERE age <= 22")
for row in data:
print(row)

You can see how simple it is.
Also, even though SQLite is light-weighted, but as a widely-used database, most of the SQL clients software support to consume it.
The one I use the most is DBeaver, let’s see how it looks like.
Connect to SQLite DB from SQL Client (DBeaver)
Because I’m using Google Colab, so I’m going to download the my-test.db
file to my local machine. In your case, if you run Python on your local machine, you can use your SQL client to connect directly to the databases file.
In DBeaver, create a new connection and select SQLite as DB type.

Then, browse the DB file.

Now, you can run any SQL query on the database. It is nothing different from other regular relational databases.

Seamless Integrate with Pandas

Do you think that’s all? No. In fact, as a built-in feature of Python, SQLite can seamlessly integrate with Pandas Data Frame.
Let’s define a data frame.
df_skill = pd.DataFrame({
'user_id': [1,1,2,2,3,3,3],
'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning']
})

Then, we can simply call to_sql()
method of the data frame to save it into the database.
df_skill.to_sql('SKILL', con)
That’s it! We even don’t need to create the table in advance, the column data types and length will be inferred. Of course, you can still define it beforehand if you want to.
Then, let’s say we want to join the table USER
and SKILL
, and read the result into a Pandas data frame. It’s also seamless.
df = pd.read_sql('''
SELECT s.user_id, u.name, u.age, s.skill
FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id
''', con)

Super cool! Let’s write the results to a new table called USER_SKILL
.
df.to_sql('USER_SKILL', con)
Then, we can also use our SQL client to retrieve the table.

Summary

Indeed, there are many surprises hidden in Python. They do not mean to be hidden, but just because there are too many out-of-box features existing in Python for one to discover all of them.
In this article, I have introduced how to use the Python built-in library sqlite3
to create and manipulate tables in an SQLite DB. Of course, it also supports updating and deleting but I think you would try it yourself after this.
Most importantly, we can easily read a table from an SQLite DB into a Pandas data frame, or vice versa. This allows us to even more easily to interact with our light-weight relational database.
You may notice that SQLite doesn’t have authentication, that’s its designed behaviour as everything needs to be lightweight. Go discover more surprising features in Python, enjoy it!
All the code in this article can be found in my Google Colab Notebook.
If you feel my articles are helpful, please consider joining Medium Membership to support me and thousands of other writers! (Click the link above)