从runtim中指定的列动态生成字典

Question

有人告诉我，我需要使用映射表，而不是我制作的硬编码字典。必须有比下面的代码更好的方法才能将一个3列表放入字典？你知道吗

映射表

AgentGrp, Property, TimeZone  #Headers
HollyWoodAgent  Sunset  PST
UnionSquareAgent UnionSquare PST

变成以下词典：

{'HollyWoodAgent': ['Sunset', 'PST'], 'UnionSquareAgent': ['UnionSquare', 'PST']}

代码：

import pandas as pd
import pyodbc
import datetime
import sys
import csv

VipAgent = "{"

finalSql = "SELECT  agentgrp, property, timezone FROM sandbox_dev.agentgrp_map;"

colcnt = 0

try:

    conn = pyodbc.connect("DSN=Dev")

    cursor = conn.cursor()
    cursor.execute(finalSql)
    for row in cursor.fetchall():
        VipAgent += "'" + row.prop + "VipAgent':['" + row.prop + "','" + row.tz + "'],"
        colcnt = colcnt + 1
        if(colcnt==3):
            VipAgent = VipAgent + "\n"
            colcnt = 0

except my.Error as e:
    print(e)

VipAgent = VipAgent[:-1] + "}"

Dict = eval(VipAgent)

print(Dict)

我确实得到了预期的值。一定有更好的办法。你知道吗

Answer 1

我们假设您将“映射表”从一个文件读入一个类似于这个的Python列表

item_map = ['AgentGrp', 'Property', 'TimeZone']

执行SELECT查询之后

cursor.execute(finalSql)

然后你可以像这样构建你的dict：

result_dict = {}
while (True):
    row = cursor.fetchone()
    if (row):
        result_dict[row.__getattribute__(item_map[0])] = \
                [row.__getattribute__(x) for x in item_map[1:]]
    else:
        break
print(result_dict)
# {'HollyWoodAgent': ['Sunset', 'PST'], 'UnionSquareAgent': ['UnionSquare', 'PST']}

诀窍是使用row.__getattribute__，例如row.__getattribute__('column_name')而不是硬编码row.column_name。你知道吗