psycopg2 / psycopg2.ProgrammingError: syntax error at or near "E'mytable'"

[Steve Holden]

mrdrew wrote:
> Hey all,
> 
> Right now I'm completely unable to pass parameters to queries under
> any circumstances.  I've got a fairly trivial query as a test...
> 
> c.execute('SELECT * FROM %(table_name)s LIMIT 1',
> {'table_name':"mytable"})
> 
> It fails, giving the error message...
> 
> Traceback (most recent call last):
>   File "test.py", line 7, in <module>
>     c.execute('SELECT * FROM %(table_name)s LIMIT 1',
> {'table_name':"mytable"})
> psycopg2.ProgrammingError: syntax error at or near "E'mytable'"
> LINE 1: SELECT * FROM E'mytable' LIMIT 1
> 
> This may be similar to the problem that ASh had (http://
> groups.google.com/group/comp.lang.python/browse_thread/thread/
> 7463ded0971425f8/538e60ba0ccf2ad3?#538e60ba0ccf2ad3)
> 
> I'd really appreciate any ideas.  At the moment, I'm stuck
> concatenating strings and hoping for the best.

You've already been told about the syntactic errors you have made with
the psycopg2 paramstyle (use %s with a tuple, not %(name)s with a dict).

You should also understand that the purpose of parameterization is
twofold: firstly, to provide efficiency by allowing the database
back-end to avoid duplication of up-front query compilation work when
only the data differs; secondly to avoid any possibility of SQL
injection attacks by ensuring that data are properly escaped.

The first purpose relies on the tables being fixed at the time of
compilation, so you are probably going to have to use string
substitution to build at least that part of the query. Most database
drivers won't allow substitution of table names.

regards
 Steve
-- 
Steve Holden           +1 571 484 6266   +1 800 494 3119
See PyCon Talks from Atlanta 2010  http://pycon.blip.tv/
Holden Web LLC                 http://www.holdenweb.com/
UPCOMING EVENTS:        http://holdenweb.eventbrite.com/