How to insert dictionary items into a PostgreSQL table

Question

So I've connected my Postgresql database into Python with Psycopg2, and I've pulled two specific columns to update. The first column I have used as the keys in a Python dictionary, and I have run some functions on the second one and use the results as the values in the dictionary. Now what I want to do is add those values back into Postgresql table as a new column, but I want them to pair with the correct keys they are paired with in the dictionary. Essentially, I want to take dictionary values and insert them as a new column and pick which "key" in the Postgresql table they belong to (however, I don't want to manually assign them, because, well, there's hopefully a better way).

Postgresql Table

     |col1   |col2  |col3   | ... | coln  
row1 | a1    | b1   | c1    | ... | n1  
row2 | a2    | b2   | c2    | ... | n2  
...  | ...   | ...  | ...   | ... | n...  
rowm | am    | bm   | cm    | ... | nm

This is the dictionary I made in Python, where f() is a series of functions ran on variable:

{ 
    a1 : f(c1),  
    a2 : f(c2),   
    ... : ... 
}

Now my goal is to add the values column back into my table so that it corresponds to the original keys. Ideally, to look something like this:

     |col1|col2|col3| ... |newcol| coln  
row1 | a1 | b1 | c1 | ... | f(c1)| n1  
row2 | a2 | b2 | c2 | ... | f(c2)| n2  
...  | ...| ...| ...| ... |  ... | n...  
rowm | am | bm | cm | ... | f(cm)| nm

I know I can insert the column into the table, but not sure how to pair it with keys. Any help is very much appreciated!

Is col1 the primary key for the db table? (it is the key in the python dict) — joop
– joop, Commented Aug 11, 2016 at 11:37
@joop, no col1 is not the primary key in the db because there are actually pairs of each value in column 1. There is a column record_id, which is basically correlates with row number, which can uniquely identify each row (I think that's the primary key, right?) — dtluther
– dtluther, Commented Aug 11, 2016 at 15:27

mechanical_meat · Accepted Answer · 2016-08-11 04:21:55Z

2

You want an UPDATE statement something like the following:

import psycopg2

con = psycopg2.connect('your connection string')
cur = connection.cursor()
# add newcol
cur.execute('ALTER TABLE your_table ADD COLUMN newcol text;')
con.commit()
for k,v in your_dict.iteritems():
    cursor.execute('''UPDATE your_table 
                         SET newcol = (%s) 
                       WHERE col1 = (%s);''',(v,k))
    conn.commit()
cur.close()
con.close()

edited Aug 11, 2016 at 4:21

answered Aug 11, 2016 at 4:16

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

Comments

Clodoaldo Neto · Accepted Answer · 2016-08-11 11:46:27Z

0

Update all the rows in a single query. First turn the dictionary into a list of tuples:

l = [(k,v) for k,v in my_dict.items()]

A Python list of tuples is adapted to an array of records by Psycopg. Then unnest the array in the from clause:

query = '''
    update t
    set colX = s.val::text
    from unnest(%s) s (key integer, val unknown)
    where t.col1 = s.key
'''

print cursor.mogrify(query, (l,))
cursor.execute(query, (l,))

Records returned from unnest need to have their elements types declared. For string values is it necessary to declare their type as unknown and cast to the appropriate type at assignment or comparing time. For all other types just declare them as usual.

edited Aug 11, 2016 at 11:46

answered Aug 11, 2016 at 10:55

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Collectives™ on Stack Overflow

How to insert dictionary items into a PostgreSQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related