How to Access SQLite from Python

Python is a popular and robust programming language rich in features that make it usable in a wide range of cases like data science, networking, IT automation, penetration testing, and many more. It also has a simple syntax that allows developers who know other programming languages to adapt to using Python with ease.Data is everywhere, and a wide range of software applications interact with data using a Database Management System. SQLite is one of the most popular database management systems in Python.

SQLite is a simple, powerful, open-source, and relational database engine that supports large software applications and embedded systems. SQLite is standalone and requires minimal configuration, making it extra easy to set up and run with minimal time. By default, Python comes built-in with an SQLite module (sqlite3), a very intuitive module for working with SQLite databases in Python.

In this tutorial, we will look at how to use Python to work with SQLite databases. From establishing a connection to creating databases, reading databases, updating, and removing databases.

Let us start by installing SQLite:

Installing SQLite and Python

Depending on your Linux distribution, you can download the SQLite archive from https://www.sqlite.org/download.html or use the package manager.

To install it on Debian:

sudo apt-get update
sudo apt-get install sqlite -y

Next, you should have the latest version of Python3 installed. Python should already be pre-installed in your distribution by default.

SQLite Shell

The default method to interact with SQLite databases is to use the shell. The shell allows you to execute SQL commands inline or a collection to perform functions on databases.

To launch the SQLite shell, use the command:

$ sqlite

SQLite version 2.8.17 Enter “.help” for instructions

sqlite>

This should launch the SQLite shell with a prompt allowing you to enter commands. Start by typing the command .help to view the shell help.

sqlite> .help
.databases List names and files of attached databases
.dump ?TABLE?… Dump the database in a text format
.echo ON|OFF Turn command echo on or off
.exit Exit this program
.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.
.header(s) ON|OFF Turn display of headers on or off
.help Show this message
.indices TABLE Show names of all indices on TABLE
.mode MODE Set mode to one of "line(s)", "column(s)",
"insert", "list", or "html"
----------------------------------------------------------------------

To exit from the SQLite shell, use the .quit command.

sqlite> .quit

There are other functions and operations you can use inside the SQLite shell. For example, to view all the databases, you can use the .database command.

I highly recommend that you experiment with the SQLite shell and get familiar as it will allow you to understand how to use the SQLite3 module in Python.

Connecting to a Database

Let us now use Python and SQLite3 modules to interact with SQLite databases. It is good to note that there are other Python modules you can use to interact with SQLite. However, SQLite3 is simple and comes bundled with Python.

Consider the script below to connect to the SQLite database.

We start by importing the SQLite and Error modules.
In line 3, we create a connect_db() function that takes the database's path as the argument.
The next part includes a try/error block. The first part takes the database path as the argument and establishes a connection. Note, in SQLite, if the database specified does not exist, it gets created automatically.
The error block tries to catch exceptions and prints them to the user.
In the final line, we call the connect_db function and pass the path to the database we want to use or create.

NOTE: If you want to create a memory database instead of a disk, you can specify :memory in the connect obj.

sqlite3.connect(“:memory”)

SQLite Create Table

In SQLite, we can use the SQL shell to create tables using the CREATE TABLE Query. The general syntax is as:

CREATE TABLE database_name.table_name (
column_name datatype PRIMARY KEY(column(s),
column2_name datatype,
… columnN_name datatype,
);

I won't dive into creating tables using SQLite shell as our primary focus is Python. Consider the SQL Lite documentation from the resource below to learn more. Now, to use the Python and sqlite3 modules to create database tables, we need to use the cursor object and execute SQL queries' functions. Consider the code below:

import sqlite3 from sqlite3 import Error
def connect_db(db_path):
connection = None
try:
connection = sqlite3.connect(db_path)
print("Database connected successfully")
except Error as e:
print(f"An Error has occured: e")
return connection def run_query(connection, sql_query):
cursor = connection.cursor()
try:
cursor.execute(sql_query)
connection.commit()
print("SQL Query Run Successfully… [OK]")
except Error as e:
print(f" Query Failed… e")
query = """
CREATE TABLE IF NOT EXISTS shows (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
year INTGER,
genre TEXT,
country TEXT
);
"""
run_query(connection=connect_db("/home/user/Desktop/sql.sqlite"), sql_query=query)

Let us now discuss what the code above does-find the first function explained above (please refer). In the second function, create, we pass the connection and the query to execute as the parameters. The following lines create a cursor object that we will use to call the execute method. As mentioned above, the next lines call the cursor's object to execute the method and call pass the query as the parameter. The block also prints a message on successful Query execution. Once the query executes successfully, we tell SQLite to use the commit method to save the changes to the database. The except block catches exceptions and prints the error message to the user. Finally, we create the Query to execute using simple SQLite syntax.

SQLite Insert Records

To add data to the SQLite database, we can dive into the run_query() function we used to create as it can run any SQLite query we pass to it. However, we use the INSERT INTO query to add data to the table.

Consider the block below:

add_shows = """
INSERT INTO
shows (id, name, year, genre, country)
VALUES
("101", "Brooklyn Nine-Nine", "2013", "comedy", "USA"),
("201", "Star-Trek: Discovery", "2017", "Sci-Fi", "USA"),
("301", "Star-Trek: Picard", "2020", "Sci-Fi", "USA");
""" run_query(connection=connect_db("/home/user/Desktop/sql.sqlite"), sql_query=add_shows)

We now need to call the run_query function and add the pass the query add_shows to insert data to the shows table. Ensure that the table you are inserting data into exists to avoid getting an error.

SQLite Delete Records

You can also use the run_query() function to remove records from the specified table. All you need is to set the query as the DELETE FROM.

Consider the following sub query:

remove = "DELETE FROM shows WHERE name = 'Brooklyn Nine-Nine'" run_query(connection=connect_db("/home/user/Deskop/sql.sqlite"), sql_query=remove)

The above query removes the show “Brooklyn Nine-Nine” from the shows table.

Conclusion

This tutorial has taught you how to use Python to access and interact with SQLite databases. From what you have learned from this tutorial, you can now create functions, connect to SQLite databases, create tables, insert data, and delete them. Although this is a starter guide to working with SQLite in Python, it should get you started learning other technologies such as SQLAlchemy and such.