Advertisement
  1. Code
  2. Coding Fundamentals
  3. Databases & SQL

Database Handling in Python: SQLite and Redis

Scroll to top

In the information age we are living in, we can see how much data the world is exchanging. We are basically creating, storing, and retrieving data, extensively! There should be a way to handle all that—it couldn't be spread everywhere without any management, right? Here comes the Database Management System (DBMS).

The DBMS is a software system that enables you to create, store, modify, retrieve, and otherwise handle data from a database. Such systems also vary in size, ranging from small systems that simply run on your personal computer to larger ones running on mainframes.

Our focus in this tutorial is on Python rather than database design. Yes, Python is wonderfully able to interact with databases, and this is what I'm going to show you in this tutorial. You'll learn how to use Python to handle both SQLite and Redis databases.

Let's get started!

Python Database API

As mentioned above, Python is able to interact with databases. But, how can it do that? Python uses what's called the Python Database API in order to interface with databases. This API allows us to program different database management systems (DBMS). For those different DBMS, however, the process followed on the code level is the same, which is as follows:

  1. Establish a connection to your database of choice.
  2. Create a cursor to communicate with the data.
  3. Manipulate the data using SQL (interact).
  4. Tell the connection to either apply the SQL manipulations to the data and make them permanent (commit), or tell it to abort those manipulations (rollback), thus returning the data to the state before the interactions occurred.
  5. Close the connection to the database.

SQLite

SQLite is an open-source, full-featured, self-contained (requires little support from external libraries), serverless (does not require a server to run the database engine on, and is a locally stored database), zero-configuration (nothing to install nor configure), SQL-based lightweight database management system (SQL queries can be run on SQLite tables), and uses one data file in order to store data.

A nice thing to know is that SQLite is used by large companies like Google, Apple, Microsoft, etc., which makes it very reliable. In this tutorial, we are going to use SQLite to interact with the database, and more specifically will be working with the sqlite3 module in Python.

Python and SQLite

As mentioned above, working with databases involves five main steps. Let's see those steps in action.

1. Establish a Connection to Your Database of Choice

This step is achieved as follows:

conn = sqlite3.connect('company.db')

As mentioned in the sqlite3 documentation:

To use the module, you must first create a Connection object that represents the database.

In the above code, notice that the data will be stored in the file company.db.

2. Create a Cursor to Communicate With the Data

The next step in working with the database is creating a cursor, as follows:

curs = conn.cursor()

3. Manipulate the Data Using SQL

After connecting with the database and creating a cursor, we are now ready to work (interact) with data. In other words, we can now run SQL commands on the database company.db.

Let's say we want to create a new table employee in our database company. In this case, we need to run a SQL command. In order to do that, we will use the execute() method of the sqlite3 module. The Python statement will thus look as follows:

curs.execute('create table employee(name, age)')

This statement will run a SQL command that will create a table called employee, with two columns (fields) name and age.

We can now run a new SQL command that will insert data in the table, as follows:

curs.execute("insert into employee values ('Ali', 28)")

You can also insert multiple values at once, as follows:

values = [('Brad',54), ('Ross', 34), ('Muhammad', 28), ('Bilal', 44)]

In this case, rather than using the method execute(), we will use the method executemany() to execute the above multiple values.

curs.executemany('insert into employee values(?,?)', values)

4. Commit the Changes

In this step, we would like to apply (commit) the changes we have made in the previous step. This is simply done as follows:

conn.commit()

5. Close the Connection to the Database

After performing our manipulations and committing the changes, the last step will be to close the connection:

conn.close()

Let's put all the steps together in one script. The program will look as follows (notice that we have to import the sqlite3 module first):

1
import sqlite3
2
conn = sqlite3.connect('company.db')
3
curs = conn.cursor()
4
curs.execute('create table employee (name, age)')
5
curs.execute("insert into employee values ('Ali', 28)")
6
values = [('Brad',54), ('Ross', 34), ('Muhammad', 28), ('Bilal', 44)]
7
curs.executemany('insert into employee values(?,?)', values)
8
conn.commit()
9
conn.close()

