Using variables in Python to communicate with PostgreSQL

Question

I've been trying to place variables inside my SQL command. However, when I try this:

def interact_database(command):
    connection = psycopg2.connect("dbname=NAME user=NAME password=PASSWORD")
    cursor = connection.cursor()

    cursor.execute(command)
    connection.commit()

    results = None
    try:
        results = cursor.fetchall()
    except psycopg2.ProgrammingError:
        print("Connection failure.")
        pass

    cursor.close()
    connection.close()

    return results


def pick_question(type):
    if type == 'G':
        QuestionID = random.randint(1,38)
    elif type == 'Gr':
        QuestionID = random.randint(39,60)
    elif type == 'R':
        QuestionID = random.randint(61,89)
    else:
        QuestionID = random.randint(90,119)
    interact_database("SELECT Question FROM Questions WHERE Question_ID = %s")(QuestionID)

pick_question('G')

I get this error

psycopg2.ProgrammingError: syntax error at or near "%"
LINE 1: SELECT Question FROM Questions WHERE Question_ID = %s

I've tried Googling it multiple times, but everywhere I read this should work. Does someone know what I'm doing wrong here? Thanks in advance!

e4c5 · Accepted Answer · 2017-01-18 14:03:53Z

4

It's a common newby mistake to attempt to wrap a database connectivity api in one's own class. It always leads to problems like this. So don't

connection = psycopg2.connect("dbname=NAME user=NAME password=PASSWORD")
cursor = connection.cursor()

def pick_question(type, cursor):
    if type == 'G':
        QuestionID = random.randint(1,38)
    elif type == 'Gr':
        QuestionID = random.randint(39,60)
    elif type == 'R':
        QuestionID = random.randint(61,89)
    else:
        QuestionID = random.randint(90,119)

    cursor.execute("SELECT Question FROM Questions WHERE Question_ID = %s" , (QuestionID,))
    connection.commit()


pick_question('G', cursor)

edited Jan 18, 2017 at 14:03

answered Jan 18, 2017 at 13:29

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

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

7 Comments

JasperMW Over a year ago

What should I put in after 'G' in pick_question('G')? I get the error "missing 1 required positional argument: 'cursor'"

JasperMW Over a year ago

Ooh I see what's wrong now. In my database I'm searching for an integer 1, but the code searches for the string '1' with %s, right? That's why I get no results when I run the code, because they're two different things. However, when I try to replace %s with %i or %d I get the error "ValueError: unsupported format character 'd' (0x64) at index %Id". I think I can fix this by editting the database, but can I also fix it by editting my Python code somehow?

JasperMW Over a year ago

I've editted my previous post, hopefully it's more clear now.

e4c5 Over a year ago

"but the code searches for the string '1' with %s, right?" nopes, postgresql will do automatic type conversion here and reconize that 1 = '1' so %s is indeed teh correct placeholder to use here

e4c5 Over a year ago

This answer does solve the problem that you posted, pertaining t the query not being executed. Your not getting any results is a different matter worthy of another question. However I suspect that the table doesn't contain the data that you expect it has

|

Daniel Roseman · Accepted Answer · 2017-01-18 13:29:21Z

0

You haven't passed your variable to the SQL query. All you've done is place it next to the call to your interact_database function. You need to modify that function to accept the data, and then to pass that to cursor.execute; and then actually pass the data to the function. So:

def interact_database(command, params=None):
    ...
    cursor.execute(command, params)

...
interact_database("SELECT Question FROM Questions WHERE Question_ID = %s", (QuestionID,))

answered Jan 18, 2017 at 13:29

Daniel Roseman

602k68 gold badges910 silver badges923 bronze badges

1 Comment

JasperMW Over a year ago

I editted my code to what you suggested, however I still get no output when I either print(pick_question('G')) or when I just call it. I don't get an error message anymore however.

Collectives™ on Stack Overflow

Using variables in Python to communicate with PostgreSQL

2 Answers 2

7 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

1 Comment

Related