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!

I don't see anything on the [psycopg2 docs](http://initd.org/psycopg/docs/usage.html#query-parameters) about using placeholders in the `ORDER BY` part of the SQL query. Additionally, as [you referenced already](http://stackoverflow.com/q/11556711/86263), you'll need to do your own formatting if you want to use placeholders & `ORDER BY`. — bitcycle, Dec 15 '12 at 00:15
Yeah I looked through the docs already =/ I was under the impression it was ill advised to manually do your own SQL formatting — sheldonk, Dec 15 '12 at 02:13

score 6 · Accepted Answer · answered Jan 28 '13 at 03:39

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.

To have identifiers or expressions place holders check [my answer](http://stackoverflow.com/a/13891511/131874). — Clodoaldo Neto, Jan 28 '13 at 16:00

score 0 · Answer 2 · answered Jan 28 '13 at 16:10

In very old versions of PostgreSQL, it was indeed not possible to use placeholders in LIMIT and OFFSET clauses. This functionality was added in version 7.4, so it is safe to assume that it exists in current installations.

But that only applies to server-side prepared statements. Psycopg does not use server-side prepared statements. It does its own string substitution and sends the resulting string to the backend as a constant. So in principle, you can use its parameter substitution feature anywhere there resulting literal would be syntactically valid.

So what you are proposing to do is fine either way.

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

2 Answers2