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
_______________________________________________
Tutor maillist - Tutor@python.org
To unsubscribe or change subscription options:
http://mail.python.org/mailman/listinfo/tutor
