Enter Unspecified Number of Variables into Postgres Psycopg2 SQL query

Question

I'm trying to retrieve some data from a postgresql database using psycogp2, and either exclude a variable number of rows or exclude none.

The code I have so far is:

def db_query(variables):
    cursor.execute('SELECT * '
                   'FROM database.table '
                   'WHERE id NOT IN (%s)', (variables,))

This does partially work. E.g. If I call:

db_query('593')

It works. The same for any other single value. However, I cannot seem to get it to work when I enter more than one variable, eg:

db_query('593, 595')

I get the error:

psycopg2.DataError: invalid input syntax for integer: "593, 595"

I'm not sure how to enter the query correctly or amend the SQL query. Any help appreciated.

Thanks

The argument should be a list or a tuple. See initd.org/psycopg/docs/… (under "lists adaptation") — joop
– joop, Commented Oct 9, 2017 at 8:55

Clodoaldo Neto · Accepted Answer · 2017-10-09 09:45:34Z

1

Pass a tuple as it is adapted to a record:

query = """
    select *
    from database.table
    where id not in %s
"""
var1 = 593
argument = (var1,)
print(cursor.mogrify(query, (argument,)).decode('utf8'))
#cursor.execute(query, (argument,))

Output:

select *
from database.table
where id not in (593)

answered Oct 9, 2017 at 9:45

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

blountdj Over a year ago

Thanks @clodoaldo Neto for the response. I'm not clear on how I can adapt this so I can pass in multiple variables?

Clodoaldo Neto Over a year ago

@blountdj To pass multiple variables just do argument = (var1, var2...)

Collectives™ on Stack Overflow

Enter Unspecified Number of Variables into Postgres Psycopg2 SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related