Puzzling encoding inconsistency with python, Flask, psycopg2

Question

I am using postgres with a psycopg2 python/flask web app.

I am having an encoding problem that I'm sure is something stupid I'm missing (I'm new to programming). The following statement works perfectly:

cur.execute("SELECT column_name FROM information_schema.columns where table_name = %s;", (tablename,))

I use fetchall() to create a list of column names in my table. However, another statement doesn't work:

cur.execute("ALTER TABLE %s ADD COLUMN %s varchar;", (tablename, col,))

Here is the error:

psycopg2.ProgrammingError
ProgrammingError: syntax error at or near "E'flatresponses_1'"
LINE 1: ALTER TABLE E'flatresponses_1' ADD COLUMN E'What was the bes...

('flatresponses_1' is the 'tablename', and 'What was the best...' is the start of 'col'.)

I did 'print cur.query' and here is the result:

>>> print cur.query
>>> ALTER TABLE E'flatresponses_1' ADD COLUMN E'What was the best part of your ENT clinic visit today? Why?' varchar;

I'm getting E' encoding in the second query but not the first. I've also tried str(tablename).

What am I missing?!

Your problem is that psycopg2 is treating the tablename and others as literal values instead of "keywords". [This question](http://stackoverflow.com/questions/6294001/psycopg2-e-on-table-field-and-schema) is about the same thing. — jadkik94, Dec 31 '12 at 08:08
It does, but for the first one, PostgreSQL expects string *values* there. But in the second, it expects *field names* or *table names* which are not strings. The parameter binding is intended for the first case, not the second. Hence what you are experiencing :) — jadkik94, Dec 31 '12 at 08:17

score 2 · Answer 1 · edited May 23 '17 at 11:56

2

I ended up using the AsIs psycopg2 extension as described in this post. Worked like a charm!

edited May 23 '17 at 11:56

Community

1
1

answered Dec 31 '12 at 08:51

Michael Levinson

95
2
8

score 0 · Answer 2 · answered Dec 31 '12 at 08:13

0

Table and column names aren't text types, they are identifiers (type = name). They do not take escaped string literals and presumably you need something other than %s for your placeholder.

http://www.postgresql.org/docs/9.2/static/datatype-character.html

answered Dec 31 '12 at 08:13

Richard Huxton

21,516
3
39
51

Thanks for the direction. I'm not seeing another placeholder that would seem to work. Could specifying naming columns via variable placeholder not be supported in python/psycopg2?! – Michael Levinson Dec 31 '12 at 08:27
Possible - I'm not familiar with that driber I'm afraid. – Richard Huxton Dec 31 '12 at 09:18

Puzzling encoding inconsistency with python, Flask, psycopg2

2 Answers2