Good day everyone,
I have a student who has chosen to create a program that links python to an access database for his coursework. He has the code for the connections etc and SOME queries work as intended. The Database is one about films so the categories are Director, name, rating etc etc
The issue seems to be (and I have taken a look myself but my knowledge in this is minimal at best) that the query works IF it is dealing with a numerical field such as rating or even ID. As soon as he tries to query a string such as querying the genre field to show only Horror films it spits out a message about missing parameter despite the code being the same.
His code is as follows, hopefully someone can spot or explain this:
import pypyodbc
conn = pypyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=U:\Python DB\FILM_SMITH.accdb;')
cursor = conn.cursor()
dictionary = {"genre": ["Please input the required genre: ", "Genre"],
"rating": ["Please input the required age rating: ", "BBFC"],
"id": ["Please input the required film name: ", "ID"]}
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")
cursor.execute(connstring)
for row in cursor.fetchall():
print(row)
connstringwhentempcontains a string, you get incorrect SQL of the following form:select * from Films where Genre = Horror. That is incorrect because it should beGenre = "Horror". Constructing SQL usingf-strings like this is often troublesome, particularly for novices. Get your student to consult a textbook on the proper way to insert variable data into a query string using placeholders. – BoarGulestemp = "'"+temp+"'"to add the quotations manually as a quick try and it worked thank you :) – iDadio