query from postgresql using python as dictionary

Question

I'm using Python 2.7 and postgresql 9.1. Trying to get dictionary from query, I've tried the code as described here: http://wiki.postgresql.org/wiki/Using_psycopg2_with_PostgreSQL

import psycopg2
import psycopg2.extras
conn = psycopg2.connect("dbname=mydb host=localhost user=user password=password")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute ("select * from port")
type(cur.fetchall())

It is printing the next answer:

<type 'list'>

printing the item itself, show me that it is list. The excepted answer was dictionary.

Edit:

Trying the next:

ans = cur.fetchall()[0]
print ans
print type(ans)

returns

[288, 'T', 51, 1, 1, '192.168.39.188']
<type 'list'>

print ans is normal - it must be like list. What result of type(cur.fetchone()) ? — Andrey Shokhin
– Andrey Shokhin, Commented Jan 16, 2014 at 9:48
>>> type(cur.fetchone()) <class 'psycopg2.extras.DictRow'> What that interesting is that i can use field name instead of the number. (ans[0] is like ans['id']). but converting it to json keeps me with the list. — Guy Dafny
– Guy Dafny, Commented Jan 16, 2014 at 9:51
If you want json - convert DictRow to dict and to json after this: JSON = json.dumps(dict(cursor.fecthone())) — Andrey Shokhin
– Andrey Shokhin, Commented Jan 16, 2014 at 9:55
If you need do it in loop use simple cursor iterator: for record in cursor: ... — Andrey Shokhin
– Andrey Shokhin, Commented Jan 16, 2014 at 9:58

Guy Dafny · Accepted Answer · 2014-01-16 10:05:40Z

51

Tnx a lot Andrey Shokhin ,

full answer is:

#!/var/bin/python 
import psycopg2
import psycopg2.extras
conn = psycopg2.connect("dbname=uniart4_pr host=localhost user=user password=password")
cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cur.execute ("select * from port")
ans =cur.fetchall()
ans1 = []
for row in ans:
    ans1.append(dict(row))

print ans1  #actually it's return

answered Jan 16, 2014 at 10:05

Guy Dafny

1,8491 gold badge15 silver badges26 bronze badges

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

1 Comment

xanjay Over a year ago

I did ans1 = [dict(row) for row in ans] but the rows are altered (not ordered) when I print(ans1). Isn't the same when using list comprehension ?

adrenalin · Accepted Answer · 2019-06-09 07:02:00Z

19

It's normal: when you call .fetchall() method returns list of tuples. But if you write

type(cur.fetchone())

it will return only one tuple with type:

<class 'psycopg2.extras.DictRow'>

After this you can use it as list or like dictionary:

cur.execute('SELECT id, msg FROM table;')
rec = cur.fetchone()
print rec[0], rec['msg']

You can also use a simple cursor iterator:

res = [json.dumps(dict(record)) for record in cursor] # it calls .fetchone() in loop

edited Jun 9, 2019 at 7:02

adrenalin

1,6482 gold badges15 silver badges25 bronze badges

answered Jan 16, 2014 at 9:40

Andrey Shokhin

12.4k1 gold badge19 silver badges15 bronze badges

1 Comment

Guy Dafny Over a year ago

print type(cur.fetchall()[0]) is list as well, and printing the result show it's list

Amateur · Accepted Answer · 2019-09-20 16:09:39Z

15

Perhaps to optimize it further we can have

#!/var/bin/python 
import psycopg2
import psycopg2.extras

def get_dict_resultset(sql):
    conn = psycopg2.connect("dbname=pem host=localhost user=postgres password=Drupal#1008")
    cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
    cur.execute (sql)
    ans =cur.fetchall()
    dict_result = []
    for row in ans:
        dict_result.append(dict(row))
    return dict_result

sql = """select * from tablename"""
return get_dict_resultset(sql)

edited Sep 20, 2019 at 16:09

Amateur

1132 silver badges5 bronze badges

answered May 8, 2015 at 12:28

Pralhad Narsinh Sonar

1,4541 gold badge14 silver badges23 bronze badges

Comments

Preston · Accepted Answer · 2021-06-23 07:11:38Z

If you don't want to use a psycopg2.extras.DictCursor you can create a list of dictionaries for the results using cursor.description:

# connect
connection = psycopg2.connect()
cursor = connection.cursor()

# query
cursor.execute("SELECT * FROM myTable")

# transform result
columns = list(cursor.description)
result = cursor.fetchall()

# make dict
results = []
for row in result:
    row_dict = {}
    for i, col in enumerate(columns):
        row_dict[col.name] = row[i]
    results.append(row_dict)

# display
print(result)

I use the following function fairly regularly:

