Creating a simple SQLite-based app with Python

Bartosz — Fri, 11 Jun 2021 19:00:24 +0000

Cover image by Jan Kahánek on Unsplash

Introduction

Most modern apps require some kind of data storage. For this purpose, relational database management systems (RDBMS) are commonly used - for example PostgreSQL, MySQL, Microsoft SQL Server, among others. However, it can be difficult for a beginner to understand them - that's when SQLite comes in.

In this article, I'm going to show you what SQLite is and how to use it with Python to make a simple database-driven app. It assumes that you already have some knowledge of Python (variables, if/else statements, loops). We are also going to use SQL - if you don't know anything about it, SQLBolt is a great place to start. You should have an idea on how to perform simple queries on tables.

About SQLite

SQLite is a relational database management system similar to these listed above, but it has a significant difference!

From Wikipedia:

In contrast to many other database management systems, SQLite is not a client–server database engine.

What it means is that there is no database server; all data is saved in a file. It is also possible to keep the database in your computer's RAM (useful for testing).

SQLite can be also used with web frameworks like Django (or with any programming language that has a SQLite driver).

About the app

The app will be a simple note list that demonstrates the use of SQLite. Nothing complicated, as we want to focus on the database side.

Software prerequisites

Python 3 (support for Python 2 has been already dropped)
plain text editor (I use VSCode)

You don't have to install anything related to SQLite - Python comes with built-in SQLite support in the sqlite3 package.

Let's go!

Creating the app

Initialization

Open your text editor and create a new Python script. I named mine notes.py.

As I said before, SQLite support is provided by sqlite3 package. Let's import it:

import sqlite3

Now we can create a database connection.

# open a SQLite connection
# a database file called data.db will be created,
# if it does not exist
connection = sqlite3.connect('data.db')

# close the connection
connection.close()

'data.db' can be anything (as long as it's a valid Python string); you can also use ':memory:' if you want to store the database in the RAM
note the connection.close() method; it must be called in order to free resources used by the database

Listing data in the database

In order to query the database, we must create a database cursor.
Then, we can use it to perform queries.

Add the following code between connection = sqlite3.connect('data.db') and connection.close():

# create a database cursor
cur = connection.cursor()

# query the database for ALL data in the notes table
cur.execute('SELECT * FROM notes;')

# print the result
result = cur.fetchall()
print(result)

# close the cursor
cur.close()

after creating the cursor, we use the execute method to make a query to the database
we assign the query result to a variable named result (fetchall returns a list of tuples) and print it out
again, when we don't need the cursor anymore, we close it to free used resources (although some people say it's not neccessary)

The full code looks like this:

import sqlite3


# open a SQLite connection
# a database file called data.db will be created,
# if it does not exist
connection = sqlite3.connect('data.db')

# create a database cursor
cur = connection.cursor()

# query the database for ALL data in the notes table
cur.execute('SELECT * FROM notes;')

# print the result
result = cur.fetchall()
print(result)

# close the cursor
cur.close()

# close the connection
connection.close()

"No such table" error

If you now try to execute this Python script, you should see an error:

$ python notes.py 
Traceback (most recent call last):
  File "/home/bartoszg/Dokumenty/code/console-sqlite/notes.py", line 12, in <module>
    cur.execute('SELECT * FROM notes;')
sqlite3.OperationalError: no such table: notes

This is absolutely normal - we were trying to query a fresh database with no data in it.

In general, data in a RDBMS is placed in tables (also called relations).

Source: https://dba.stackexchange.com/questions/187544/designing-a-database-structure-for-companies-and-stock-owners

There are three tables in the database above:

Companies
Company_ID
Owners

If you need, you can read more about tables at the SQLBolt website.

Creating a table

Let's describe our new table named notes

The table columns are:

ID - an integer which is also a primary key (that is, the ID is unique for all data rows)
name - text, must not be empty
description - text, may be empty

Note that SQLite has no VARCHAR type.

This is how the SQL for our table looks like:

CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);

Place it in our Python code, before the SELECT query.

# create the database table if it doesn't exist
table_schema = """
CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);
"""
cur.execute(table_schema)

