"ProgrammingError: syntax error at or near...'" when updating rows in postgres with python dictionary

Question

I'm new to postgres. I'm trying to update rows in a postgres table with a Python dictionary. A sample of the error:

Traceback (most recent call last):
  File "json_to_sql.py", line 187, in <module>
    logger.debug(c.execute(update_movie_data, movie_vars))
psycopg2.ProgrammingError: syntax error at or near "'genres'"
LINE 1: UPDATE movies SET 'genres' = ARRAY['Comedy'] WHERE original_...
                          ^

The ^ always points to the ' no matter the key.

The dictionary, movie_dicts, has entries that look like this:

{'adult': False,
'genres': ['Comedy'],
'original_language': 'pa', 
'original_title': 'Jatts in Golmaal',
'overview': "Jatts in Golmal is an Comedy... [Truncated for brevity.]", 
'production_countries': [],
'release_date': '2003-02-21',
'title': 'Jatts in Golmaal',
'images': '/g5epyphS4WqmwJDIu14EL20gxuc.jpg'}

I create the database with psql in shell. Then, I create the table without a hitch with this in Python:

create_table_movie = """CREATE TABLE movies (
id SERIAL PRIMARY KEY,
adult TEXT,
original_language TEXT,
original_title TEXT,
title TEXT,
overview TEXT,
release_date DATE,
genres TEXT,
production_countries TEXT,
videos TEXT,
images TEXT
);"""

c.execute(create_table_movie)

Thereafter, I iterate through each entry in the dictionary. On each loop, I first insert the movie title into the original_title column. It works well.

Then, I loop through each entry to update each row where its title has just been inserted. It fails. The code:

for i in movie_dicts:

    insert_movie_data = "INSERT INTO movies (original_title) VALUES (%s);"
    movie_titles = i['original_title'],
    c.execute(insert_movie_data, movie_titles)

    for k, v in i.items():
        try:
            update_movie_data = "UPDATE movies SET %s = %s WHERE original_title = %s;"
            movie_vars = k, v, i['original_title']
            logger.debug(c.execute(update_movie_data, movie_vars))

        except:
            logger.exception()
            pass

I've read through a lot of similar questions on Stack Overflow about this error with relation to syntax, and also the documentation on passing parameters to sql queries. I thought maybe this one came closest to my problem. So I tried to remove the ' with k.replace('\'', "") and even k[1:-1]. Didn't work.

My syntax looks correct, but it evidently is not. Can't figure it out. What have I missed?

Single quotes (and %s) are for strings, identifiers (such as table and column names) should be quoted with double quotes. Looks like you want to use something other than %s but I don't really know enough Python or psycopg to know how this works: initd.org/psycopg/docs/sql.html — mu is too short
– mu is too short, Commented Feb 12, 2018 at 4:20
I removed my answer after realizing that I don't know what the expected output is for the SQL string. Note that if you want something formatted and unquoted in a string, use %s and then process the string substitution before calling c.execute on the string. Also note that %r (repr()) can be used to put quoted strings into other strings. — JacobIRR
– JacobIRR, Commented Feb 12, 2018 at 4:57

nusantara · Accepted Answer · 2018-02-13 14:39:40Z

1

@muistooshort provides the link in comment above that solves this issue: http://initd.org/psycopg/docs/sql.html

Edit:

It looks like using %s as a placeholder for the table gave it single quotes, which is the syntax error. Using sql.Identifier() and .format() to identify the table properly prevented this. An example from the page:

from psycopg2 import sql

cur.execute(
    sql.SQL("insert into {} values (%s, %s)")
        .format(sql.Identifier('my_table')),
            [10, 20])

edited Feb 13, 2018 at 14:39

answered Feb 12, 2018 at 14:34

nusantara

1,2662 gold badges18 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

erik258 Over a year ago

So what was the fix? Join the array into a string?

nusantara Over a year ago

@DanFarrell It looks like using %s as a placeholder for the table gave it single quotes, which is the syntax error. Using sql.Identifier() and .format() to identify the table properly prevented this.

Collectives™ on Stack Overflow

"ProgrammingError: syntax error at or near...'" when updating rows in postgres with python dictionary

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related