Python nested dictionary with SQL insert

Question

I have generated a very large dictionary after processing an XML file, and I am looking extract from this dictionary and to insert columns and values into my mySQL database table.

I am using Python 3.

The dictionary is nested; here's a simplistic example of what I have:

d ={'Test1'{'TestID':'first','Dev_Type':'this device','Version':'v1_0','Address':'some Address'}
    'Test2'{'TestID':'second','Dev_Type':'that device','Version':'v1_0','Address':'other Address'}
    'Test3'{'TestID','third','Dev_Type':'other device','Version':'v1_0','Address':'another Address'}
}

Essentially I want to iterate over each primary Key in this dictionary (e.g. Test1,Test2,Test3) and extract the secondary keys as a column name tuple and the associated seconday key values as a values tuple, a bit like this:

cols = ('TestID','Dev_Type','Version','Address')
vals = ('first','this device','v1_0','some Address')

On iterating over each primary key I will add the two tuples to my mySQL table using this command:

sql = "INSERT INTO Parameters ({0}) VALUES ({1})".format(', '.join(cols), ', '.join(['%s'] * len(cols)));
try:
    cursor.execute(sql, vals)
except Exception as e:
    print(e)
    pass

Then repeat the process on the next primary key ('Test2').

I have made an initial attempt, but have hard coded the Primary key in this instance:

for k, v in d:
    #Missing appropriate method here
    cols = tuple(d['Test1'].keys())
    vals = tuple(d['Test1'].values())

    sql = "INSERT INTO Parameters ({0}) VALUES ({1})".format(', '.join(cols), ', '.join(['%s'] * len(cols)));
    try:
        cursor.execute(sql, vals)
    except Exception as e:
        pass

connection.close()
return

This is tangential, but you should use the database API's parameter substitution to guard against SQL injections: docs.python.org/2/library/sqlite3.html#sqlite3.Cursor.execute — Joel Cornett
– Joel Cornett, Commented Oct 12, 2016 at 15:40
Did you mean to leave out the : between the keys and values in your example dict? — Joel Cornett
– Joel Cornett, Commented Oct 12, 2016 at 15:42

Eugene Yarmash · Accepted Answer · 2016-10-12 15:49:24Z

2

You can iterate over d.values() and use the .keys() and .values() methods on the nested dictionaries to get the columns and values:

for v in d.values():
    cols = v.keys()
    vals = v.values()

    sql = "INSERT INTO Parameters ({}) VALUES ({})".format(
        ', '.join(cols),
        ', '.join(['%s'] * len(cols)));
    try:
        cursor.execute(sql, vals)
    except Exception as e:
        pass

Note that in Python 3 dict.keys() and dict.values() return views of the dictionary’s keys and values (unlike lists in Python 2).

edited Oct 12, 2016 at 15:49

answered Oct 12, 2016 at 15:19

Eugene Yarmash

152k44 gold badges346 silver badges391 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Moses Koledoye Over a year ago

Is there any guarantee that v.values() will always come in the same order as the list of keys list(v)? Else, you'll be inserting the wrong values in mismatching columns

Eugene Yarmash Over a year ago

There is such guarantee for d.keys() and d.values(). There's no mention of list(d) though.

Moses Koledoye Over a year ago

Yep, that looks better

Mad Physicist Over a year ago

You can do len(v) instead of needlessly converting cols to a list.

Mad Physicist Over a year ago

Good call. I did not realize that the keys and values iterators actually define a length until now. Thanks for pointing that out.

|

Mad Physicist · Accepted Answer · 2016-10-12 15:44:31Z

Iterating over a dictionary actually iterates over the keys. for k in d: is equivalent to for k in d.keys():. You are looking for the values or items methods, which will actually return the key and the value as a tuple:

for k, v in d.items():
    # k will take the values 'Test1', 'Test2', etc.
    # v will take the values of the corresponding nested dicts.

or

for v in d.values():
    # v will take on the values of the nested dicts.

I would recommend using items over values since that way you will have a reference to which primary key (test) you are processing. I am going to go out on a limb here and guess that you will need this for the non-trivial version of your program.

From there, you use v as you are trying to use d[...], since that is exactly what it is:

for k, v in d.items():
    cols = v.keys()
    vals = v.values()

    sql = "INSERT INTO Parameters ({0}) VALUES ({1})".format(
                ', '.join(cols),
                ', '.join(['%s'] * len(v))
    )

    try:
        cursor.execute(sql, vals)
    except Exception as e:
        pass

connection.close()
return

Since v is a nested dictionary, you can get the number of elements in both cols and vals as just len(v).

Collectives™ on Stack Overflow

Python nested dictionary with SQL insert

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related