Type error when attempting to insert dict values into postgresql table

Question

I am having trouble inserting multiple values into a postgres table with python's psycopg2 executemany() function. I have a dict with the following values:

{u'city': u'14000', u'sitename': u'12298', u'longitude': u'-9767764.18643674', u'county': u'17031', u'sourceid': u'42', u'state': u'17', u'latitude': u'5147311.10876352', u'csrfmiddlewaretoken': u'WY7EBHl55TuWSwXv4C3vNa5X5d0peJyv', u'sourcesiteid': u'42'  }

which I am attempting to insert with the following code:

try:
    con = psycopg2.connect(db_connect)
    cur = con.cursor()

    cur.executemany("""INSERT INTO cacw_sites(sourceid,sitename,sourcesiteid,state,county,city,schooldistrict,zipcode,neighborhood,latitude,longitude) 
                       VALUES ( %(sourceid)s, %(sitename)s, %(sourcesiteid)s, %(state)s, %(county)s, %(city)s, %(zipcode)s, %(neighborhood)s, 
                                %(latitude)s, %(longitude)s)""", dict)
    con.commit()
except psycopg2.DatabaseError, e:
    print 'There was a problem updating the sites: %s'%e

finally:
    if con:
        con.close()

However, I keep receiving the error: TypeError: string indices must be integers

I realize that I am somehow trying to reference a string with another string but I am not sure where. If I do

dict['state']

I recieve the proper output of

u'17'

So how come I cannot seem to insert these values correctly? Thank you for your help!

try: cur.executemany(..., [dict]). executemany expects a list of lists or dicts. — isedev
– isedev, Commented Feb 27, 2014 at 18:58

Martijn Pieters · Accepted Answer · 2014-02-27 18:59:31Z

1

You are using executemany() which expects a sequence of dictionaries, but give it just a dictionary.

Use:

cur.execute(
    """INSERT INTO cacw_sites(sourceid,sitename,sourcesiteid,state,county,city,schooldistrict,zipcode,neighborhood,latitude,longitude) 
       VALUES ( %(sourceid)s, %(sitename)s, %(sourcesiteid)s, %(state)s, %(county)s, %(city)s, %(zipcode)s, %(neighborhood)s, 
                %(latitude)s, %(longitude)s)""", dict)

instead.

What happens instead is that the database adapter loops over the dictionary object, which yields keys (each a string), then tries to find your parameters on those strings. You end up trying to do the equivalent of 'sourceid'['sourceid'] that way.

answered Feb 27, 2014 at 18:59

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

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

Collectives™ on Stack Overflow

Type error when attempting to insert dict values into postgresql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related