Skip to main content

Using SQLite with Python

Hands-On Lab

 

Photo of Larry Fritts

Larry Fritts

Python Development Training Architect II

Length

00:30:00

Difficulty

Beginner

In this lab, you will learn to perform CRUD processes on a SQLite database. You will first create a data table and then add data to it. You will practice reading data from the table, and need to make corrections to the data.

The skills you practice and learn in this lab will be applicable to most other major databases. You will be able to reuse your code here by just replacing the database engine connection with the one you are using.

You will need basic Python programming and SQL skills for this lab:

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Using SQLite with Python

Introduction

In this lab, you will learn to perform CRUD processes on a SQLite database. You will first create a data table and then add data to it. You will practice reading data from the table, and need to make corrections to the data.

The skills you practice and learn in this lab will be applicable to most other major databases. You will be able to reuse your code here by just replacing the database engine connection with the one you are using.

You will need basic Python programming and SQL skills for this lab:

The Scenario

Atlantic Publishing is a startup publishing house with the motto "We print what others won't!" They started keeping written lists that summarized the author contracts. Finally realizing they need to have a more robust method, they decided to move to a database. The first step they took was to create a Python list of their written summarized data. But they were unsure how to proceed past that point. So they have hired you to create and maintain their database, as well as provide them needed data on the spot. They have left the choice of the database up to you.

Logging In

There are a couple of ways to get in and work with the code. One is to use the credentials provided in the hands-on lab page, log in with SSH, and use a text editor in the terminal.

The other is using VS Code in the browser. If you'd like to go this route, then you will need to navigate to the public IP address of the workstation server (provided in the hands-on lab page) on port 8080 (example: http://PUBLIC_IP:8080).

Whether you use a command line text editor or VS Code, the files we'll be working on are all sitting in /home/cloud_user.

Create a Data Table in SQLite

One of the things we will have to do over and over is connect to the database. So first, we will write a function that we can then import into each of our separate tasks. The file we will use is connect_db.py. The function should be called get_db_connection, and should return a connection and cursor object when called. connect_db.py should look like this when we're finished:

# connect_db.py

import sqlite3

DB_NAME = "author_contracts.db"

def get_database_connection():
    con = sqlite3.connect(DB_NAME)

    return con

Now we need to create the table and populate it. The file create_table.py has a skeleton for this work. There are two tests in this file. These tests check that the database table exists, and that the table has six rows of data.

Python Version

Before we can run create_table.py, let's make sure we're using the right version of Python:

python -V

We should be at 3.8.2. If we're not, run this to set it:

pyenv shell 3.8.2

create_table.py contains the data and SQL statements needed. You supply the code to process the SQL statement with Python and SQLite. To begin with, run python create_table.py. The main function is provided for you. This should return an assert error with the statement 'table does not exist.'.

Let's create the table by adding some code to actually connect to the database (below the create_table declaration):

# create_table.py

def create_table():
    """
    Creates a table ready to accept our data.

    write code that will execute the given sql statement
    on the database
    """

    create_table = """ CREATE TABLE authors(
        ID          INTEGER PRIMARY KEY     AUTOINCREMENT,
        author      TEXT                NOT NULL,
        title       TEXT                NOT NULL,
        pages       INTEGER             NOT NULL,
        due_date    CHAR(15)            NOT NULL
    )   
    """

    con = get_database_connection()
    con.execute(create_table)
    con.close()

Now that we have written the code, let's check our test by running python create_table.py. You should not see the error 'table does not exist.', but instead should see 'The table does not have six rows.'. This means that the table was created and only needs to be populated.

Now let's move a bit farther down the script and populate the table we just created with the data provided (the last four lines here):

def populate_table():

    add_data_stmt = ''' INSERT INTO authors(author,title,pages,due_date) VALUES(?,?,?,?); '''

    con = get_database_connection()
    con.executemany(add_data_stmt, contract_list)
    con.commit()
    con.close()

Test the code with python create_table.py. All tests should pass.

Congratulations! You have created the table and populated it. You are now ready to process requests by the Atlantic Publishing staff.

Read from the Data Table

The Contracts department has asked for a list of all upcoming books, showing the author, the title, and the due date.

The file read_data.py has the skeleton for you. Again, run python read_data.py and you should see an error with the message the results do not match the expected.

Let's open read_data.py and complete the function read_data_from_db. Make the function return the results from the sql_query so the testing function can be run. The lines after sql_query is declared are what we need to add:

def read_data_from_db():
    """
    Return data from database.
    """

    sql_query = ''' SELECT author,title,due_date FROM authors; '''

    con = get_database_connection()
    cur = con.cursor()

    cur.execute(sql_query)
    results = cur.fetchall()

    cur.close()
    con.close()  

    return results

Again, run python read_data.py and you should not see an error, but should see the data that will be passed to the Contracts department.

Congratulations! The Contracts department was thoroughly impressed with your ability to deliver the data.

Update and Delete Rows

The Contracts department sent back the data with the following notes:

  • "Smith, Jackson" is duplicated and neither is correct, the due date is "2020-10-31" and pages are 600.

The file update_data.py has the skeleton for you. Again, run python update_data.py and you should see an error with the message the number of Smith Jackson rows is incorrect.

Let's complete the function delete_data_from_db. Write code that will delete one of the Smith, Jackson entries, below the sql_query declaration:

def delete_data_from_db():
    """
    Delete selected data from database.

    execute the given sql statement to remove
    the extra data
    """

    sql_query = ''' DELETE FROM authors WHERE (author="Smith, Jackson" AND pages=400); '''

    con = get_database_connection()
    con.execute(sql_query)
    con.commit()
    con.close()    

Now that we have written the code, let's check our test by running python update_data.py. You should not see the error the number of Smith Jackson rows is incorrect, but instead should see due date not updated correctly. This means that you have deleted a duplicate row and now just need to fix the due date issue. We'll do it in much the same way as the last bit:

def update_data():
    sql_query = ''' UPDATE authors SET due_date="2020-10-31" WHERE author="Smith, Jackson"; '''

    con = get_database_connection()
    con.execute(sql_query)
    con.commit()
    con.close()

Again, run python update_data.py and you should not see an error, so you know the data was updated correctly.

Congratulations! You have shown that you can update data in the data table.

Conclusion

We've got some very happy people at Atlantic Publishing. Congratulations on completing this hands-on lab!