SQLite: Integrating Python and SQL

In this blog post, I will talk about a SQL library “SQLite” and how can we use that using python. Please note that this article will not go into SQL commands or functions. This article will mainly focus on how we can leverage Python and SQLite together.

---

Introduction

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The code for SQLite is in the public domain and thus free for use for any commercial or private purpose. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to an ordinary disk file.

---

Let’s get started

Before we dive deep into SQLite, there are multiple ways to use SQLite. It offers a command line tool for direct use or you can use it in regular python with the use of the “sqlite3” library. For general querying, you can use the command line tool but for automation, you can use python.

---

SQLite: Command Line Tool

The command line tool is called “sqlite3” and when you run it, you need to provide a file name for it to use as a storage mechanism. If the file doesn’t exist, then it will create that file. For the purpose of this exercise, I will create a table for tracking my finances.

sqlite3 finance.db

The above code access/creates a database file “finance”. SQLite offers various functions, you can get a list of all of them by entering “.help”. Some common functions are:

Function	Description
.tables ?TABLE?	List names of tables. If TABLE is specified, only list tables matching LIKE pattern TABLE.
.save FILE	Write in-memory database into FILE
.databases	List names and files of attached databases

---

Creating tables

For creating a table, you can simply write a SQL code as you would in any SQL environment. Let’s create a table to store my finances. Make sure to use “;” to end your SQL statement.

sqlite> CREATE TABLE finance_data (       
   ...> date DATE,
   ...> category VARCHAR,
   ...> amount FLOAT,
   ...> note VARCHAR,
   ...> account VARCHAR
   ...> );

---

Inserting data

You can simply write the INSERT SQL command directly in the command line tool as you would in any SQL environment. I will be using python to write data into the database. But for the purpose of this exercise, let’s add one row into our table.

sqlite> INSERT INTO finance_data VALUES (
   ...>         '18/07/2022',
   ...>         'Test',
   ...>         100,
   ...>         'Test Note',
   ...>         'Test Account'
   ...> );

---

Querying table

Similar to any other SQL environment, you can simply write the SELECT statement to query the table. Let’s get all the data from our newly created table.

sqlite> SELECT * FROM finance_data;

Output:
18/07/2022|Test|100.0|Test Note|Test Account

---

Deleting rows

As you may have guessed, deleting rows is similar to a SQL environment. Let’s now remove the newly added row.

sqlite> DELETE FROM finance_data WHERE note = 'Test Note';

---

You can also create table with constraints, join table in SELECT statements, DROP tables, UPDATE tables, etc. I won’t go into every single of these use cases, but if you have general idea about about SQL, then it should be pretty straight forward.

---

SQLite: Python library

In this section, I will go over the above use cases but in a pythonic way. First we need to create a connection to the database file and create a cursor which points to that connection. The cursor will then be used for executing various processes.

# Importing Library
import sqlite3

connection = sqlite3.connect('finance.db')
cursor = connection.cursor()

---

Inserting data

I already have a CSV file which I want to read and write to my “finance_data” table. Let’s see how we can achieve that.

""" Reading the CSV File and loading the data in a database table """
# Importing Library
import pandas as pd

# Reading data into a pandas dataframe
df = pd.read_csv('./spend data/Transactions Jul 1, 2022 - Jul 31, 2022.csv')

# Removing white spaces from column names
df.columns = [x.strip() for x in df.columns]

# Removing white spaces from columns
for col in df:
    try:
        df[col] = df[col].map(str.strip)
    except:
        print(f'Excluding column: {col}')

# Exporting data to SQLite table
df.to_sql('finance_data', connection, if_exists='replace', index=False)

I am using Pandas for reading the CSV file and loading that to the SQLite table. Please note, I had to do some housecleaning since some of my columns have leading whitespace in them.

---

Reading the data

Let’s quickly read the data.

>>> result = connection.execute('SELECT * FROM finance_data')

>>> result.fetchone()
('18/07/2022', 'Travel (shared)', -13.88, 'Uber', 'CAD Account')

>>> result.fetchone()
('18/07/2022', 'General (shared)', -100.0, 'Marriage', 'CAD Account')

>>> result.fetchmany(3)
[('16/07/2022', 'Utilities', -22.59, 'Belt', 'CAD Account'), 
('16/07/2022', 'Eating Out (shared)', -36.61, '', 'CAD Account'), 
('16/07/2022', 'Eating Out (shared)', -8.97, 'Ice cream', 'CAD Account')]

>>> result.fetchall()
Outputs all the data

Note that at every call of “fetchone()”, python outputs a different result. The cursor moves to the next row after every call. Due to this, once you call “fetchall()”, your result will be empty. This is counter intuitive to python.

---

Saving your database

SQLite offers a handy functionality of rolling back to the previous commit. All the changes which we have done above are temporary. In order to save our changes, we need to commit our connection by using COMMIT function. Alternatively, we can undo the changes and rollback to the last commit by using the ROLLBACK function.

---

Commiting the changes

connection.commit()

---

Closing the connection

To close the connection, we can simply use “.close()” function.

connection.close()

---

Final thoughts

Hopefully, the article gave you a quick explanation about how we can use SQLite to manage a local database. I am planning on using SQLite to manage a database for my finances, which I will later visualize using PowerBI or Tableau. SQLite a neat and handy tool for any of your data projects and something I definitely recommend learning. You do require SQL knowledge in order to get the best output from SQLite. I have written some blog posts on SQL as well, so feel free to give that a look. And as always, any kind of feedback is much appreciated.

Thank you.