Using SQLite with Python

30 minutes
  • 3 Learning Objectives

About this Hands-on Lab

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.

Additional Resources

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). Your password will be the same password that you'd use to connect over SSH.

What are Hands-on Labs

Hands-on Labs are real environments created by industry experts to help you learn. These environments help you gain knowledge and experience, practice without compromising your system, test without risk, destroy without fear, and let you learn from your mistakes. Hands-on Labs: practice your skills before delivering in the real world.

Get Started
Who’s going to be learning?

How many seats do you need?

  • $499 USD per seat per year
  • Billed Annually
  • Renews in 12 months

Ready to accelerate learning?

For over 25 licenses, a member of our sales team will walk you through a custom tailored solution for your business.


$2,495.00

Checkout
Sign In
Welcome Back!

Psst…this one if you’ve been moved to ACG!