Using SQLite with Python

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:
– [Certified Associate in Python Programming Certification](https://linuxacademy.com/cp/modules/view/id/470)
– [SQL Deep Dive](https://linuxacademy.com/cp/modules/view/id/407)

Learning Objectives

Successfully complete this lab by achieving the following learning objectives:

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

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.

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

# 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 populate the table we just created with the data provided:

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 complete the function read_data_from_db. Make the function return the results from the sql_query so the testing function can be run. There is also some code in the main function that prints the data so you can review:

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 a script to delete one of the Smith, Jackson entries:

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 typo in the due date column for the remaining entry.

Now let’s fix the due date typo:

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.

Using SQLite with Python

About this Hands-on Lab

Learning Objectives

Additional Resources

Logging In

What are Hands-on Labs

Newsletter