def update_data():
sql_query = ''' UPDATE authors SET due_date="2020-10-31" WHERE author="Smith, John"; '''
con = get_database_connection()
con.execute(sql_query)
con.commit()
con.close()
def test_populate_data():
# Get db connection
db = get_database_connection()
contracts = db[COLLECTION]
count = 0
for contract in db[COLLECTION].find():
count = count + 1
assert count == 6, 'The table does not have six rows.'
def test_populate_table():
# Get connection and cursor to db
cxn = get_database_connection()
cur = cxn.cursor()
cur.execute("select * from authors;")
results = cur.fetchall()
assert len(results) == 6, 'The table does not have six rows.'
cur.close()
cxn.close()
def test_delete_data():
sql_query = ''' SELECT count(author) FROM authors WHERE author='Smith, Jackson'; '''
# make connection to db
db = get_database_connection()
contracts = db[COLLECTION]
results = list()
# find all documents with "Smith, Jackson" as the author
for contract in contracts.find({"author": "Smith, Jackson"}):
results.append(contract)
assert len(results) == 1, "the number of Smith Jackson rows is incorrect"
def test_update_data():
sql_query = ''' SELECT due_date FROM authors WHERE author='Smith, Jackson'; '''
# make connection to db
db = get_database_connection()
contracts = db[COLLECTION]
results = list()
# find all documents with "Smith, Jackson" as the author
for contract in contracts.find({"author": "Smith, Jackson"}):
results.append(contract)
assert results[0]["due_date"] == "2020-10-31", "due date not updated correctly"
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, John" AND pages=100); '''
con = get_database_connection()
con.execute(sql_query)
con.commit()
con.close()
def populate_table():
contract_list = [
["Amith, Aohn", "Life of John", 1200, "2029-11-15"],
["Zmith, Zohn", "Life of Smith", 1200, "2029-11-15"],
["Smith, John", "Of John Smith", 100, "2029-11-15"],
["Gmith, Gohn", "Of John", 1200, "2029-11-15"],
["Rmith, Rohn", "Of Smith", 1200, "2029-11-15"],
["Smith, John", "John Smith", 1200, "2029-11-15"],
]
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()
def test_update_data():
sql_query = ''' SELECT due_date FROM authors WHERE author='Smith, Jackson'; '''
# make connection to db
cxn = get_database_connection()
# create a cursor to db
cur = cxn.cursor()
# send sql query to request
cur.execute(sql_query)
results = cur.fetchall()
assert results[0][0] == "2020-10-31", "due date not updated correctly"
# close database connection.
cur.close()
cxn.close()
def test_delete_data():
sql_query = ''' SELECT count(author) FROM authors WHERE author='Smith, Jackson'; '''
# make connection to db
cxn = get_database_connection()
# create a cursor to db
cur = cxn.cursor()
# send sql query to request
cur.execute(sql_query)
results = cur.fetchall()
assert results[0][0] == 1, "the number of Smith Jackson rows is incorrect"
# close database connection.
cur.close()
cxn.close()
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()
print(results)
cur.close()
con.close()
return results
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()
def test_table_created():
"""
Test table was created.
"""
test_stmt = ''' SELECT count(ID) FROM authors; '''
# get connection and cursor to db
cxn = get_database_connection()
cur = cxn.cursor()
# send sql query to request
cur.execute(test_stmt)
cxn.commit()
assert cur.fetchone()[0] == 0, 'table does not exist.'
# close database connection.
cur.close()
cxn.close()
