Issues with IF statements and Pypyodbc

Question

I am brand new to Python and know very little. I have connected my MS-Access file to my python using Pypyodbc. I am attempting to query with it using a user input however, I need it to be able to change based on the input of the user rather than having lots of hard-coded options. Here is the code and any help is greatly appreciated. Thanks.

Side note: I would be adding more options to this if statement in the future (a total of 5 accepted user inputs)

import pypyodbc

conn = pypyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\DB\FILM_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

First, your code is prone to SQL injection attacks. You should pass the parameter dynamically, instead of putting it inside the string. See this for example.

Now to your actual question. If you want to avoid some code repetition, you can make a list of possible fields. You should have a way to restrict the possible fields, otherwise the user might provide an invalid field. Here is an example:

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

Your requirements might vary, so probably there is a better solution for your case. But that should help, I hope.

Answer 2

Here is another way of doing it to prevent code duplication using a dictionary.

the dictionary is structured like this

"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:\DB\FILM_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)