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

Tim Chase python.list at tim.thechases.com
Sat Apr 3 03:57:41 CEST 2010


MRAB wrote:
> I think that you're confusing Python's string formatting with 
> SQL placeholders.
> 
> The "%(table_name)s" works only with Python's '%' operator. 
> You should use only the "%s" form (or possibly "?", I'm not 
> sure which!)

It varies depending on your DB driver.  Check out the .paramstyle 
property of your DB driver:

   >>> import sqlite3
   >>> sqlite3.paramstyle
   'qmark'

(sqlite uses "?" as the placeholder).  Annoying at times, but at 
least documented and able to be automated which is more than I 
can say for non-Python DB drivers.

> in the template string and pass the parameters in a tuple 
> (maybe a list will also work) when calling .execute().

Additionally, the OP is passing in a *table-name*, not a 
parameter value.  Most DB interfaces only allow things like

   # Okay:
   cur.execute("select * from tbl where field=?", (value,))

not

   # not okay
   cur.execute("select * from ? where field=42", (tblname,))

For this, you really have to (1) use Python string-formatting 
instead of DB parameters and (2) THOROUGHLY vet that the 
table-name isn't something malicious -- either through 
controlling that it never comes from the user, or enforcing a 
fairly strict limit on what table-names can be used if they do 
come from the user.  Regardless of parameter placeholder style.

-tkc







More information about the Python-list mailing list