我一直在使用以下函数来创建一种“更具可读性”(假设是)的格式,以便从Oracle获取数据。下面是函数:
def rows_to_dict_list(cursor):
"""
Create a list, each item contains a dictionary outlined like so:
{ "col1_name" : col1_data }
Each item in the list is technically one row of data with named columns,
represented as a dictionary object
For example:
list = [
{"col1":1234567, "col2":1234, "col3":123456, "col4":BLAH},
{"col1":7654321, "col2":1234, "col3":123456, "col4":BLAH}
]
"""
# Get all the column names of the query.
# Each column name corresponds to the row index
#
# cursor.description returns a list of tuples,
# with the 0th item in the tuple being the actual column name.
# everything after i[0] is just misc Oracle info (e.g. datatype, size)
columns = [i[0] for i in cursor.description]
new_list = []
for row in cursor:
row_dict = dict()
for col in columns:
# Create a new dictionary with field names as the key,
# row data as the value.
#
# Then add this dictionary to the new_list
row_dict[col] = row[columns.index(col)]
new_list.append(row_dict)
return new_list
然后我会像这样使用这个函数:
sql = "Some kind of SQL statement"
curs.execute(sql)
data = rows_to_dict_list(curs)
#
for row in data:
item1 = row["col1"]
item2 = row["col2"]
# Do stuff with item1, item2, etc...
# You don't necessarily have to assign them to variables,
# but you get the idea.
虽然这似乎在不同的压力水平下表现得相当好,但我想知道是否有更有效的或“蟒蛇”方式来做到这一点。
发布于 2012-05-05 04:53:26
还有其他改进要做,但这一点真的让我想到了:
for col in columns:
# Create a new dictionary with field names as the key,
# row data as the value.
#
# Then add this dictionary to the new_list
row_dict[col] = row[columns.index(col)]
除了效率低下之外,在这样的情况下使用index
很容易出现错误,至少在同一项可能在列表中出现两次的情况下是如此。请改用enumerate
:
for i, col in enumerate(columns):
# Create a new dictionary with field names as the key,
# row data as the value.
#
# Then add this dictionary to the new_list
row_dict[col] = row[i]
但这真的是小事一桩。下面是这个函数的一个更紧凑的版本:
def rows_to_dict_list(cursor):
columns = [i[0] for i in cursor.description]
return [dict(zip(columns, row)) for row in cursor]
如果有效的话,请告诉我。
发布于 2013-08-30 04:09:13
为了避免预先转储列表中的所有内容而占用内存,您可以将游标包装在生成器函数中:
def rows_as_dicts(cursor):
""" returns cx_Oracle rows as dicts """
colnames = [i[0] for i in cursor.description]
for row in cursor:
yield dict(zip(colnames, row))
然后按如下方式使用-在迭代时,游标中的行将转换为字典:
for row in rows_as_dicts(cursor):
item1 = row["col1"]
item2 = row["col2"]
发布于 2013-08-09 05:48:34
你不应该对大的结果集使用dict,因为它的内存使用量会很大。我经常使用cx_Oracle,并且没有一个很好的字典游标,这让我很困扰,以至于我为它编写了一个模块。我还必须将Python连接到许多不同的数据库,所以我以一种可以与任何DBAPI2连接器一起使用的方式实现了这一点。
在PyPi DBMS - DataBases Made Simpler上
>>> import dbms
>>> db = dbms.OraConnect('myUser', 'myPass', 'myInstance')
>>> cur = db.cursor()
>>> cur.execute('SELECT * FROM people WHERE id = :id', {'id': 1123})
>>> row = cur.fetchone()
>>> row['last_name']
Bailey
>>> row.last_name
Bailey
>>> row[3]
Bailey
>>> row[0:4]
[1123, 'Scott', 'R', 'Bailey']
https://stackoverflow.com/questions/10455863
复制相似问题