How to use an input in python with sql

Question

I have this table in Windows Access:

The title of my table is DATA

NAME - GENDER - NUMBER

Jo - Male - 1

Ali - Male - 2

MO - Male - 3

I want to use an input that asks a name and I want my program to give the details of that person.

I tried to do this:

import pyodbc

conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};' +

        r'DBQ=C:\Users\Gebruiker\PycharmProjects\TennisDatabase.accdb;')
gegevens = conn.cursor()
question = input("Give a name: ")
SelectString = 'SELECT NAME FROM DATA WHERE DATA.NAME = ' + question + ';'

gegevens.execute(SelectString)
gegevensList = gegevens.fetchall()


print(len(gegevensList), "Spelergegevens : ")

for gegevens in gegevensList:
    print (gegevens)
print('')

I get this error:

Traceback (most recent call last): File "C:/Users/Gebruiker/PycharmProjects/Opdracht 1.py", line 9, in gegevens.execute(SelectString) pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')

I have no clue what I am doing wrong and how to fix it. Can anyone help me how to do this?

you can try to insert the query string directly in the gegevens.execute method instead of in a variable. Also try to use '''SELECT NAME FROM DATA WHERE DATA.NAME = question;''' — willy1994
well, you are expecting that in SelectString the question is replaced with the input value. However, the variable call is inside a string and thus you are not actually getting the value. Try 'SELECT NAME FROM DATA WHERE DATA.NAME = ' + question + ';'. It is not going to solve you main problem but it still needs to be fixed — jeanggi90
I edited you suggestion @jeanggi90 and I tried your suggestion but it does not work unfortunately. Do you have any other suggestions? — user10620023

Gord Thompson Gord Thompson · Accepted Answer · 2019-03-22T15:55:40

Print out SelectString and you will see that it looks something like

SELECT NAME FROM DATA WHERE DATA.NAME = Gord;

Your problem is that Gord is not recognized as a string literal; it is interpreted as a column name.

You need to use a parameterized query, like this

sql = "SELECT [NAME] FROM [DATA] WHERE DATA.NAME = ?"
crsr.execute(sql, question)

How to use an input in python with sql

1 Answers