The code should look like this (I've added some more comments for clarity):

import sqlite3


#
# Establishing connection
#

# open a SQLite connection
# a database file called data.db will be created,
# if it does not exist
connection = sqlite3.connect('data.db')

# create a database cursor
cur = connection.cursor()

#
# Creating the table
#

# create the database table if it doesn't exist
table_schema = """
CREATE TABLE IF NOT EXISTS notes (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT
);
"""
cur.execute(table_schema)

#
# Querying the database
#

# query the database for ALL data in the notes table
cur.execute('SELECT * FROM notes;')

# print the result
result = cur.fetchall()
print(result)

#
# Cleaning up
#

# close the cursor
cur.close()

# close the connection
connection.close()

Now, if you run this script:

$ python notes.py 
[]

As expected, there are no data rows in our table - but there is no error, since the table exists.

Inserting data

If we want to insert a new row to our database, we use a INSERT statement. For our database, the query would look like this:

INSERT INTO notes (name, description)
VALUES ('my first note', 'hi, this is the description');

In the first line, we said which columns we are inserting to, and in the second one we specified what data should be in these columns.

Note that we didn't include the id column. This way it'll be automatically set to a random value.

Now, for the Python code:

#
# Inserting to the database
#

# insert some hard-coded data
insert_query = """
INSERT INTO notes (name, description)
VALUES ('my first note', 'hi, this is the description');
"""
cur.execute(insert_query)

# save it in the database file
connection.commit()

Place this just before the Querying the database section.

Custom data, SQL injection

You may be tempted to replace 'my first note' and 'hi, this is the description' like this:

name = input('Note name')
desc = input('Note description')

# insert some hard-coded data
insert_query = """
INSERT INTO notes (name, description)
VALUES ('{}', '{}');
""".format(name, desc)
cur.execute(insert_query)

Hovewer, this means that your database would be prone to a SQL injection attack. Arbitrary SQL code could be executed, which could have terrible consequences.

Instead, we use SQL parameters to safely execute a SQL query containing user-provided data. Here is an example using a SELECT ... FROM ... WHERE query:

x = int(input())
y = input()

# the correct way
cur.execute('SELECT * FROM abc WHERE x = ? AND y = ?', (x, y))

# NEVER DO THIS
cur.execute(f'SELECT * FROM abc WHERE x = {x} AND y = "{y}"')

# NEVER DO THIS
cur.execute('SELECT * FROM abc WHERE x = {} AND y = "{}"'.format(x, y))

# NEVER DO THIS
cur.execute('SELECT * FROM abc WHERE x = %d AND y = "%s"' % (x, y))

That's how we would implement it in the app:

#
# Inserting to the database
#

name = input('Note name: ')
desc = input('Note description: ')

# insert some hard-coded data
insert_query = """
INSERT INTO notes (name, description)
VALUES (?, ?);
"""
cur.execute(insert_query, (name, desc))

# save it in the database file
connection.commit()

Now try running the script.

$ python notes.py 
Note name: hello
Note description: this is my first note
[(1, 'hello', 'this is my first note')]

It works! Try running it several times.

Improving the data display

Displaying data as a Python array isn't very elegant. Let's change it by modifying the Querying the database section.

#
# Querying the database
#

# query the database for ALL data in the notes table
cur.execute('SELECT * FROM notes;')

# print the result
print('\nNotes:')
for row in cur.fetchall():
    display_name = row[1]
    display_desc = row[2]

    print(f'Note name: {display_name}\nNote description: {display_desc}\n')

as we have seen before, cur.fetchall returns an array of tuples
row[1] is the 2nd column value (name), and row[2] is the 3rd column value; we don't need to display the 1st value (ID)
the \n character is an escape sequence; it places a new line at its position

Try running this script now. It looks so much better, doesn't it?

Conclusion

And that's all for the tutorial. Thanks for reading, I hope you've learned something new. In fact, this is my first article :D

We have merely scratched the surface; if you want to learn more about SQLite, check out SQLite Tutorial.

You can access the app's source code here.

Assignments

Do you want to exercise yourself?

add more table columns - creation date, category number etc
make the app more interactive (for example, ask the user if they want to read or write data in the database)
show only the last 5 notes (tip: use ORDER BY and LIMIT)

Feel free to ask if you have any questions!

Forem: Bartosz