Creating Data Dictionary from SQL Server Database

Question

I would like to create a Data Dictionary using Python from the SQL Server Database. I've written the following code but it gives me the following result,

[OrderedDict([('SystemID', '1'), ('FileID', 1), ('DateTime', None)])]

But I want my result to be as below,

File = { "SystemID": "1", "FileID": "1", "DateTime": None }

I have the following code right now

import pyodbc  
import collections  
cnxn = pyodbc.connect(trusted_Connection='yes', driver = '{SQL Server}', server = 'localhost', database = 'myDB')  
cursor = cnxn.cursor()  
cursor.execute("SELECT SystemID, FileID, EventDateTime FROM dbo.File")  
rows = cursor.fetchall()  
objects_list = []  
for row in rows:  
    d = collections.OrderedDict()  
    d['SystemID'] = row.SystemID  
    d['FileID'] = row.FileID  
    d['DateTime'] = row.EventDateTime  
    objects_list.append(d)  
print objects_list  
cnxn.close()

This should do the trick: replace `print objects_list` with `print dict(d)` — Hans Then, Aug 01 '13 at 08:30
Thanks, that worked. And forgive me for being naive. How do I give a name to the dictionary? I mean I would need the output as, File = { "SystemID": "1", "FileID": "1", "DateTime": None } — user1345260, Aug 01 '13 at 08:45

score 0 · Answer 1 · answered Jan 03 '14 at 05:20

If you use dict(d), you coerce the ordered dictionary to a dict, but there is nothing preventing Python from reordering it, Python dict's aren't ordered.

Another problem with this is you're looping through the rows, and then just taking the last item, probably since you're developing and intend to move it back into the loop as I show below, but otherwise it doesn't make sense. One shouldn't abuse variables escaping loops in python.

May I suggest:

# Implicit concatenation for longish string, (see PEP 8)
output_format = ('File = {{ "SystemID": "{SystemID}", '
                 '"FileID": "{FileID}", "DateTime": {DateTime} }}')
for row in rows:  
    d = {}
    d['SystemID'] = row.SystemID  
    d['FileID'] = row.FileID  
    d['DateTime'] = row.EventDateTime    
    # expand the dictionary as keyword args for the str.format method
    print output.format(**d)

But then you probably shouldn't be printing in your final product either.

Creating Data Dictionary from SQL Server Database

1 Answers1