使用 python 作为字典从 postgresql 查询

query from postgresql using python as dictionary

提问by Guy Dafny

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

我正在使用 Python 2.7 和 postgresql 9.1。尝试从查询中获取字典，我尝试了此处描述的代码：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'>

采纳答案by Andrey Shokhin

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

这是正常的：当您调用.fetchall()方法时返回元组列表。但是如果你写

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

回答by Guy Dafny

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

回答by Pralhad Narsinh Sonar

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)

回答by dheeraj .A

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.

对我来说，当我将行转换为字典失败（其他人提到的解决方案）并且也无法使用游标工厂。我正在使用 PostgreSQL 9.6.10，下面的代码对我有用，但我不确定它是否是正确的方法。

    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)

回答by JulienM

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()

回答by Rui Costa

Contents of './config.py'

'./config.py' 的内容

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

contents of './main.py'

'./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()