SQLite3 in Python

SQLite is the world’s most-used database and allows you to create an SQL-based database which can be integrated into all sorts of different applications. Python also supports SQLite3 by way of a module.

Using SQLite3 in Python

You can connect SQLite to Python by using a module. To make your work more efficient with the database system in Python, we recommend you have some background knowledge of both. This makes the setup and, later on, use of the database much easier.

How to set up a database – step by step

You can set up your own SQLite3 database in Python in a couple of steps. Once you’ve finished setting it up, you will be able to use the database using standard SQL commands.

Step 1: Import SQLite3 into Python

So that you have access to the functions that are specific to SQLite3 in Python you will need to import the corresponding module first:

import sqlite3

Step 2: Create the database

The next step is to create your own database by using the SQLite function “connect()”. It will give you a connection object, from which you can connect to your database. To open connect and create a connection to the database “test”, you will need the following:

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

The transfer parameter of the function is a database file. If you still haven’t created a database file called “test.db” this will be done automatically by using the connect function.

Step 3: Check whether the database was successfully created

At this point you can check whether your SQLite3 database has been successfully created. To do so you can call up the “total_changes” function on the connection object you just created. It will show you how many table rows in your relational database have been changed since you connected to the database.

print(connection.total_changes)

In this case the value would be “0” since we’ve not yet used any SQL commands and, therefore, have an empty database. In case you’ve already got data in your database, the value you see may be different.

Step 4: Create the basic framework of your database

To add data to your SQLite3 database in Python, you will now need to create a table to store your data, as is the norm with relational databases.

To do this you will first need to create a cursor object with the pre-defined “cursor” function using SQLite3 in Python. This will allow you to send SQL commands to your database. The code you need to do this looks as follows:

cursor = connection.cursor()

You can then use the “execute” function from the SQLite3 module to send SQL commands to your database. The function takes your commands, which follow standard SQL syntax, as strings. So, if you want to create a database with the name “example” and the columns “ID”, “name”, and “age”, then the code you use in Python will look as follows:

cursor.execute("CREATE TABLE IF NOT EXISTS example (id INTEGER, name TEXT, age INTEGER)")

Step 5: Add data

You will need the same syntax as you used to create a table to add data to a table:

cursor.execute("INSERT INTO example VALUES (1, alice, 20)")
cursor.execute("INSERT INTO example VALUES (2, 'bob', 30)")
cursor.execute("INSERT INTO example VALUES (3, 'eve', 40)")

Using the code above you will have added three entries to your database table “example”.

To save the changes to your database you need to use the “commit” function.

connection.commit()

Read the data

Of course, you can also read and output data from your databases instead of just using SQLite3 in Python to create them. To do this, you need to first connect to the database. From there you can create a connection and cursor object as explained in the step-by-step guide above. Finally you can formulate your SQL request, send it to the database using the “execute” function and use the “fetch_all” function to display the results:

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

The “fetch_all” function will give you a list of rows which correspond to your request. To output these rows on the console, you can use a Python for loop alongside a print statement.

Modify existing data

Since SQLite3 supports SQL commands, you can also modify or delete data within your database. To do so you just need to use the corresponding SQL command in your database. To start, this step also requires you to create a connection to your database followed by a connection and cursor object.

Delete data

To delete the row with the ID 1, you can use the following code:

cursor.execute("DELETE FROM example WHERE id = 1")

Change data

You can use the following command to change the age in the row with ID 2:

cursor.execute("UPDATE example SET age = 31 WHERE id = 2")

Use a place holder

If you want to use dynamic SQL requests in your Python program, you should never use Python string operations. Hackers can use them to attack your program with SQL injections. Instead you should use the SQL place holder “?” which you can use with the SQL request you sent using “execute” instead of a specific value. You can also enter a second parameter as a Python tuple to the “execute” function, which will be replaced by the question mark.

The following requests are the same:

# Direct SQL requests
cursor.execute("UPDATE example SET age = 31 WHERE id = 2")
# SQL requests with place holders
age_var = 31
id_var = 2
cursor.execute("UPDATE example SET age = ? WHERE id = ?", (age_var, id_var))

Be aware that the question marks in the SQL request will be replaced in the order in which you set the variables in the tuple.

End the connection to your database

After you have carried out all your tasks in your database, you will now need to end your connection. You can again use the SQLite3 module in Python to do so by opening it directly in your connection object:

connection.close()