Take the 2-minute tour ×

I have a dictionary of list of dictionaries. Why did I pick this bizarre approach? This is part of a web scraping effort wherein I am storing different sections of a massive webpage in different dictionaries. I have the column names to keep track of the data. I don't have ordered dicts cos' I am on Python 2.6.6.

What is a more efficient way of storing this data (dictionary to mysql)? Every time I scrape the website, I create a temp table to store the data for further processing. I first create a record for the id and then update the columns for that id. Is there a quicker and more efficient way of doing it? Thank you!


import MySQLdb


mydict = {'1': [{'First': 'John', 'Last': 'Doe'}, {'Company': 'Trulia Inc.', 'Title': 'CEO', 'YearsattheCompany': 4}, {'Cell': '216-453-4322', 'Home': None}]}

for key, value in mydict.items():
    id = key
    c.execute("insert into deldictmysql (id) values (%s)" % id)
    for eachdict in value:
        print eachdict
        for finalkey, finalvalue in eachdict.items():
            print finalkey, finalvalue
            if finalvalue:
                query = "update deldictmysql set %s = '%s'"
                c.execute(query % (finalkey, finalvalue))



create table deldictmysql (id integer, first varchar(40), last varchar(40), company varchar(200), title varchar(200), yearsatthecompany integer, cell varchar(20), home varchar(20));


select * from deldictmysql;

"id"    "first" "last"  "company"   "title" "yearsatthecompany" "cell"  "home"
"1" "John"  "Doe"   "Trulia Inc."   "CEO"   "4" "216-453-4322"  ""
share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

Just flat out the dictionaries and insert them:

def encoding(val):
    if isinstance(val, unicode):
        return val.encode('utf-8')
        return str(val)

for id, val in mydict.items():
    data = dict(reduce(lambda x, y: x+y, [v.items() for v in val]) + [('id', id)])
    sorted_keys = sorted(map(str, data.keys()))
    sorted_vals = map(encoding, [v[k] for k in sorted_keys])  # sorted by keys
    format = ', '.join(["'%s'"] * len(sorted_vals))
    c.execute("insert into deldictmysql
               (%s) values (%s)" % (', '.join(sorted_keys), format), sorted_vals)

UPD: for any number and values of keys

share|improve this answer
This assumes the dict will always have the same keys. And the dict's keys have the first letter upper-case (but that's just a detail). –  jadkik94 Jun 26 '12 at 22:06
@jadkik94 Updated. Works for any keys (except MySQL reserved words). This way is even better: escaping and mapping None to NULL will be done by MySQLdb lib. –  Scorpil Jun 26 '12 at 22:22
data = dict(reduce(lambda x, y: x+y, [v.items() for v in val] + [('id', id)])) TypeError: can only concatenate list (not "tuple") to list . It is erring out. –  ThinkCode Jun 27 '12 at 2:08
@ThinkCode yep, id didn't tested it. Now it works. –  Scorpil Jun 27 '12 at 7:24
@ThinkCode if you use 'ignore', your program does not save ® character or any of other non-ascii characters (for example, text in any foreign language). To save all those character try using updated code. You just need to add "encoding" function and replace line with sorted_vals: sorted_vals = map(encoding, [v[k] for k in sorted_keys]) # sorted by keys –  Scorpil Jun 27 '12 at 21:12
show 9 more comments
  1. Consider using something like redis to store this sort of data.
  2. You could use the json module to serialise as text.
share|improve this answer
Data always wants to be sorted out! :) But i agree about NoSQL. –  Scorpil Jun 26 '12 at 22:27
add comment

One improvement you can do is issue one single update query:

for key, value in mydict.iteritems():
    id = key
    for eachdict in value:
        print eachdict
        items = [item for item in eachdict.iteritems() if item[1]]
        query_values = tuple(itertools.chain(*items))
        query = "update deldictmysql set "+", ".join("%s = '%s'" for i in items)
        c.execute(query % query_values)

Or even better, one single insert query:

for key, value in mydict.iteritems():
    id = key
    keys = []
    values = []
    for eachdict in value:
        print eachdict
        for finalkey, finalvalue in eachdict.iteritems():
            if not finalvalue: continue
    keys_part = ", ".join("%s" for k in keys)
    values_part = ", ".join("'%s'" for v in values)
    query_values = keys+[id]+values
    c.execute("insert into deldictmysql (id, "+keys_part+") values (%s"+values_part+")" % query_values)

If you can guarantee that all values in the dict will have the same keys, you could follow the same logic to issue all inserts in one query. Something like "insert into deldictmysql (id, "+keys_part+") values "+(", ".join("(%s"+values_part+")" for i in range(len(mydict)))) % query_values (excuse me for the series of ) in the end :) ) and construct query_values accordingly.

Also, as I noticed you are using Python 2.7 from the print statements, I believe it would be better for you to use iteritems instead of items, which returns an iterator instead of a list, that is convenient if you have lots of items in your dictionary.

And the most important thing in all this is that YOU ARE NOT SANITIZING YOUR QUERIES, and that is bad. The problem here is that you can't use the classic c.execute(sql, params), to bind params, because even the column names are dynamic, which this does not cover. So you will have to sanitize these manually, and also trust the keys, as you probably can't sanitize those.

This will give you this roughly:


when appending values.

Note that this just gives you an idea of what can be done, and will fail in many cases.

Hope it helps.

share|improve this answer
add comment

Your Answer


