Unit 2.4b HACKS
import sqlite3
def create_connection(db_file):
conn = None
try:
conn = sqlite3.connect(db_file)
return conn
except Error as e:
print(e)
return conn
def create_table(conn, create_table_sql):
try:
c = conn.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
def main():
database = 'instance/books.db'
sql_create_books_table = """ CREATE TABLE IF NOT EXISTS books (
_id integer PRIMARY KEY,
_author text NOT NULL,
_country text NOT NULL,
_title text NOT NULL,
_pages integer NOT NULL,
_borrowed integer NOT NULL
); """
conn = create_connection(database)
# create table
if conn is not None:
# create books table
create_table(conn, sql_create_books_table)
else:
print("Error! cannot create the database connection.")
if __name__ == '__main__':
main()
def read():
database = 'instance/books.db'
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Execute a SELECT statement to retrieve data from a table
results = cursor.execute('SELECT * FROM books').fetchall()
# Print the results
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
# Close the cursor and connection objects
cursor.close()
conn.close()
read()
def create():
database = 'instance/books.db'
author = input("Enter author name:")
country = input("Enter country of origin:")
title = input("Enter book title")
pages = input("Enter number of pages")
borrowed = input("Enter if book is borrowed. 1 for yes, 0 for no.")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO books (_author, _country, _title, _pages, _borrowed) VALUES (?, ?, ?, ?, ?)", (author, country, title, pages, borrowed))
# borrowed represents whether the book is available or not avaiable at the library, whether it is checked out or not
# Commit the changes to the database
conn.commit()
print(f"A new book record {title} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
create()
read()
import pandas as pd
# reads the JSON file and converts it to a Pandas DataFrame
df = pd.read_json('files/books.json')
cols_to_print = [ 'author', 'country', 'title', 'pages' ]
df = df[cols_to_print]
rows_to_print = [0,1,2,3,4,5, 6, 7, 8]
df = df.iloc[rows_to_print]
def remove_unknown(df, col):
x = df[col] == "Unknown"
df = df.drop(df[x].index)
return df
df = remove_unknown(df, "author")
print(df)
import sqlite3
# populating table with sample data
def populate():
database = 'instance/books.db'
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO books (_author, _country, _title, _pages, _borrowed) VALUES (?, ?, ?, ?, ?)", ('Chinua Achebe' , 'Nigeria', 'Things Fall Apart', 209, 0))
cursor.execute("INSERT INTO books (_author, _country, _title, _pages, _borrowed) VALUES (?, ?, ?, ?, ?)", ('Hans Christian Andersen' , 'Denmark', 'Fairy Tales', 784, 0))
cursor.execute("INSERT INTO books (_author, _country, _title, _pages, _borrowed) VALUES (?, ?, ?, ?, ?)", ( 'Dante Alighieri' , 'Italy', 'The Divine Comedy' , 928, 1))
cursor.execute("INSERT INTO books (_author, _country, _title, _pages, _borrowed) VALUES (?, ?, ?, ?, ?)", ( 'Jane Austen', 'United Kingdom', 'Pride and Prejudice', 226, 0))
cursor.execute("INSERT INTO books (_author, _country, _title, _pages, _borrowed) VALUES (?, ?, ?, ?, ?)", (' Honoré de Balzac', 'France', 'Le Père Goriot' , 443, 1))
# Commit the changes to the database
conn.commit()
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
populate()
import sqlite3 # i am not sure why but here it made me redfine the read function
def read():
database = 'instance/books.db'
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL queries
cursor = conn.cursor()
# Execute a SELECT statement to retrieve data from a table
results = cursor.execute('SELECT * FROM books').fetchall()
# Print the results
if len(results) == 0:
print("Table is empty")
else:
for row in results:
print(row)
# Close the cursor and connection objects
cursor.close()
conn.close()
read()
import sqlite3
def delete():
database = 'instance/books.db'
id = input("Enter a book id to delete")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
cursor.execute("DELETE FROM books WHERE _id = ?", (id,))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No uid {id} was not found in the table")
else:
# The uid was found in the table and the row was deleted
print(f"The row with uid {id} was successfully deleted")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the DELETE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
print("----Original Table:----")
read()
print("")
delete()
read()
import sqlite3
def update():
database = 'instance/books.db'
id = input("Enter a book id to update book's borrow status:")
borrowed = input("Enter updated borrow status (1 for borrowed, 0 for returned):")
if borrowed == 1:
status = "borrowed"
else:
status = "returned"
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to update data in a table
cursor.execute("UPDATE books SET _borrowed = ? WHERE _id = ?", (borrowed, id))
if cursor.rowcount == 0:
# The uid was not found in the table
print(f"No id {id} was not found in the table")
else:
print(f"The book with id {id} has been updated to: {status}")
conn.commit()
except sqlite3.Error as error:
print("Error while executing the UPDATE:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
print('----Original Databse:----')
read()
print("")
print('----Updated Databse:----')
update()
read()
def create():
database = 'instance/books.db'
author = input("Enter author name:")
country = input("Enter country of origin:")
title = input("Enter book title")
pages = input("Enter number of pages")
borrowed = input("Enter if book is borrowed. 1 for yes, 0 for no.")
# Connect to the database file
conn = sqlite3.connect(database)
# Create a cursor object to execute SQL commands
cursor = conn.cursor()
try:
# Execute an SQL command to insert data into a table
cursor.execute("INSERT INTO books (_author, _country, _title, _pages, _borrowed) VALUES (?, ?, ?, ?, ?)", (author, country, title, pages, borrowed))
# borrowed represents whether the book is available or not avaiable at the library, whether it is checked out or not
# Commit the changes to the database
conn.commit()
print(f"A new book record {title} has been created")
except sqlite3.Error as error:
print("Error while executing the INSERT:", error)
# Close the cursor and connection objects
cursor.close()
conn.close()
def menu():
operation = input("Enter: (C)reate (R)ead (U)pdate or (D)elete")
if operation.lower() == 'c':
create()
elif operation.lower() == 'r':
read()
elif operation.lower() == 'u':
update()
elif operation.lower() == 'd':
delete()
elif len(operation)==0: # Escape Key
return
else:
print("Please enter c, r, u, or d")
menu() # recursion, repeat menu
try:
menu() # start menu
except:
print("Perform Jupyter 'Run All' prior to starting menu")