If you run the script, you should get a file called company.db in your current directory. Download this file as we will use it in the next step.

6. Let's Browse the Database

Having created a database and a table and added some data, let's see what's inside company.db (the file you downloaded in the above section). For this, we are going to use a nice tool: DB Browser for SQLite. Go ahead and download the tool on your machine. Once you open the program, you should get a screen that looks as follows:

DB Browser for SQLite screenDB Browser for SQLite screenDB Browser for SQLite screen

Open the database using the Open Database button at the top, in which case you should get the Database Structure, as follows:

Database StructureDatabase StructureDatabase Structure

Notice that we have the table employee listed, with two fields: name and age.

To confirm that our code above worked and the data has been added to the table, click on the Browse Data tab. You should see something like the following:

Browse Data tabBrowse Data tabBrowse Data tab

As you can see, a database (company) and a table (employee) have been created, and data has been successfully added to the table.

Redis

Remote Dictionary Server, known briefly as Redis, is a robust NoSQL database which is also capable of serving as an in-memory cache. Redis was developed by Salvatore Sanfilippo and is currently maintained by Redis Labs. The software is written using the C programming language and is open source (BSD license).

One of the most notable features of Redis is its data structure storage mechanism. You can store data in Redis using the same data types found in Python (strings, sets, integers, lists, dictionaries, and so on). This makes Redis a popular choice for Python developers. 

Redis and Python

Other than its data structure storage mechanism, Python developers also prefer Redis to other NoSQL databases due to its vast collection of Python clients, with perhaps the most popular choice being redis-py. Redis-py provides built-in commands for storing various kinds of data in a Redis server.

Now that we have a basic understanding of Redis, let's learn how to store data inside it. Before proceeding, make sure you install the Redis Server on your computer.

1. Install the Redis.py Client

First, create a new folder named redis-python for your Python script. Next, cd to the folder using your command terminal, and then run the following command to install the Redis client: 

1
pip install redis

Afterward, create a file in redis-python named app.py and open the file with a text editor. The next step is to create the Python script for adding data to the database.

2. Connect the Redis Client to the Redis Server

First, in app.py, import redis and set variables for the Redis server host and port address:

1
import redis
2
redis_host = 'localhost'
3
redis_port = 6379

Next, define the data that you want to add to the database. In this case, we're creating a simple Python dictionary:

1
user = {
2
    'ID': 1,
3
    'name': 'Kingsley Ubah',
4
    'email': 'ubahthebuilder@gmail.com',
5
    'role': 'Tech Writing',
6
}

Next, define an add_to_db function with a try...except. In the try block, we connect to our local Redis server and store the above dictionary in the database, before outputting the values on the console. If the code fails, we print the error object in the except block. The last part will run the function:

1
def add_to_db():
2
    try:
3
        r = redis.StrictRedis(host = redis_host, port = redis_port, decode_responses=True)
4
        r.hmset("newUserOne", user)
5
        msg = r.hgetall("newUserOne")
6
        print(msg)
7
    except Exception as e:
8
        print(f"Something went wrong {e}")
9
 
10
# Runs the function:

11
if __name__ == "__main__":
12
    add_to_db()

3. Start the Server and Run the Code

First, run your Redis server by executing the following command on the command terminal:

1
redis-cli

Once the server is active, you can run the Python script with the following command:

1
python app.py

If all goes well, the dictionary containing the user profile will be added to Redis with the newUserOne key. In addition, you should see the following output on your terminal's console:

1
{
2
  'ID': '1', 
3
  'name': 'Kingsley Ubah', 
4
  'email': 'ubahthebuilder@gmail.com', 
5
  'role': 'Tech Writing', 
6
}

That's it for Redis!

Conclusion

This tutorial just scratched the surface of working with databases using Python. You can learn about more methods from the sqlite3 module, where you will be able to carry out different database operations such as updating and querying the database.

To learn more about redis-py, do not hesitate to read their complete documentation. Have fun!

Advertisement
Did you find this post useful?
Want a weekly email summary?
Subscribe below and we’ll send you a weekly email summary of all new Code tutorials. Never miss out on learning about the next big thing.
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.