0

I'm trying to execute the below postgres query through python psycopg2

SELET * FROM users WHERE user_id IN (1,2,3,4,5);

Below is the python code for the same:

def get_usernames(userids):
    connection = psycopg2.connect("dbname=userdb user=guest host=example.com")
    connection.autocommit = True
    cursor = connection.cursor()
    cursor.execute("SELECT username FROM users WHERE userid IN {}".format(userids))
    return cursor

userids=[1,2,3,4,5]
get_usernames(userids)

I'm getting the below error:

psycopg2.ProgrammingError: syntax error at or near "["
LINE 1: ...from users WHERE userid IN {1,2...

I'm used to pymysql. This is new to me. Let me know what is my mistake.

Gowtham
  • 351
  • 1
  • 4
  • 20
  • `userids` is a list, you can't just interpolate that into a query. – Martijn Pieters Jan 31 '19 at 12:50
  • And please copy and paste errors, don't type them in manually. The error message would say `WHERE userid in [1, 2, ...` at the very least. – Martijn Pieters Jan 31 '19 at 12:51
  • In `pymysql` we can send a list and get this done... However for time being im iterating through `for` loop for all `userids`.. Is there any efficient way to achieve this?.. – Gowtham Jan 31 '19 at 12:52
  • 1
    All you need is a `%s` query parameter for the values: `cursor.execute("SELECT username FROM users WHERE userid IN %s", (tuple(userids),))` (but do pass in the ids as a tuple). – Martijn Pieters Jan 31 '19 at 12:53
  • @MartijnPieters that is the error message i got .. I didn't type in.. Pasted as it is.. All others were just stacktraces – Gowtham Jan 31 '19 at 12:56
  • The actual error message produced is `'syntax error at or near "["\nLINE 1: SELECT username FROM users WHERE userid IN [1, 2, 3, 4, 5]\n ^\n'` (whitespace before the `^` collapsed from 51 to 1 space by the way SO renders comments). Note the square brackets and spacing in that error message, there is no `{` curly brace and there are spaces after each comma. ` – Martijn Pieters Jan 31 '19 at 13:04
  • well.. I just modified the error message from actual code.. :-) ... Never expected you'd pinpoint like this :-D ... – Gowtham Jan 31 '19 at 13:10
  • Exactly, and for future questions: *don't do that*, as you may be removing critical information that we may need to diagnose issues. – Martijn Pieters Jan 31 '19 at 13:11
  • sure.. thanks for the feedback and answering on time.. – Gowtham Jan 31 '19 at 13:13

0 Answers0