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
– willy1994, Commented Mar 22, 2019 at 15:31
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
– jeanggi90, Commented Mar 22, 2019 at 15:33
I edited you suggestion @jeanggi90 and I tried your suggestion but it does not work unfortunately. Do you have any other suggestions? — user10620023
– user10620023, Commented Mar 22, 2019 at 15:41

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

1

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)

answered Mar 22, 2019 at 15:55

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use an input in python with sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related