Using SQLite with Python

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.

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.

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.