psycopg error, column does not exist

Question

I keep getting this

error: psycopg2.ProgrammingError: column "someentry" does not exist.

The error indicates that the column someentry does not exist when someentry is not a column it's just a value to enter into the db.

Here is the code that gives the error:

cur.execute('INSERT INTO {0!s} (ip_id, item) VALUES ({1!s}{2!s})'.format('mytable',1,'someentry'))

Here is how I create my table:

tablename = 'mytable'
command = """
          CREATE TABLE IF NOT EXISTS {} (
                ip_id SERIAL PRIMARY KEY,
                item VARCHAR(255) NOT NULL
          )
          """.format(tablename)

cur.execute(command)

score 8 · Answer 1 · answered Mar 10 '18 at 11:00

You have to use single quotes within the query.

I received the same type of error from this

cur.execute('insert into my_table(id, name, horse_type, horse_code, horse_name) values(default, %s, 3, %s, "Mary Wonder")', [e[0], e[1]])

it produced

Traceback (most recent call last):
File "process_horse.py", line 11, in <module>
[e[0], e[1]])
psycopg2.ProgrammingError: column "Mary Wonder" does not exist
LINE 2: ', "Mary Wonder")
       ^

Obviously it is data, not a column name, like you said.
When I changed it to

cur.execute("insert into my_table(id, name, horse_type, horse_code, horse_name) values(default, %s, 3, %s, 'Mary Wonder')",[e[0], e[1]])

it worked with no errors.

score 1 · Accepted Answer · edited May 23 '17 at 11:46

1

The problems that cause this error are because you forgot to add a comma between {1!s} and {2!s}, and you also didn't escape the string 'someentry' so postgres thought it was a column name identifier.

The solution is to fix the syntax error and escape values. Here's the correct way to do that:

cur.execute(
    'INSERT INTO mytable (ip_id, item) VALUES (%s, %s)',
    (1, 'someentry')
)

If the table name is also a variable, since the table name is an identifier you need to use extension AsIs:

from psycopg2.extensions import AsIs

cur.execute(
    'INSERT INTO %s (ip_id, item) VALUES (%s, %s)',
    (AsIs('mytable'), 1, 'someentry')
)

edited May 23 '17 at 11:46

Community

1
1

answered Jan 05 '17 at 00:49

Philip Tzou

5,926
2
18
27

Philip, thank you. It worked. But could you explain what you mean by "escaping the string". All you did was use the %s string format. How is that escaping the string? Does psycopg automatically escape it? – 7alman Jan 05 '17 at 00:58
@7alman: Yes, you can read this article of psycopg2: http://initd.org/psycopg/docs/usage.html#query-parameters – Philip Tzou Jan 05 '17 at 01:05

psycopg error, column does not exist

2 Answers2

Linked