python - Python Sqlite 无类型错误 - 如果查询在函数外执行则有效
问题描述
我正在尝试将以下函数添加到我的脚本中。我希望用户能够搜索数据库。呈现所有可能的选择，然后能够根据唯一的 book_id 进行搜索以避免重复。
整个函数如下：
# 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....")
给我问题的部分是在要求 book_id 时像这样
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]
我能够接受用户输入并将其存储为 book_to_change。但是，在尝试存储旧作者、标题和库存时出现以下错误。
---------------------------------------------------------------------------
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
如果我在函数之外执行搜索，我可以使用以下代码检索数据并存储它：
book_to_change = 3002
cursor.execute('''SELECT * FROM books WHERE book_id=?''', (book_to_change,))
test = cursor.fetchone()[1]
print(test)
输出是：
Harry Potter and the Philospher's Stone
只有在使用该功能时，我似乎无法正确使用它。我已经执行了代码以确保它可以正常工作，然后将其粘贴到完全相同的位置，但我得到了同样的错误。
I am not sure why I cannot get it to execute when working from my function update_book
What have I done wrong?
解决方案
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.
推荐阅读
- c++ - 为什么两个不同布局兼容类型的数组本身布局不兼容？
- javascript - 为什么使用 POST 方法可以防止 json 劫持？
- django - Heroku/Django：项目未迁移，运行 manage.py migrate 时抛出有关丢失表的错误
- javascript - Microsoft Graph API 文件请求不返回任何文件
- c - Realloc 错误，读取管道失败错误，关闭管道错误
- twitter-bootstrap - Bootstrap 不固定按钮
- python - 从保存在集合中的标准输出拆分行
- c++ - VirtualAllocEx 抛出 ERROR_INVALID_HANDLE
- c++ - 毫不拖延地将作业传递给另一个函数
- c# - 我收到“SQLite 错误 14：'无法打开数据库文件'”。