How To Connect And Work With SQLite Database Using Python sqlite3

Today we’ll talk about how to connect and work with the SQLite database using Python. In case you have never heard of SQLite database before, it’s actually the most widely deployed and used database engine, according to its official website. You can find SQLite databases in almost any computing device, including your PCs/Macs, smartphones, etc.

Why consider using SQLite database

Unlike MS Access database, SQLite is a real and powerful database application.

Pros

The SQLite database is very small but fast and reliable. It can run on almost any device and super easy to set up. The maximum size of the database is said to be 281 terabytes (TB) or 281,000 GB, which is more than enough for most use cases.

Cons

Although SQLite databases support unlimited read access, only one write access is allowed at a time. Therefore it lacks scalability if multiple users need to frequently update the database. Also due to its design for local use, the database doesn’t require authentication, which means not a good candidate for enterprise uses especially if you want more control over the data access.

When should I use SQLite?

All that being said, SQLite database is a great tool for small-medium size projects. I use it all the time for making websites, and even workplace projects that are used by small teams (~10 people). It’s so simple to set up and use, you’ll see in a second.

sqlite3 is a native Python library for accessing SQLite databases. The library comes with the standard Python installation so no additional action is required to get it.

Unlike MS Access or other commercial database applications, you don’t need to install any additional driver to work with the SQLite database. (I’m not a database expert, if this statement is not true, please leave a comment!)

Create/Connect to A SQLite database

SQLite database is just a single file that ends with a “.db” file extension. We can connect to an existing SQLite database by using the .connect() method. If the database does not exist at the destination folder, the same method will just create it. How convenient 🙂

python connect to sqlite
python connect to sqlite

There’s also a feature in sqlite3 that allows you to create a database in computer RAM, which is a neat way to test things in a temporary database, which will cease to exist after you close the connection. Just put ":memory:" as the connection string.

python connect to sqlite
python connect to sqlite

Introducing DB Browser for SQLite

DB Browser for SQLite is a free and open-source tool to interact with SQLite databases with a graphical interface. You can use the software to view and edit database files. Although it has lots of capabilities, I use it mainly for viewing and understanding the data structure.

Work with the SQLite database

We still need to use the SQL language to “talk” to a SQLite database. Imagine that normally you use a program (with a graphical interface) to connect to a database, and you can enter a SQL statement in a text field then execute it. Now Python comes in and acts as that interface (connection) to execute the same SQL statement. Even better, remember that everything in Python is an object? You can assign a database connection to a variable, and re-use the connection in multiple instances. We also need a cursor object to execute SQL statements.

sqlite database cursor
sqlite database cursor

You can download the example database here that we’ll walk through in this tutorial. Anyone who’s into finance/investing might find the data interesting. It’s the historical positions of Tesla stock held by the ARKK fund. The ARK funds publish their stock holdings on a daily basis, and this example is a subset of the data I have been collecting for the past several months.

List all tables in the SQLite database

Let’s connect to the example SQLite database first, then query all table names from it “SELECT name FROM sqlite_master where type=’table'”. Notice that we use the cursor object to execute a SQL statement here. After executing the SELECT statement, we can treat the cursor as an iterator and loop through it.

find all table names in sqlite database
find all table names in sqlite database

Query The Database Using SQL

I try to focus on how to use Python to work with the SQLite database and not the SQL language itself. So if you need help with SQL, the w3schools is a very good site for learning just that.

cursor.fetchone() returns the record that the cursor is currently pointing at. Once this is called, the current record gets returned, then the cursor moves on to the next record.

cursor.fetchall() returns all the records obtained from the query “SELECT * from sample_data”

query the sqlite database using python
query the sqlite database using python

Use pandas To Query

Given that 99.635% of my work involves pandas, I prefer to use pandas for database queries. pandas provides a simple and clean API to work with SQL databases, we don’t have to worry about cursors or fetching results after execution. All we need is a SQL statement and the connection object, then pandas will extract everything (equivalent to fetchall()) for us. The best part is that the result is in a pandas dataframe!

query the sqlite databasae using pandas
query the sqlite databasae using pandas

Create New Table

SQL method

When using SQL statements to modify the database, we have to commit the changes, otherwise, they will not be saved. The below will create a new table with the name “new_table_name”, and specify the column names (column1, column2, column3, …) and their respective data types. However, this is an empty table with no data.

create a new table in sqlite database
create a new table in sqlite database

Add Records

add values in sqlite database table
add values in sqlite database table

We can also use the connection as a context manager. In this case the commit will be done automatically.

add values in sqlite database table
add values in sqlite database table

Load a pandas dataframe into SQLite database

This is my favorite method to update data in the SQLite database. We can either create a new table or add new records to existing tables, all with the same pandas method to_sql().

save a table into sqlite database
save a table into sqlite database
  • ‘fail’: Raise a ValueError
  • ‘replace’: Drop the existing table and insert the new one
  • ‘append’: Keep the existing table and insert new values after the existing records

Update Records

update values in sqlite database
update values in sqlite database

Delete Entire Table

To delete an entire table, it’s probably easier to just use the SQL way. There’s no effective pandas method for this operation.

delete an entire table from sqlite database
delete an entire table from sqlite database

Delete Records

Be careful with the delete statement because if we omit the keyword “WHERE”, all records from that table will be deleted.

delete records from a sqlite database table
delete records from a sqlite database table

Closing The Database

Always remember to close the database connection after we are done with it, also make sure that we commit the changes before closing the connection

close connection
close connection

Leave a Reply

Your email address will not be published. Required fields are marked *