Obtaining data from PostgreSQL as Dictionary

I have a database table with multiple fields which I am querying and pulling out all data which meets certain parameters. I am using psycopg2 for python with the following syntax:

cur.execute("SELECT * FROM failed_inserts where insertid='%s' AND site_failure=True"%import_id)
                failed_sites= cur.fetchall()

This returns the correct values as a list with the data's integrity and order maintained. However I want to query the list returned somewhere else in my application and I only have this list of values, i.e. it is not a dictionary with the fields as the keys for these values. Rather than having to do

desiredValue = failed_sites[13] //where 13 is an arbitrary number of the index for desiredValue

I want to be able to query by the field name like:

desiredValue = failed_sites[fieldName] //where fieldName is the name of the field I am looking for

Is there a simple way and efficient way to do this?

Thank you!

Answers


cursor.description will give your the column information (http://www.python.org/dev/peps/pep-0249/#cursor-objects). You can get the column names from it and use them to create a dictionary.

cursor.execute('SELECT ...')
columns = []
for column in cursor.description:
    columns.append(column[0].lower())
failed_sites = {}
for row in cursor:
    for i in range(len(row)):
        failed_sites[columns[i]] = row[i]
        if isinstance(row[i], basestring):
             failed_sites[columns[i]] = row[i].strip()

The "Dictionary-like cursor", part of psycopg2.extras, seems what you're looking for.


Need Your Help

closure-library drag a goog.fx.DragListGroup Item between Panes

javascript html html5 google-closure google-closure-library

i'm currently playing with the google closure library but ran into a problem.