如何用cx_Oracle制作字典列表?

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

  • 回答 (2)
  • 关注 (0)
  • 查看 (22)

我一直在使用下面的函数来创建一个“更具可读性”(据称是)从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.

虽然这种情况在不同程度的压力下表现相当好,但我想知道是否有更高效的或“pythonic”的方式来做到这一点。

提问于
用户回答回答于

还有其他的改进,但是这真的让我跳出来了:

    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]

让我知道这是否有效。

用户回答回答于

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"]

扫码关注云+社区