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)

Answer 1

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.

Answer 2

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')
)