Creating a simple SQLite-based app with Python
Tags: python, sqlite
Published: 2021-06-11 19:00:24

*Cover image by [Jan Kahánek](https://unsplash.com/@honza_kahanek?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText) on [Unsplash](https://unsplash.com/s/photos/notepad?utm_source=unsplash&utm_medium=referral&utm_content=creditCopyText)*

## 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](https://sqlbolt.com/) 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](https://en.wikipedia.org/wiki/SQLite):

> 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](https://en.wikipedia.org/wiki/Django_(web_framework)) (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:

```python
import sqlite3
```

Now we can create a database connection.

```python
# 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](https://en.wikipedia.org/wiki/Cursor_(databases)).
Then, we can use it to perform queries.

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

```python
# 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](https://stackoverflow.com/questions/2330344/in-python-with-sqlite-is-it-necessary-to-close-a-cursor))

The full code looks like this:

```python
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](https://en.wikipedia.org/wiki/Relation_(database))). 

![Example database tables](https://i.stack.imgur.com/ytHC3.png)

*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](https://sqlbolt.com/lesson/inserting_rows).

### Creating a table

Let's describe our new table named `notes`

The table columns are:
  * ID - an integer which is also a [primary key](https://www.tutorialspoint.com/sql/sql-primary-key.htm) (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](https://sqlite.org/datatype3.html).

This is how the SQL for our table looks like:

```sql
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.

```python
# 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):

```python
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:

```sql
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:

```python
#
# 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:

```python
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](https://en.wikipedia.org/wiki/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:

```python
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:

```python
#
# 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.

```python
#
# 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](https://www.sqlitetutorial.net/).

You can access the app's source code [here](https://gist.github.com/karmek-k/4aa51a461a80c34022feb1baffe2835a).

### 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](https://sqlbolt.com/lesson/filtering_sorting_query_results))

Feel free to ask if you have any questions!