CODEWITHSUNDEEP
pythonsqlpostgresql
sreisman
sreisman

Reputation: 668

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!

Upvotes: 1

Views: 1108

Answers (2)

Carlos Hanson
Carlos Hanson

Reputation: 98

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], str):
             failed_sites[columns[i]] = row[i].strip()

Since I live in a Python 3 world now, I've changed basestring to str in isinstance().

Upvotes: 2

Milen A. Radev
Milen A. Radev

Reputation: 62563

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

Upvotes: 1

Related Questions