输出pyodbc游标结果为python字典

How do I serialize pyodbc cursor output (from .fetchone, .fetchmany or .fetchall) as a Python dictionary?

如何将pyodbc游标输出（来自.fetchone，.fetchmany或.fetchall）序列化为Python字典？

I'm using bottlepy and need to return dict so it can return it as JSON.

我正在使用bottlepy并需要返回dict，因此它可以将其作为JSON返回。

7 个解决方案

#1

If you don't know columns ahead of time, use cursor.description to build a list of column names and zip with each row to produce a list of dictionaries. Example assumes connection and query are built:

如果您不提前知道列，请使用cursor.description构建列名列表并使用每行压缩以生成字典列表。示例假定建立了连接和查询：

>>> cursor = connection.cursor().execute(sql)
>>> columns = [column[0] for column in cursor.description]
>>> print columns
['name', 'create_date']
>>> results = []
>>> for row in cursor.fetchall():
...     results.append(dict(zip(columns, row)))
...
>>> print results
[{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'master'},   
 {'create_date': datetime.datetime(2013, 1, 30, 12, 31, 40, 340000), 'name': u'tempdb'},
 {'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'model'},     
 {'create_date': datetime.datetime(2010, 4, 2, 17, 35, 8, 970000), 'name': u'msdb'}]

#2

Using @Beargle's result with bottlepy, I was able to create this very concise query exposing endpoint:

使用@ Beargle的结果和bottlepy，我能够创建这个非常简洁的查询暴露端点：

@route('/api/query/<query_str>')
def query(query_str):
    cursor.execute(query_str)
    return {'results':
            [dict(zip([column[0] for column in cursor.description], row))
             for row in cursor.fetchall()]}

#3

Here is a short form version you might be able to use

这是您可以使用的简短版本

>>> cursor.select("<your SQL here>")
>>> single_row = dict(zip(zip(*cursor.description)[0], cursor.fetchone()))
>>> multiple_rows = [dict(zip(zip(*cursor.description)[0], row)) for row in cursor.fetchall()]

As you might be aware when you add * to a list you basically strips away the list, leaving the individual list entries as parameters to the function you are calling. By using zip we pick the 1st to n entry and zip them together like a the zipper in you pants.

您可能会注意到，当您向列表添加*时，您基本上会删除列表，将各个列表条目作为参数添加到您正在调用的函数中。通过使用拉链，我们选择第1到第n个入口并将它们拉链在一起，就像你裤子里的拉链一样。

so by using

所以通过使用

zip(*[(a,1,2),(b,1,2)])
# interpreted by python as zip((a,1,2),(b,1,2))

you get

你得到

[('a', 'b'), (1, 1), (2, 2)]

Since description is a tuple with tuples, where each tuple describes the header and the data type for each column, you can extract the first of each tuple with

由于description是一个带元组的元组，其中每个元组描述每个列的标题和数据类型，你可以提取每个元组的第一个元组

>>> columns = zip(*cursor.description)[0]

equivalent to

相当于

>>> columns = [column[0] for column in cursor.description]

#4

Mainly going off @Torxed response, I created a full generalised set of functions to find the schema and data into a dictionary:

主要是关闭@Torxed响应，我创建了一个完整的通用函数集来查找字典中的模式和数据：

def schema_dict(cursor):
    cursor.execute("SELECT sys.objects.name, sys.columns.name FROM sys.objects INNER JOIN sys.columns ON sys.objects.object_id = sys.columns. object_id WHERE sys.objects.type = 'U';")
    schema = {}

    for it in cursor.fetchall():
        if it[0] not in schema:
            schema[it[0]]={'scheme':[]}
        else:
            schema[it[0]]['scheme'].append(it[1])

    return schema


def populate_dict(cursor, schema):
    for i in schema.keys():
        cursor.execute("select * from {table};".format(table=i))

        for row in cursor.fetchall():
            colindex = 0

            for col in schema[i]['scheme']:
                if not 'data' in schema[i]:
                    schema[i]['data']=[]

                schema[i]['data'].append(row[colindex])
                colindex += 1

    return schema

def database_to_dict():
    cursor = connect()
    schema = populate_dict(cursor, schema_dict(cursor))

Feel free to go all code-golf on this to reduce the lines; but in the meantime, it works!

随意使用所有代码 - 高尔夫球来减少线条;但与此同时，它的确有效！

;)

;）

#5

I know this question is old, but it helped me figure out how to do what I needed, which is slightly different than what OP was asking for, so I thought I'd share, to help anyone else that needs what I needed: If you want to fully generalize a routine that performs SQL Select Queries, but you need to reference the results by an index number, not a name, you can do this with a list of lists instead of a dictionary. Each row of returned data is represented in the returned list as a list of field(column) values. The column names can be provided as the first entry of the returned list, so parsing the returned list in the calling routine can be really easy and flexible. In this way, the routine doing the database call doesn't need to know anything about the data that it's handling. Here is such a routine:

