Database Handling in PythonSQLite and Redis

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.

The 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):

import sqlite3
conn = sqlite3.connect('company.db')
curs = conn.cursor()
curs.execute('create table employee (name, age)')
curs.execute("insert into employee values ('Ali', 28)")
values = [('Brad',54), ('Ross', 34), ('Muhammad', 28), ('Bilal', 44)]
curs.executemany('insert into employee values(?,?)', values)
conn.commit()
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, 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:

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

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

In order 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:

So, 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 of it. Before proceeding, make sure you install the Redis Server on your computer.

1. Install 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: 

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 Redis Server

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

import redis
redis_host = 'localhost'
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:

user = {
    'ID': 1,
    'name': 'Kingsley Ubah',
    'email': '[email protected]',
    'role': 'Tech Writing',
}

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:

def add_to_db():
    try:
        r = redis.StrictRedis(host = redis_host, port = redis_port, decode_responses=True)
        r.hmset("newUserOne", user)
        msg = r.hgetall("newUserOne")
        print(msg)
    except Exception as e:
        print(f"Something went wrong {e}")
 
# Runs the function:
if __name__ == "__main__":
    add_to_db()

3. Start server and run code

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

redis-cli

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

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:

{
  'ID': '1', 
  'name': 'Kingsley Ubah', 
  'email': '[email protected]', 
  'role': 'Tech Writing', 
}

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!