def select_query_dict(connection, query, data=[]):
    """
    Run generic select query on db, returns a list of dictionaries
    """
    logger.debug('Running query: {}'.format(query))

    # Open a cursor to perform database operations
    cursor = connection.cursor()
    logging.debug('Db connection succesful')

    # execute the query
    try:
        logger.info('Running query.')
        if len(data):
            cursor.execute(query, data)
        else:
            cursor.execute(query)
        columns = list(cursor.description)
        result = cursor.fetchall()
        logging.debug('Query executed succesfully')
    except (Exception, psycopg2.DatabaseError) as e:
        logging.error(e)
        cursor.close()
        exit(1)

    cursor.close()

    # make dict
    results = []
    for row in result:
        row_dict = {}
        for i, col in enumerate(columns):
            row_dict[col.name] = row[i]
        results.append(row_dict)

    return results

Makyen · Accepted Answer · 2020-09-01 20:47:29Z

11

There is a built in solution to get your result as a collection of dictionary:

from psycopg2.extras import RealDictCursor
cur = conn.cursor(cursor_factory=RealDictCursor)

edited Sep 1, 2020 at 20:47

Makyen♦

33.6k12 gold badges94 silver badges128 bronze badges

answered Sep 1, 2020 at 19:24

Tzahi

1111 silver badge3 bronze badges

2 Comments

Franco GIL Over a year ago

I make use of this one, to fetch all the rows I use this line: data = [dict(row) for row in cur.fetchall()]

Franco GIL Over a year ago

I didn't check complexity time, or make a benchmark using other solutions but at least work like a charm for me.

Amateur · Accepted Answer · 2019-09-20 15:56:42Z

9

In addition to just return only the query results as a list of dictionaries, I would suggest returning key-value pairs (column-name:row-value). Here my suggestion:

import psycopg2
import psycopg2.extras

    conn = None
    try:
        conn = psycopg2.connect("dbname=uniart4_pr host=localhost user=user password=password")

        with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cursor:
            cursor.execute("SELECT * FROM table")
            column_names = [desc[0] for desc in cursor.description]
            res = cursor.fetchall()
            cursor.close()
        return map(lambda x: dict(zip(column_names, x)), res))

    except (Exception, psycopg2.DatabaseError) as e:
        logger.error(e)
    finally:
        if conn is not None:
            conn.close()

answered Sep 20, 2019 at 15:56

Amateur

1132 silver badges5 bronze badges

1 Comment

shadab.tughlaq Over a year ago

There is an extra ) perhaps at return map(lambda x: dict(zip(column_names, x)), res))

dheeraj .A · Accepted Answer · 2019-02-14 21:34:14Z

For me when I convert the row to dictionary failed (solutions mentioned by others)and also could not use cursor factory. I am using PostgreSQL 9.6.10, Below code worked for me but I am not sure if its the right way to do it.

    def convert_to_dict(columns, results):
    """
    This method converts the resultset from postgres to dictionary
    interates the data and maps the columns to the values in result set and converts to dictionary
    :param columns: List - column names return when query is executed
    :param results: List / Tupple - result set from when query is executed
    :return: list of dictionary- mapped with table column name and to its values
    """

    allResults = []
    columns = [col.name for col in columns]
    if type(results) is list:
        for value in results:
            allResults.append(dict(zip(columns, value)))
        return allResults
    elif type(results) is tuple:
        allResults.append(dict(zip(columns, results)))
        return allResults

Way to use it:

conn = psycopg2.connect("dbname=pem host=localhost user=postgres,password=Drupal#1008")
cur = conn.cursor()
cur.execute("select * from tableNAme")

resultset = cursor.fetchall()
result = convert_to_dict(cursor.description, resultset)
print(result)

resultset = cursor.fetchone()
result = convert_to_dict(cursor.description, resultset)
print(result)

Rui Costa · Accepted Answer · 2020-02-28 19:29:25Z

Contents of './config.py'

        #!/usr/bin/python
        PGCONF = {
            "user": "postgres", 
            "password": "postgres", 
            "host": "localhost", 
            "database": "database_name"
        }

contents of './main.py'

        #!/usr/bin/python

        from config import PGCONF

        import psycopg2
        import psycopg2.extras

        # open connection
        conn = psycopg2.connect(**PGCONF)
        cur = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)

        # declare lambda function
        fetch_all_as_dict = lambda cursor: [dict(row) for row in cursor]

        # execute any query of your choice
        cur.execute("""select * from table_name limit 1""")

        # get all rows as list of dicts
        print(fetch_all_as_dict(cur))

        # close cursor and connection
        cur.close()
        conn.close()

Collectives™ on Stack Overflow

query from postgresql using python as dictionary

8 Answers 8

1 Comment

1 Comment

Comments

Comments

2 Comments

1 Comment

Comments

Contents of './config.py'

contents of './main.py'

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

1 Comment

1 Comment

Comments

Comments

2 Comments

1 Comment

Comments

Contents of './config.py'

contents of './main.py'

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related