将pyodbc游标结果输出为python字典
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16519385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Output pyodbc cursor results as python dictionary
提问by Foo Stack
How do I serialize pyodbc cursor output (from .fetchone, .fetchmanyor .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 返回。
采纳答案by Bryan
If you don't know columns ahead of time, use Cursor.descriptionto build a list of column names and zipwith 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'}]
回答by Torxed
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 colnamescould 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:
写这个，我知道做for col in colnames可以被取代，for colindex in range(0, len())但你明白了。后面的示例在不获取所有数据而是一次获取一行时很有用，例如：
Using dict for each row of data
对每一行数据使用dict
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 solutionfrom 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]]
回答by Foo Stack
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!
随意使用所有代码高尔夫以减少线条；但与此同时，它起作用了！
;)
;)
回答by Foo Stack
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()]}
回答by Tommy Strand
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.
正如您可能知道的，当您将 * 添加到列表时，您基本上会删除列表，将单个列表条目作为您正在调用的函数的参数。通过使用 zip，我们选择第 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]
回答by Grimravus
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 选择查询的例程，但您需要通过索引号而不是名称来引用结果，您可以使用列表而不是字典来实现。返回数据的每一行在返回列表中表示为字段（列）值列表。列名可以作为返回列表的第一个条目提供，因此在调用例程中解析返回的列表可以非常简单和灵活。通过这种方式，执行数据库调用的例程不需要知道关于它正在处理的数据的任何信息。这是这样一个例程：
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
回答by matthaeus
I like @bryan and @foo-stack answers. If you are working with postgresql and you are using psycopg2you could use some goodies from psycopg2to achieve the same by specifying the cursorfactory being a DictCursorwhen creating your cursor from the connection, like this:
我喜欢@bryan 和@foo-stack 的答案。如果您正在使用 postgresql 并且正在使用psycopg2，则可以使用psycopg2 中的一些好东西来实现相同的目的，方法是DictCursor在从连接创建游标时指定 cursorfactory 为 a ，如下所示：
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.extrasfor that to work.
请注意，您必须这样import psycopg2.extras做才能工作。
回答by Kevin Campbell
For situations where the cursor is not available - for example, when the rows have been returned by some function call or inner method, you can still create a dictionary representation by using row.cursor_description
对于游标不可用的情况 - 例如，当某些函数调用或内部方法返回行时，您仍然可以使用 row.cursor_description 创建字典表示
def row_to_dict(row):
return dict(zip([t[0] for t in row.cursor_description], row))
