CODEWITHSUNDEEP
pythonpostgresqlencodingflaskpsycopg2
Michael Levinson
Michael Levinson

Reputation: 95

Puzzling encoding inconsistency with python, Flask, psycopg2

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?!

Upvotes: 0

Views: 613

Answers (2)

Michael Levinson
Michael Levinson

Reputation: 95

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

Upvotes: 2

Richard Huxton
Richard Huxton

Reputation: 22893

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

Upvotes: 0

Related Questions