IF声明和Pypyodbc的问题

Question

我是Python的新手，对Python知之甚少。我已经使用Pypyodbc将我的MS Access文件连接到我的python。我试图使用用户输入进行查询，但是，我需要它能够根据用户的输入进行更改，而不是有很多硬编码的选项。这是代码，非常感谢您的帮助。谢谢

旁注：我将在未来为此if语句添加更多选项(总共5个可接受的用户输入(

import pypyodbc
conn = pypyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:DBFILM_SMITH.accdb;')
cursor = conn.cursor()
input("What would you like to search for? ")
if input == "genre":
genre = input("Please input the required genre: ")
connstring = "select * from Films where Genre = " + genre
if input == "rating":
rating = input("Please input the required age rating: ")
connstring = "select * from Films where BBFC = " + rating
else:
print("Invalid Entry")
for row in cursor.fetchall():
print (row)

Answer 1

首先，您的代码容易受到SQL注入攻击。应该动态传递参数，而不是将其放入字符串中。请参见此示例。

现在来谈谈你的实际问题。如果您想避免某些代码重复，可以列出可能的字段。您应该有一种方法来限制可能的字段，否则用户可能会提供无效字段。这里有一个例子：

available_fields = ['genre', 'rating', 'another_field']
fld = input('What field would you like to search for?')
assert fld in available_fields, 'Invalid field'
value = input('Please enter a value you want to search for: ')
query = f'select * from Films where {fld} = ?'
# Now run the query with *value* as a dynamic parameter

您的要求可能会有所不同，因此可能有更好的解决方案来满足您的需求。但我希望这会有所帮助。

Answer 2

下面是使用字典防止代码重复的另一种方法。

字典的结构类似于

"key_you_want_to_search":["Question_you_want_the_user_to_answer","Name_of_your_SQL,column"]

import pypyodbc
conn = pypyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:DBFILM_SMITH.accdb;')
cursor = conn.cursor()
dictionary = {"genre": ["Please input the required genre: ", "Genre"],
"rating": ["Please input the required age rating: ", "BBFC"]}
user_input = input("What would you like to search for? ")
if user_input in dictionary:
temp = input(dictionary[user_input][0])
connstring = f"select * from Films where {dictionary[user_input][1]} = {temp}"
else:
print("Invalid Entry")
for row in cursor.fetchall():
print(row)