Given is a Python program that connects to a sqlite database and has one table called writers with two columnns: name - the name of a writer num - the number of works the writer has written 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. Check if a writer exists in the writers table If the writer exists in the table, locate an entry to be updated by writer's name and update the writer's value for num If the writer does not exist in the table, add a new entry in the writers table and provide the value for name and num 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) import csv, sqlite3 from sqlite3 import Error # TODO: Implement this function # inserts new writer data as a row in the database. Insert both name and number of works. def insert_writer(con, data): # TODO: Implement this function # updates an existing writer record based on writer name def update_writer(con, writer): # TODO: Implement this function # Check if the writer's name exists in the database # Return the number of records found (should be 1 if writer exits, or 0 if the writer doesn't exist in table) def check_writer(con, name): # NOTE: Do not modify # Creates a sqlite3 database connection (in memory) def create_connection(): """ create a database connection to a SQLite database """ con = None try: con = sqlite3.connect(":memory:") return con except Error as e: print(e) return con # NOTE: Do not modify # Creates a table def create_table(con, create_table_sql): """ create a table from the create_table_sql statement :param cnn: Connection object :param create_table_sql: a CREATE TABLE statement :return: """ try: c = con.cursor() c.execute(create_table_sql) except Error as e: print(e) # NOTE: Do not modify # Prints all rows of data from the writers table def select_all_writers(con): """ Query all rows in the tasks table :param con: the Connection object :return: """ cursor = con.cursor() cursor.execute("SELECT * FROM writers") rows = cursor.fetchall() for row in rows: print(row) # NOTE: Do not modify # Initializes the database by adding all rows from the allWorks.csv file def initialize_database(con): cursor = con.cursor() ## create writers table create_table_sql = """ CREATE TABLE writers ( id integer PRIMARY KEY NOT NULL, name text, num integer ); """ create_table(con, create_table_sql) ## Read contents of CSV file with open('allWorks.csv', 'r') as fin: # `with` statement available in 2.5+ # csv.DictReader uses first line in file for column headings by default dr = csv.DictReader(fin) # comma is default delimiter to_db = [(i['name'], i['num']) for i in dr] ## Insert contents of CSV file into writers table cursor.executemany("INSERT INTO writers (name, num) VALUES (?, ?);", to_db) con.commit() if __name__ == '__main__': # TODO: Create a database connection con = create_connection() # Initialize database data initialize_database(con) print('Would you like to update a writer database record?') yesNo = input() if yesNo[0] == 'y' or yesNo[0] == 'Y': # Loop until user is done updating records while yesNo == 'y' or yesNo == 'Y': print('Which entry would you like to update?') name = input() # TODO Check if writer exists in database if ( ): print('Enter a new value for ' + name) num = input() # TODO Update writer's value for number of works else: print("Entry not found. Add new entry?") yesNo = input() if yesNo[0] == 'y' or yesNo[0] == 'Y': print('Number of works?') num = input() # TODO Insert new writer entry print('Would you like to continue to update writers') yesNo = input() print("(ID, Name, Num)") select_all_writers(con) con.close()
Given is a Python program that connects to a sqlite
- name - the name of a writer
- num - the number of works the writer has written
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.
- Check if a writer exists in the writers table
- If the writer exists in the table, locate an entry to be updated by writer's name and update the writer's value for num
- If the writer does not exist in the table, add a new entry in the writers table and provide the value for name and num
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)
import csv, sqlite3
from sqlite3 import Error
# TODO: Implement this function
# inserts new writer data as a row in the database. Insert both name and number of works.
def insert_writer(con, data):
# TODO: Implement this function
# updates an existing writer record based on writer name
def update_writer(con, writer):
# TODO: Implement this function
# Check if the writer's name exists in the database
# Return the number of records found (should be 1 if writer exits, or 0 if the writer doesn't exist in table)
def check_writer(con, name):
# NOTE: Do not modify
# Creates a sqlite3 database connection (in memory)
def create_connection():
""" create a database connection to a SQLite database """
con = None
try:
con = sqlite3.connect(":memory:")
return con
except Error as e:
print(e)
return con
# NOTE: Do not modify
# Creates a table
def create_table(con, create_table_sql):
""" create a table from the create_table_sql statement
:param cnn: Connection object
:param create_table_sql: a CREATE TABLE statement
:return:
"""
try:
c = con.cursor()
c.execute(create_table_sql)
except Error as e:
print(e)
# NOTE: Do not modify
# Prints all rows of data from the writers table
def select_all_writers(con):
"""
Query all rows in the tasks table
:param con: the Connection object
:return:
"""
cursor = con.cursor()
cursor.execute("SELECT * FROM writers")
rows = cursor.fetchall()
for row in rows:
print(row)
# NOTE: Do not modify
# Initializes the database by adding all rows from the allWorks.csv file
def initialize_database(con):
cursor = con.cursor()
## create writers table
create_table_sql = """ CREATE TABLE writers (
id integer PRIMARY KEY NOT NULL,
name text,
num integer
); """
create_table(con, create_table_sql)
## Read contents of CSV file
with open('allWorks.csv', 'r') as fin: # `with` statement available in 2.5+
# csv.DictReader uses first line in file for column headings by default
dr = csv.DictReader(fin) # comma is default delimiter
to_db = [(i['name'], i['num']) for i in dr]
## Insert contents of CSV file into writers table
cursor.executemany("INSERT INTO writers (name, num) VALUES (?, ?);", to_db)
con.commit()
if __name__ == '__main__':
# TODO: Create a database connection
con = create_connection()
# Initialize database data
initialize_database(con)
print('Would you like to update a writer database record?')
yesNo = input()
if yesNo[0] == 'y' or yesNo[0] == 'Y':
# Loop until user is done updating records
while yesNo == 'y' or yesNo == 'Y':
print('Which entry would you like to update?')
name = input()
# TODO Check if writer exists in database
if ( ):
print('Enter a new value for ' + name)
num = input()
# TODO Update writer's value for number of works
else:
print("Entry not found. Add new entry?")
yesNo = input()
if yesNo[0] == 'y' or yesNo[0] == 'Y':
print('Number of works?')
num = input()
# TODO Insert new writer entry
print('Would you like to continue to update writers')
yesNo = input()
print("(ID, Name, Num)")
select_all_writers(con)
con.close()
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 2 images
