Python Pyscopg: How do I use place holders with LIMIT/OFFSET and WHERE?

Question

I am currently trying to use place holders in my PostgreSQL query within Python's psycopg's module. Here is a sample of the code I am using.

table.execute('SELECT * FROM table WHERE col2 = %s ORDER BY pID ASC LIMIT %s OFFSET %s;',(val1,val2,val3))

I read somewhere that it is not possible to use placeholders like this for LIMIT and OFFSET however I should use this placeholder format for WHERE =.

safely specifying 'order by' clause from user input in python / postgresql / psycopg2

Does anyone know the proper placeholder syntax for this sql query? Thanks!

Answer 1

Limit and offset can both be used with placeholders without any issue.

Generally speaking you can use placeholders wherever a 'value' would be allowed in an expression.

cur.execute("select * from node where node_name = %s limit %s offset %s", ('test', 5, 5))

Works just fine.

As already noted in the referenced article you cannot use placeholders to refer to tables, columns, schemas, or aliases for any of them. In those cases you generally need to do your own variable substitution before calling execute.