update python dictionary to postgres db table using psycopg2

Question

I have a dictionary which represents one row from a table.
For example my table is this:

CREATE TABLE test (
    id          char(5) CONSTRAINT firstkey PRIMARY KEY,
    column1     varchar(40) NOT NULL,
    column2     integer
);

My dict looks like this:

mydict = {'id':'row11', 'column1':'abcdef', 'column2':4}

Now i am searching for an elegant generic way to update this data in the table with Psycopg2 similar to the way it is described for an insert statement here:

Insert Python Dictionary using Psycopg2

Or is this not possible with an update-statement?

score 0 · Answer 1 · answered Jul 07 '19 at 16:11

Are you just looking for the update syntax if you know the names of the columns ahead of time?

cursor.execute('UPDATE test set column1=%(column1)s, column2=%(column2)s where id=%(id)s', mydict)

If you are looking for a solution where you don't know the columns ahead of time, you can use the SQL string composition module. Just make sure you are always using parameterized queries and don't try to build the whole query yourself to avoid SQL injection issues.

update python dictionary to postgres db table using psycopg2

1 Answers1