CODEWITHSUNDEEP
pythonpostgresqlpsycopg2
JasperMW
JasperMW

Reputation: 533

Using variables in Python to communicate with PostgreSQL

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!

Upvotes: 1

Views: 3270

Answers (2)

e4c5
e4c5

Reputation: 53744

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)

Upvotes: 4

Daniel Roseman
Daniel Roseman

Reputation: 599630

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,))

Upvotes: 0

Related Questions