python字典添加列表,将Python字典/列表插入到SQL数据库中最有效的方法是什么？...

Imagine you have a key-value Python dictionary (or list) with larger amounts of items. Let’s say you’re reading a bigger JSON file and you’d like to store it’s contents into MySQL table with keys as names of columns and values as values itself.

JSON Example:

"display_location": {

"city":"Bratislava",

"state_name":"Slovakia",

"country_iso3166":"SK",

"latitude":"48.20000076",

"longitude":"17.20000076",

}

Then it is quite inefficient to write SQL insert like this manually:

INSERT INTO TABLE (city, state_name, country_iso3166, latitude, longitude) VALUES('%s','%s','%s','%s','%s')

% (Bratislava, Slovakia, SK, 48.20000076, 17.20000076);

(Well, it's ok with five values, but imagine there are for example five hundreds of them.)

Is there any Python class / method for effective and short-stringed SQL insert? I wrote this piece of code:

for key,value in list.iteritems():

value_type = type(value)

if value_type is unicode:

vars_to_sql.append(value.encode('ascii', 'ignore'))

keys_to_sql.append(key.encode('ascii', 'ignore'))

else:

vars_to_sql.append(value)

keys_to_sql.append(key)

keys_to_sql = ', '.join(keys_to_sql)

After that the insert looks much more simple:

INSERT INTO conditions_bratislava(%s) VALUES %r" % (keys_to_sql, tuple(vars_to_sql),)

There can be thousands of values and you will still be fine with this one insert statement.

Note that condition which will decode Unicode strings, so you won’t have “u” letters before the each value.

So, is there any more effective and prepared class or method how to insert many values in the same simple approach with short INSERT string?

解决方案

If your data is structured like that, it would lend itself more towards a document orientated database (Mongo/Couch etc...)

You can get away with something like this... I think using repr is being a little too clever...

insert_sql = 'INSERT INTO conditions_bratislava(%s) values(%s)'

cols = ', '.join(somedict)

vals = ', '.join('?' * len(somedict)) # or whatever qparam is required

to_execute = insert_sql % (cols, vals)

some_cursor.execute(to_execute, somedict.values())

On a side note:

value_type = type(value)

if value_type is unicode:

Should be written as:

if isinstance(value, unicode):