Python Sqlite None Type Error - works if query executed outside function

Question

I am trying to add the below function to my script. I want a user to be able to search a database. Be presented with all possible choices and then be able to search based on a unique book_id to avoid duplicates.

The entire function is below:

# 2 - function to update a book
def update_book():
    '''Takes in new information about a book and replaces the the information in the database
    Allows user to search via title or author 
    Then displays all matches and requests the unique book_id in the event that the search returns more 
    than one result for an author or title. This ensures correct book is updated
    Search by title and author rather than book_id as it is very unlikely a store clerk could remember every unique book_id 
    when the database becomes larger
    parameters - None
    Returns - None. 
    Prints what has been amended in the db, displaying previous info and new updated info.'''

    print("You want to update a book, do you want to search by the author or the book title?")
    search_item = input(" To search by author - Enter a\n To search by title - Enter t")

    if search_item.strip().lower() == "a":

        # ask user if they know the full author name or only a portion
        # if only a portion implement wildcards in the search and direct user to then search off of book_id

        print("\nDo you know the authors initials and surname or do you only know part of the name?")
        partial_or_full = input(" If you know the full details of the author enter full, otherwise press enter and a search on a partial name will be done")
        
        if partial_or_full.strip().lower() == "full":

            # added check to ensure database contains search results

            while True:

                author_search = input("Please enter the authors full name")
                print ("\nThe database contains the below results for the author")

                # display all matches for the author
                cursor.execute('''SELECT * from books WHERE author=?''', (author_search,))

                if cursor is None:
                    print("The database has no results for that author")
                    print("Double check your spelling and try again")
                    continue

                else:

                    for row in cursor:
                        print(f"book_id: {row[0]}, Title: {row[1]}, Author: {row[2]}, Stock: {row[3]}")

            print("\nPlease select your book from the above")
            book_to_change = int(input("Enter the book's unique book_id ").strip())

        else:

            # make use of wildcards in search
            # in the event that the store clerk did not have the full author name
            # added check for if the user results returns nothing in the event of a partial entry

            while True:

                author_search = input("\nPlease enter the known piece of the author's name to search ")
                wild_card_search = "%"+author_search+"%"

                cursor.execute('''SELECT * FROM books WHERE author LIKE ?''', (wild_card_search,))
                
                if cursor is None:
                    print("\nThe Database contains no similar search results")
                    print("Double check your spelling and try again")
                    continue

                else:
                    print("Search results will be printed below:")
                    for row in cursor:
                        print(f"book_id: {row[0]}, Title: {row[1]}, Author: {row[2]}, Stock: {row[3]}")
                    break

            print("\nPlease select your book from the above")
            book_to_change = int(input("Enter the book's unique book_id ").strip())
            
            
        # based on book_id retrieve the previous database info
        cursor.execute('''SELECT * FROM books WHERE book_id=?''', (book_to_change,))
        
        old_title = cursor.fetchone()[1]
        old_author = cursor.fetchone()[2]
        old_stock = cursor.fetchone()[3]

        # request updated information from the user
        print("\nPlease enter the new information for the book")
        print("If you want to leave a value unchanged please enter the previous value")

        book_title = input("\nPlease enter the previous or ammended Title of the book ")
        book_author = input("\nPlease enter the previous or ammended Author of the book ")
        book_stock = int(input("\nPlease enter the previous or ammended Stock on hand "))

        # add changes to the database

        cursor.execute(''' UPDATE books SET (Title = ?,Author = ?,Qty = ?) WHERE book_id = ? ''', (book_title,book_author,book_stock,book_id))
        db.commit()



        print(f"\n Thank you, the below changes have been made to book_id: {book_id}")
        print("The information has been changed FROM:")
        print(f'''Title: {old_title}
        Author: {old_author}
        Stock on Hand: {old_stock}''')
        print("To the following:")
        print(f'''Title: {book_title}
        Author: {book_author}
        Stock: {book_stock}''')


        print("\n Data base updated and ready for query....")


    elif search_item.strip().lower() == "t":

        # ask user if they know the full title or only a portion
        # if only a portion implement wildcards in the search and direct user to then search off of book_id

        print("\nDo you know the full title or only the beginning?")
        partial_or_full = input("If you know the full title enter full otherwise press enter and a search on a partial title will be done")
        
        if partial_or_full.strip().lower() == "full":

            # added check to ensure database contains search results
            
            while True:

                title_search = input("Please enter the full title")
                print ("\nThe database contains the below results for the author")

                # display all matches for the author
                cursor.execute('''SELECT * from books WHERE title=?''', (title_search,))

                if cursor is None:
                    print("The database has no results for that title")
                    print("Double check your spelling and try again")
                    continue

                else:

                    for row in cursor:
                        print(f"book_id: {row[0]}, Title: {row[1]}, Author: {row[2]}, Stock: {row[3]}")
                    break

            print("\nPlease select your book from the above")
            book_to_change = int(input("Enter the book's unique book_id ").strip())

        else:

            # make use of wildcards in search
            # in the event that the store clerk did not have the full title
            # added check for if the user results returns nothing in the event of a partial entry

            while True:

                title_search = input("\nPlease enter the portion of the title that is known ")
                wild_card_search = "%"+title_search+"%"
                
                cursor.execute('''SELECT * FROM books WHERE title LIKE ?''', (wild_card_search,))
                print("Search results will be printed below:")

                if cursor is None:
                    print("\nThe Database contains no similar search results")
                    print("Double check your spelling and try again")
                    continue

                else:
                    print("Search results will be printed below:")
                    for row in cursor:
                        print(f"book_id: {row[0]}, Title: {row[1]}, Author: {row[2]}, Stock: {row[3]}")
                    break

            print("\nPlease select your book from the above")
            book_to_change = int(input("Enter the books unique book_id ").strip())
            
            
        # based on book_id retrieve the previous database info
        cursor.execute('''SELECT * FROM books WHERE book_id=?''', (book_to_change,))
        old_title = cursor.fetchone()[1]
        old_author = cursor.fetchone()[2]
        old_stock = cursor.fetchone()[3]

        # request updated information from the user
        print("\nPlease enter the new information for the book")
        print("If you want to leave a value unchanged please enter the previous value")

        book_title = input("\nPlease enter the previous or ammended Title of the book ")
        book_author = input("\nPlease enter the previous or ammended Author of the book ")
        book_stock = int(input("\nPlease enter the previous or ammended Stock on hand "))

        # add changes to the database

        cursor.execute(''' UPDATE books SET (Title = ?,Author = ?,Qty = ?) WHERE book_id = ? ''', (book_title,book_author,book_stock,book_id))
        db.commit()



    print(f"\n Thank you, the below changes have been made to book_id: {book_id}")
    print("The information has been changed FROM:")
    print(f'''Title: {old_title}
    Author: {old_author}
    Stock on Hand: {old_stock}''')
    print("To the following:")
    print(f'''Title: {book_title}
    Author: {book_author}
    Stock: {book_stock}''')


    print("\n Data base updated and ready for query....")

