In: Computer Science
6.23 LAB: Python insert/update sqlite3 datafiles
Given is a Python program that connects to a sqlite database and has one table called writers with two columnns:
The writers table originally has the following data:
name, num Jane Austen,6 Charles Dickens,20 Ernest Hemingway,9 Jack Kerouac,22 F. Scott Fitzgerald,8 Mary Shelley,7 Charlotte Bronte,5 Mark Twain,11 Agatha Christie,73 Ian Flemming,14 J.K. Rowling,14 Stephen King,54 Oscar Wilde,1
Update the Python program to ask the user if they want to update entries or add new entries. If the name entered already exists in the writers table then the database record is updated, overwriting the original contents. If the name does not exist in the writers table, then add a new record with the writer's name and number of works. The following TODO sections must be completed.
Ex: If the input is:
y J.K. Rowling 30 y Elton John y 62 n
The output is:
(ID, Name, Num) (1, 'Jane Austen', 6) (2, 'Charles Dickens', 20) (3, 'Ernest Hemingway', 9) (4, 'Jack Kerouac', 22) (5, 'F. Scott Fitzgerald', 8) (6, 'Mary Shelley', 7) (7, 'Charlotte Bronte', 5) (8, 'Mark Twain', 11) (9, 'Agatha Christie', 73) (10, 'Ian Flemming', 14) (11, 'J.K. Rowling', 30) (12, 'Stephen King', 54) (13, 'Oscar Wilde', 1) (14, 'Elton John', 62)
The following function does the above required tasks. The code is throroughly commented to explain the steps.
import sqlite3
#This function will do the required tasks upon being called
def process_database():
# Assuming the database file with given data is also named 'writers.db', otherwise change name below
# Connect to database named "writers.db"
connection = sqlite3.connect("writers.db")
# Create cursor to navigate
cursor = connection.cursor()
while True:
# Ask input from user
command = input("Do you want to update entries or add new entries? (Enter y or n)")
if command == 'n':
break
elif command == 'y':
name = input() # Get 'name' input
num = input() # Get 'num' input
exists = False # variable to store if inputted name already exists in database
# SELECT ALL ROWS FROM EXISTING DATABASE, and check to see if the input 'name' already exists
rows = cursor.execute("SELECT name, num from writers").fetchall()
for row in rows:
# if name exists, set var 'exists' to True
if row[0] == name:
exists = True
break
# Update record if it already exists
if exists:
cursor.execute("UPDATE writers set num=? where name=?", (num, name))
# Insert record if it doesn't already exist in the database
else:
insert_statement = "INSERT INTO writers VALUES (?, ?);"
cursor.execute(insert_statement, (name, num))
# Commit changes to database
connection.commit()
# Close db connection
connection.close()
Hope that helps!