[Tutor] How to import python dictionary into MySQL table?

[Peter Otten]

Sean Carolan wrote:

>> I have a database with a table called "systems" that contains an
>> auto-increment id field, as well as fields for each of the keys in
>> mydata.keys().  But I can't seem to get the syntax to import
>> mydata.values() into the table.  I think the problem may be that some
>> of the items in my list are dictionaries or lists...
>>
>> What would be the quickest and most straightforward way to do this?
> 
> I got this working in case anyone else comes across the same problem.
> This function will pull cobbler *.json data into a MySQL database
> table.  The function assumes that you already have a database set up,
> and that you are dumping the data into the "systems" table:
> 
> def importJSON(targetfile):
>     '''
>     Imports JSON data from targetfile into MySQL database table.
>     '''
>     value_list = []
>     rawdata = json.load(open(targetfile))
>     for key in rawdata.keys():
>         strvalue = str(rawdata[key]).replace("'",'"')

Is the above line really needed?

>         value_list.append(strvalue)
>     valtuple = tuple(value_list)
>     sql = "INSERT INTO systems (comment, kickstart,
> name_servers_search, ks_meta, kernel_options_post, image,
> redhat_management_key, power_type, power_user, kernel_options, vi
> rt_file_size, mtime, template_files, gateway, uid, virt_cpus,
> hostname, virt_type, mgmt_classes, power_pass, netboot_enabled,
> profile, virt_bridge, parent, virt_path, interfaces, power_address,
> name_servers, name, owners, ctime, virt_ram, power_id, random_id,
> server, redhat_management_server, depth) VALUES (%s, %s, %s, %s, %s,
> %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s,
> %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
>     cursor.execute(sql, valtuple)

The order of key/value pairs in a dictionary is an implementation detail. I 
think it's dangerous to assume they will always be in sync with the column 
names as provided in the string constant.

Here's what I came up with when I tried to make a generalized version of the 
above:

def add_row(cursor, tablename, rowdict):
    # XXX tablename not sanitized
    # XXX test for allowed keys is case-sensitive

    # filter out keys that are not column names
    cursor.execute("describe %s" % tablename)
    allowed_keys = set(row[0] for row in cursor.fetchall())
    keys = allowed_keys.intersection(rowdict)

    if len(rowdict) > len(keys):
        unknown_keys = set(rowdict) - allowed_keys
        print >> sys.stderr, "skipping keys:", ", ".join(unknown_keys)

    columns = ", ".join(keys)
    values_template = ", ".join(["%s"] * len(keys))

    sql = "insert into %s (%s) values (%s)" % (
        tablename, columns, values_template)
    values = tuple(rowdict[key] for key in keys)
    cursor.execute(sql, values)

filename = ...
tablename = ...
db = MySQLdb.connect(...)
cursor = db.cursor()
with open(filename) as instream:
    row = json.load(instream)
add_row(cursor, tablename, row)

Peter