Question

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)

Answer 1

# import necessary module(s)
import sqlite3
from sqlite3 import Error

def connect(dbFileName):
    #  This function connects to the database
    # @Param: data base file name
    # Return: connection
    connection = None
    # connect to data base
    try:
        connnection = sqlite3.connect(dbFileName)
        # return the connection
        return connnection
    except Error as err:
        # show error
        print(err)
    # return to caller
    return connection


def createTable(connection, query):
    #  This function creates a table
    # @Param: connection and table creation query
    # Return: 

    # execute query
    try:
        c = connection.cursor()
        c.execute(query)
    # if query throws error
    except Error as e:
        print(e)


def addData(dbFileName):
    # create a database connection
    connection = connect(dbFileName)

    # enter name and number
    name=str(input("Enter the name: "))
    num=int(input("Enter a number: "))

    # create cursor
    cursor=connection.cursor()
    # execute query
    cursor.execute("SELECT Name FROM writers WHERE Name=?",(name,))
    # fetch all
    check=cursor.fetchall()
    # If writer not present insert in table
    if len(check)==0:
        connection.execute("INSERT INTO writers(Name,Num) VALUES(?,?)",(name,num))
        # show success message
        print("Data added successfully!!!")
    else:
        # if data already present update it with new
        connection.execute("UPDATE writers set Num=? where Name = ?",(num,name))
        # show success message
        print("Data was avialable and it is updated now!!!")
    # commit the changes
    connection.commit()
    # close the connection
    connection.close()

def updateData(dbFileName):
    # This function update data
    # @Param: database filename
    
    # create a database connection
    connection = connect(dbFileName)

    # enter name and number
    name=str(input("Enter the name: "))
    num=int(input("Enter a number to update: "))

    # update data
    try:
        connection.execute("UPDATE writers set Num=? where Name = ?",(num,name))
    # if query throws error
    except Error as e:
        print("Error while updating!!")

    # commit the changes
    connection.commit()
    # close the connection
    connection.close()


def showData(dbFileName):
    # This function shows all the data
    # @Param: database filename

    # create a database connection
    connection = connect(dbFileName)

    # select all from table
    dataRow = connection.execute("SELECT * FROM writers")
    print("\n(ID, Name, Num)")
    row = 1
    #  show record
    for fields in dataRow:
        print("( {0}, {1}, {2} )".format(fields[0],fields[1],fields[2]))
        row = row + 1

    # close the connection
    connection.close()
    

# main function
def main(database, query):
    # This function handles other function
    # @Param: database name ad table creation query
    
    # create a database connection
    connection = connect(database)

    # create tables
    if connection is not None:
        # call function to create table
        createTable(connection, query)
        print("Succesfully connected to database!!")
    else:
        print("Error!! Unable to connect to database!!")


    while True:
        print("\n\nEnter 1 to add data")
        print("Enter 2 to update data")
        print("Enter 3 to show data")
        print("Enter 4 exit")
        choice = int(input("Your choice >> "))
        if choice == 4:
            break
        if choice == 1:
            addData(database)
        elif choice == 2:
            updateData(database)
        elif choice == 3:
            showData(database)

# deiver code
if __name__ == '__main__':
    # intialize database name
    # and query
    database = "writers.db"

    query = """ CREATE TABLE IF NOT EXISTS writers (
                ID integer PRIMARY KEY,
                Name text,
                Num  integer
               ); """
    # call main
    main(database, query)

___________________________________________________________________

___________________________________________________________________

Succesfully connected to database!!


Enter 1 to add data
Enter 2 to update data
Enter 3 to show data
Enter 4 exit
Your choice >> 3

(ID, Name, Num)
( 1, James Bond, 23 )
( 2, Harry Potter, 20 )
( 3, J K Rowling, 25 )


Enter 1 to add data
Enter 2 to update data
Enter 3 to show data
Enter 4 exit
Your choice >> 2
Enter the name: James Bond
Enter a number to update: 50


Enter 1 to add data
Enter 2 to update data
Enter 3 to show data
Enter 4 exit
Your choice >> 3

(ID, Name, Num)
( 1, James Bond, 50 )
( 2, Harry Potter, 20 )
( 3, J K Rowling, 25 )


Enter 1 to add data
Enter 2 to update data
Enter 3 to show data
Enter 4 exit
Your choice >> 1
Enter the name: Jane Austen
Enter a number: 6
Data added successfully!!!


Enter 1 to add data
Enter 2 to update data
Enter 3 to show data
Enter 4 exit
Your choice >> 3

(ID, Name, Num)
( 1, James Bond, 50 )
( 2, Harry Potter, 20 )
( 3, J K Rowling, 25 )
( 4, Jane Austen, 6 )


Enter 1 to add data
Enter 2 to update data
Enter 3 to show data
Enter 4 exit
Your choice >> 4

___________________________________________________________________

___________________________________________________________________

Note: If you have queries or confusion regarding this question, please leave a comment. I would be happy to help you. If you find it to be useful, please upvote.