SQLite3 and Python

July 21, 2023 · 5 minute read

Introduction

SQLite is a lightweight, serverless, and self-contained database management system. SQLite is designed to be lightweight, making it a popular choice where a full-fledged database server is not necessary.

Manually creating SQL queries and entering data can be burdensome. However this process can be automated using the python module sqlite3. This module is a native Python module that allows us to connect to, and manage databases.

Step One: Establishing a Connection to the SQLite Database

We can connect to the SQLite database using the connect() method from the sqlite3 module. When using this method it attempts to connect to a databse in the directory where your program resides. Furthermore, if the connect method cannot find the database file, it will create a new database with the name you specified.

Below is an example code snippet of connecting to a database named: testdb


import sqlite3

connection = sqlite3.connect("testdb.db")

After we have connected to the database, we can create a cursor object to send SQL statements to the SQLite database. This can be done with the code snippet:


cursor = connection.cursor()

NOTE: Remember to close the database connection using close() as well as commit() before closing to avoid losing changes.

Step Two: Adding Data to the database

Adding data to the SQLite database is as simple and straightforward as executing regular SQL commands. You can use CREATE TABLE to create a table and, INSERT INTO to add values to a table. Some examples can be seen below.

Syntax for Creating a Table


CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
    ....
);


cursor = connection.cursor()
cursor.execute("CREATE TABLE TACOBELLMENU (ItemName varchar(255), ItemID int, Price float)")

Syntax for Adding Data

When using INSERT INTO or UPDATE statements, it's important to remember to use the commit() method to save changes to your database.

Note that if you don't specify columns when using an INSERT INTO statement, then the values correspond to the index of the columns. For example the first parameter will be applied to the first column and so on.


INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);


cursor = connection.cursor()
cursor.execute("INSERT INTO TACOBELLMENU ('Burrito', 32, 7.92)")

connection.commit()
connection.close()

In practical scenarios, you will never be manually specificying data, instead you should be utilizing variables to specify the data to be inserted. This allows you to easily adapt your code and add diverse values during runtime.

Parameterized queries are utilized in SQL statements that decouple SQL code from the actual data, reducing the risk of SQL injection and upholding data integrity. In sqlite3 the question mark ? is used as a placeholder. A tuple is then passed into the method which inserts the data using the variables.

Below is an example:


cursor = connection.cursor()

name = "Chalupa"
itemID = 87
price = 3.872

cursor.execute("INSERT INTO TACOBELLMENU VALUES(?, ?, ?)", name, itemID, price)
connection.commit()
connection.close()

In the case that you need to insert multiple items:


cursor = connection.cursor()

data = [{"name": "Chalupa", "itemID": 87, "price": 3.87},
        {"name": "Cheesy Burrito", "itemID": 17, "price": 7.87},
        {"name": "Refried Beans", "itemID": 89, "price": 10.11}]

cursor.execute("INSERT INTO TACOBELLMENU VALUES(:name, :itemID, :price)", data)
connection.commit()
connection.close()

However the example above sets static values in the dictionary and again, in most cases you will want to use variables when inserting data. This can be done simply by inserting variables into the value pairs of the dictionary.

Step Three: Reading Data from the Database

Reading SQLite data using sqlite3 is straightforward and can be done by constructing a SELECT statement in SQL. We can do this by using the execute() method along with the appropriate SQL query. Once complete, you can either use fetchone() or fetchall() to access the data.

Below is an example of how to read data from the database:


cursor = connection.cursor()

data = cursor.execute("SELECT * FROM TACOBELLMENU").fetchall()
for row in data:
    print(row)

connection.close()

In conclusion, sqlite3 is a robust, native Python module that provides a lightweight and self-contained interface for working with databases. Key features of this module include connecting to the database, executing and fetching SQL statements and results, transaction methods such as commit, as well as parameter binding to prevent SQL injection vulnerabilities.