我知道这个问题已经过时了，但它帮助我弄清楚如何做我需要的东西，这与OP的要求略有不同，所以我想我会分享，帮助其他需要我需要的人：如果您希望完全概括执行SQL Select Queries的例程，但您需要通过索引号而不是名称来引用结果，您可以使用列表而不是字典来执行此操作。每行返回的数据在返回的列表中表示为字段（列）值的列表。列名可以作为返回列表的第一个条目提供，因此在调用例程中解析返回的列表可以非常简单和灵活。通过这种方式，执行数据库调用的例程不需要知道它正在处理的数据。这是一个例程：

    def read_DB_Records(self, tablename, fieldlist, wherefield, wherevalue) -> list:

        DBfile = 'C:/DATA/MyDatabase.accdb'
        # this connection string is for Access 2007, 2010 or later .accdb files
        conn = pyodbc.connect(r'Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ='+DBfile)
        cursor = conn.cursor()

        # Build the SQL Query string using the passed-in field list:
        SQL = "SELECT "
        for i in range(0, len(fieldlist)):
            SQL = SQL + "[" + fieldlist[i] + "]"
            if i < (len(fieldlist)-1):
                SQL = SQL + ", "
        SQL = SQL + " FROM " + tablename

        # Support an optional WHERE clause:
        if wherefield != "" and wherevalue != "" :
            SQL = SQL + " WHERE [" + wherefield + "] = " + "'" + wherevalue + "';"

        results = []    # Create the results list object

        cursor.execute(SQL) # Execute the Query

        # (Optional) Get a list of the column names returned from the query:
        columns = [column[0] for column in cursor.description]
        results.append(columns) # append the column names to the return list

        # Now add each row as a list of column data to the results list
        for row in cursor.fetchall():   # iterate over the cursor
            results.append(list(row))   # add the row as a list to the list of lists

        cursor.close()  # close the cursor
        conn.close()    # close the DB connection

        return results  # return the list of lists

#6

Assuming you know you column names! Also, here are three different solutions,
you probably want to look at the last one!

假设你知道列名！此外，这里有三种不同的解决方案，你可能想看看最后一个！

colnames = ['city', 'area', 'street']
data = {}

counter = 0
for row in x.fetchall():
    if not counter in data:
        data[counter] = {}

    colcounter = 0
    for colname in colnames:
        data[counter][colname] = row[colcounter]
        colcounter += 1

    counter += 1

That's an indexed version, not the most beautiful solution but it will work. Another would be to index the column name as dictionary key with a list within each key containing the data in order of row number. by doing:

这是一个索引版本，不是最美丽的解决方案，但它会起作用。另一种方法是将列名称索引为字典键，每个键中的列表包含按行号顺序排列的数据。通过做：

colnames = ['city', 'area', 'street']
data = {}

for row in x.fetchall():
    colindex = 0
    for col in colnames:
        if not col in data:
            data[col] = []
        data[col].append(row[colindex])
        colindex += 1

Writing this, i understand that doing for col in colnames could be replaced by for colindex in range(0, len()) but you get the idea. The later example tho would be useful when not fetching all data, but one row at a time, for instance:

写这个，我明白在colnames中为col进行替换可以替换为range（0，len（））中的colindex，但是你明白了。后面的示例在不获取所有数据时非常有用，但一次只能获取一行，例如：

Using dict for each row of data

def fetchone_dict(stuff):
    colnames = ['city', 'area', 'street']
    data = {}

    for colindex in range(0, colnames):
        data[colnames[colindex]] = stuff[colindex]
    return data

row = x.fetchone()
print fetchone_dict(row)['city']

---

Getting tablenames (i think.. thanks to Foo Stack):
a more direct solution from beargle below!

获取桌面名称（我认为......感谢Foo Stack）：下面是一个更直接的解决方案！

cursor.execute("SELECT sys.objects.name, sys.columns.name FROM sys.objects INNER JOIN sys.columns ON sys.objects.object_id = sys.columns. object_id WHERE sys.objects.type = 'U';")
schema = {}
for it in cursor.fetchall():
    if it[0] in schema:
       schema[it[0]].append(it[1])
    else:
        schema[it[0]] = [it[1]]

#7

I like @bryan and @foo-stack answers. If you are working with postgresql and you are using psycopg2 you could use some goodies from psycopg2 to achieve the same by specifying the cursorfactory being a DictCursor when creating your cursor from the connection, like this:

我喜欢@bryan和@ foo-stack的答案。如果您正在使用postgresql并且您正在使用psycopg2，您可以使用来自psycopg2的一些好东西通过在从连接创建光标时指定cursorfactory是DictCursor来实现相同的目的，如下所示：

cur = conn.cursor( cursor_factory=psycopg2.extras.DictCursor )

cur = conn.cursor（cursor_factory = psycopg2.extras.DictCursor）

So now you can execute your sql query and you'll get a dictionary to fetch your results, without the need to map them by hand.

所以现在你可以执行你的sql查询，你会得到一个字典来获取你的结果，而不需要手工映射它们。

cur.execute( sql_query )
results = cur.fetchall()

for row in results:
    print row['row_no']

Please note that you'll have to import psycopg2.extras for that to work.

请注意，您必须导入psycopg2.extras才能正常工作。