0

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)
iDadio
  • 1
  • Strings need to be quoted. Better to use prepared statements – HoneyBadger Mar 17 '22 at 10:16
  • If you print out the value of `connstring` when `temp` contains a string, you get incorrect SQL of the following form: `select * from Films where Genre = Horror`. That is incorrect because it should be `Genre = "Horror"`. Constructing SQL using `f`-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. – BoarGules Mar 17 '22 at 10:20
  • I didn't think of that, I have just tried hard forcing the following: ```temp = "'"+temp+"'"``` to add the quotations manually as a quick try and it worked thank you :) – iDadio Mar 17 '22 at 10:23
  • @iDadio - If you counsel your student to embed untrusted (user) input into a SQL statement via string formatting then you are doing them a grave disservice. – Gord Thompson Mar 17 '22 at 13:06

0 Answers0