The piece that is giving me issues is when asking for the book_id Like so

print("\nPlease select your book from the above")
book_to_change = int(input("Enter the book's unique book_id ").strip())
            
            
# based on book_id retrieve the previous database info
cursor.execute('''SELECT * FROM books WHERE book_id=?''', (book_to_change,))
        
old_title = cursor.fetchone()[1]
old_author = cursor.fetchone()[2]
old_stock = cursor.fetchone()[3]

I am able to take the user input and store it as book_to_change. However I get the below error when trying to store the old author, title and stock.

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-20-2c02560967f4> in <module>
----> 1 update_book()

<ipython-input-19-8bc56328ea70> in update_book()
     79 
     80                 old_title = cursor.fetchone()[1]
---> 81                 old_author = cursor.fetchone()[2]
     82                 old_stock = cursor.fetchone()[3]
     83 

TypeError: 'NoneType' object is not subscriptable

If I execute a search outside the function I can retrieve the data and store it using this code:

book_to_change = 3002
cursor.execute('''SELECT * FROM books WHERE book_id=?''', (book_to_change,))

test = cursor.fetchone()[1]
print(test)

Output is:

Harry Potter and the Philospher's Stone

It is only when using the function that I cannot seem to get it right. I have executed the code to make sure it works and then pasted it in exactly the same but I get the same error.

I am not sure why I cannot get it to execute when working from my function update_book

What have I done wrong?

Answer 1

You should use cursor.fetchone() only once

data = cursor.fetchone() 

and later use

old_title = data[1]
old_author = data[2]
old_stock = data[3]

Using cursor.fetchone() second time it may try to get second row which you don't have.