Create Python dictionary from MS-Access table
<p>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.</p>

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

<pre><code>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
</code></pre>

<p>When I print fieldDomains I get this:</p>

<pre><code>{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'}}
</code></pre>

<p>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:</p>

<pre><code>{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'}}
</code></pre>

<p>You can use <a href="https://docs.python.org/3/library/collections.html#collections.defaultdict" rel="nofollow"><code>defaultdict</code></a> to do that</p>

<pre><code>&gt;&gt;&gt; from collections import defaultdict
&gt;&gt;&gt; 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'}}]
&gt;&gt;&gt; output = defaultdict(dict)
&gt;&gt;&gt; for item in data:
...     for k, v in item.items():
...         output[k].update(v)
&gt;&gt;&gt; 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'}}
</code></pre>

<p><strong>UPDATE</strong> </p>

<p>Since you are getting data in another format, like <code>data2</code> bellow it's better to </p>

<pre><code>&gt;&gt;&gt; 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']]
&gt;&gt;&gt; output2 = defaultdict(dict)
&gt;&gt;&gt; for row in data2:
...     output2[row[0]].update({row[1]: row[2]})
&gt;&gt;&gt; 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'}}
</code></pre>

<p>So basically <code>data2</code> is your <code>rows = cursor.fetchall()</code> and you can replace <code>data2</code> with <code>rows</code> variable</p>

python dictionary