David Bailey David Bailey - 2 months ago 15
Python Question

Create Python dictionary from MS-Access table

I have an MS-Access table that has 6 columns. I want to extract the first column and use it as the key and then extract the 2nd and 3rd columns and use them as values in a Python dictionary. There are multiple values for one key.

This is what I have so far but I can't figure out what to do next:

import numpy
import pyodbc

access_database_file = r"C:\Users\david\Documents\\LISTS.mdb"
ODBC_CONN_STR = r"DRIVER={Microsoft Access Driver (*.mdb)};DBQ=%s;" % access_database_file

conn = pyodbc.connect(ODBC_CONN_STR)
cursor = conn.cursor()
cursor.execute("select * from LISTS")
print "..processing..."
rows = cursor.fetchall()

fieldDomains = {}

for row in rows:

k = row[0]

v1 = row[1]

v2 = row[2]

fieldDomains = {k: {v1: v2}}

print fieldDomains


When I print fieldDomains I get this:

{u'MAIN_VW': {u'PRESSURE_ZONE_NUM': u'LU_PRESSURE_ZONE_VW'}}
{u'MAIN_VW': {u'DIAMETER': u'LU_MAIN_DIAMR_LK_MV'}}
{u'MAIN_VW': {u'MATERIAL': u'LU_MAIN_MATRL_LK_MV'}}
{u'WATER_VW': {u'SUBTYPE': u'LU_WATER_SUBTYP_LK_MV'}}
{u'WATER_VW': {u'IS_RESTRAINED': u'LU_YES_NO_LK'}}
{u'PIPE_VW': {u'IS_TIE_IN': u'LU_YES_NO_LK'}}
{u'PIPE_VW': {u'ORIGIN': u'LU_PIPE_ORIGN_LK_MV'}}


I want need to merge these separate dictionaries into one large dictionary - but i'm not sure how to do this in my current script? This is what I want my output to be this:

{u'MAIN_VW': {u'PRESSURE_ZONE_NUM': u'LU_PRESSURE_ZONE_VW', u'DIAMETER': u'LU_MAIN_DIAMR_LK_MV', u'MATERIAL': u'LU_MAIN_MATRL_LK_MV'}, u'WATER_VW': {u'SUBTYPE': u'LU_WATER_SUBTYP_LK_MV', u'IS_RESTRAINED': u'LU_YES_NO_LK', u'PIPE_VW': {u'IS_TIE_IN': u'LU_YES_NO_LK', u'ORIGIN': u'LU_PIPE_ORIGN_LK_MV'}}

Answer

You can use defaultdict to do that

>>> from collections import defaultdict
>>> data = [{u'MAIN_VW': {u'PRESSURE_ZONE_NUM': u'LU_PRESSURE_ZONE_VW'}},
{u'MAIN_VW': {u'DIAMETER': u'LU_MAIN_DIAMR_LK_MV'}},
{u'MAIN_VW': {u'MATERIAL': u'LU_MAIN_MATRL_LK_MV'}},
{u'WATER_VW': {u'SUBTYPE': u'LU_WATER_SUBTYP_LK_MV'}},
{u'WATER_VW': {u'IS_RESTRAINED': u'LU_YES_NO_LK'}},
{u'PIPE_VW': {u'IS_TIE_IN': u'LU_YES_NO_LK'}},
{u'PIPE_VW': {u'ORIGIN': u'LU_PIPE_ORIGN_LK_MV'}}]
>>> output = defaultdict(dict)
>>> for item in data:
        for k, v in item.items():
            output[k].update(v)
>>> dict(output)
{'MAIN_VW': {'DIAMETER': 'LU_MAIN_DIAMR_LK_MV',
'MATERIAL': 'LU_MAIN_MATRL_LK_MV',
  'PRESSURE_ZONE_NUM': 'LU_PRESSURE_ZONE_VW'},
 'PIPE_VW': {'IS_TIE_IN': 'LU_YES_NO_LK', 'ORIGIN': 'LU_PIPE_ORIGN_LK_MV'},
 'WATER_VW': {'IS_RESTRAINED': 'LU_YES_NO_LK',
  'SUBTYPE': 'LU_WATER_SUBTYP_LK_MV'}}

UPDATE

Since you are getting data in another format, like data2 bellow it's better to

>>> data2 = [[u'MAIN_VW', u'PRESSURE_ZONE_NUM', u'LU_PRESSURE_ZONE_VW'],
[u'MAIN_VW', u'DIAMETER', u'LU_MAIN_DIAMR_LK_MV'],
[u'MAIN_VW', u'MATERIAL', u'LU_MAIN_MATRL_LK_MV'],
[u'WATER_VW', u'SUBTYPE', u'LU_WATER_SUBTYP_LK_MV'],
[u'WATER_VW', u'IS_RESTRAINED', u'LU_YES_NO_LK'],
[u'PIPE_VW', u'IS_TIE_IN', u'LU_YES_NO_LK'],
[u'PIPE_VW', u'ORIGIN', u'LU_PIPE_ORIGN_LK_MV']]
>>> output2 = defaultdict(dict)
>>> for row in data2:
        output2[row[0]].update({row[1]: row[2]})
>>> dict(output2)
{'MAIN_VW': {'DIAMETER': 'LU_MAIN_DIAMR_LK_MV',
  'MATERIAL': 'LU_MAIN_MATRL_LK_MV',
  'PRESSURE_ZONE_NUM': 'LU_PRESSURE_ZONE_VW'},
 'PIPE_VW': {'IS_TIE_IN': 'LU_YES_NO_LK', 'ORIGIN': 'LU_PIPE_ORIGN_LK_MV'},
 'WATER_VW': {'IS_RESTRAINED': 'LU_YES_NO_LK',
  'SUBTYPE': 'LU_WATER_SUBTYP_LK_MV'}}

So basically data2 is your rows = cursor.fetchall() and you can replace data2 with rows variable