What is the most effective way of Inserting Python

Question

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?

Answer 1

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):