Question

In: Computer Science

6.23 LAB: Python insert/update sqlite3 datafiles Given is a Python program that connects to a sqlite...

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:

  • 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)

Solutions

Expert Solution

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!


Related Solutions

6.23 LAB: Python insert/update sqlite3 datafiles Given is a Python program that connects to a sqlite...
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: 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...
In Java Please!!! 6.22 LAB: Python and sqlite basics Write a Python program that connects to...
In Java Please!!! 6.22 LAB: Python and sqlite basics Write a Python program that connects to a sqlite database. Create a table called Horses with the following fields: id (integer): a primary key and not null name (text) breed (text) height (real) birthday (text) Next, insert the following data row into the Horses table: id: 1 name: 'Babe' breed: 'Quarter Horse' height: 15.3 birthday: '2015-02-10' Output all records from the Horses table. Ex: With the above row inserted, the output...
Given is a Python program that connects to a sqlite database and has one table called...
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...
DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table as...
DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table as if it were being added to MySQL (please give explanations for each line of SQL code and a copy of the code as it would be entered into the query by itself: Customer PK Customer ID Text Phone Number int name text address ID int email text FK vendor ID int Vendor is the name of the table the FK comes from.
DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table: Customer...
DBMS Create/Insert/Update SQL I need the create, insert, and update SQL statement for this table: Customer PK Customer ID Text Phone Number int name text address ID int email text FK vendor ID int Vendor is the name of the table the FK comes from.
write a program in python that insert a number in the middle of an array. Assume...
write a program in python that insert a number in the middle of an array. Assume that the length of an array is even. For instance, is a=(1,4,7,9) and num=100, then really=(1,4,100,7,9)
Chapter 6: Use a list to store the players Update the program in python so that...
Chapter 6: Use a list to store the players Update the program in python so that it allows you to store the players for the starting lineup. This should include the player’s name, position, at bats, and hits. In addition, the program should calculate the player’s batting average from at bats and hits. Console ================================================================ Baseball Team Manager MENU OPTIONS 1 – Display lineup 2 – Add player 3 – Remove player 4 – Move player 5 – Edit player...
Chapter 6: Use a list to store the players In Python, Update the program so that...
Chapter 6: Use a list to store the players In Python, Update the program so that it allows you to store the players for the starting lineup. This should include the player’s name, position, at bats, and hits. In addition, the program should calculate the player’s batting average from at bats and hits. Console ================================================================ Baseball Team Manager MENU OPTIONS 1 – Display lineup 2 – Add player 3 – Remove player 4 – Move player 5 – Edit player...
PYTHON In this lab we will design a menu-based program. The program will allow users to...
PYTHON In this lab we will design a menu-based program. The program will allow users to decide if they want to convert a binary number to base 10 (decimal) or convert a decimal number to base 2 (binary). It should have four functions menu(), reverse(), base2(), and base10(). Each will be outlined in detail below. A rubric will be included at the bottom of this document. Menu() The goal of menu() is to be the function that orchestrates the flow...
8.16 LAB: Mileage tracker for a runner Given the MileageTrackerNode class, complete main() to insert nodes...
8.16 LAB: Mileage tracker for a runner Given the MileageTrackerNode class, complete main() to insert nodes into a linked list (using the InsertAfter() function). The first user-input value is the number of nodes in the linked list. Use the PrintNodeData() function to print the entire linked list. DO NOT print the dummy head node. Ex. If the input is: 3 2.2 7/2/18 3.2 7/7/18 4.5 7/16/18 the output is: 2.2, 7/2/18 3.2, 7/7/18 4.5, 7/16/18 _____________________________ The given code that...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT