Last Updated on July 14, 2022 by Jay
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 🙂
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.
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.
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.
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”
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!
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.
Add Records
We can also use the connection as a context manager. In this case the commit will be done automatically.
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().
- ‘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
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 Records
Be careful with the delete statement because if we omit the keyword “WHERE”, all records from that table will be deleted.
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
One comment