escape string in mysql insert query

Question

am inserting a list of dictionaries in a mysql table with the code below, but having issues on how to escape ',' and other potential error...

data=[{'index': 1, 'kite_size': u'5', 'source': 'spainkiters', 'type': 'kite', 'brand': u'rrd', 'title': u'RRD Religion 2014 10\xb45m usada solo 1 vez', 'id': u'112506', 'kite_model': u'religion', 'location': u' santa pola', 'year': u'2014', 'date_added': u' Lun Ene 27, 2014 9:52 am', 'quality': 5, 'price': None}, {'index': 1, 'kite_size': u'10', 'source': 'spainkiters', 'quality': 5, 'price': u'750', 'title': u'Vendo Kite completo Nobile Fifty 10M 2013', 'id': u'112762', 'kite_model': u'fifty', 'location': u'', 'year': u'2013', 'date_added': u' Mar Feb 11, 2014 5:38 pm', 'type': 'kite', 'brand': u'nobile'}, {'index': 1, 'kite_size': u'7', 'source': 'spainkiters', 'quality': 5, 'price': None, 'title': u'NORTH EVO 7m 2013 !!!!!!!!!!!!!', 'id': u'112789', 'kite_model': u'evo', 'location': u'', 'year': u'2013', 'date_added': u' Mie Feb 12, 2014 9:08 pm', 'type': 'kite', 'brand': u'north'}, {'index': 1, 'kite_size': u'5', 'source': 'spainkiters', 'quality': 4, 'price': u'350', 'title': u'cabrinha convers 2012   5m y tabla slingshot lunaci 1,28cm', 'id': u'112767', 'kite_model': None, 'location': u' pais vasco', 'year': u'2012', 'date_added': u' Mar Feb 11, 2014 6:41 pm', 'type': 'kite', 'brand': u'cabrinha'}, {'index': 1, 'kite_size': u'5', 'source': 'spainkiters', 'quality': 4, 'price': u'350', 'title': u'cabrinha convers 2012   5m y tabla slingshot lunaci 1,28cm', 'id': u'112766', 'kite_model': None, 'location': u' pais vasco', 'year': u'2012', 'date_added': u' Mar Feb 11, 2014 6:33 pm', 'type': 'kite', 'brand': u'cabrinha'}, {'index': 1, 'kite_size': u'8', 'source': 'spainkiters', 'type': 'kite', 'brand': u'flexifoil', 'title': u'flexifoil HADLOW ID 8 metros precio negociable', 'id': u'112512', 'kite_model': u'hadlow', 'location': u' Gran Canaria', 'year': None, 'date_added': u' Lun Ene 27, 2014 5:08 pm', 'quality': 5, 'price': None}, {'index': 1, 'kite_size': u'11', 'source': 'spainkiters', 'quality': 5, 'price': u'500', 'title': u'VENDO SWITCH COMBAT 11M A\xd1O 2013...500E ENVIO INCLUIDO!!', 'id': u'112773', 'kite_model': u'combat', 'location': u'', 'year': u'2013', 'date_added': u' Mar Feb 11, 2014 9:11 pm', 'type': 'kite', 'brand': u'switch'}]
cursor.executemany("INSERT INTO search_kite_products_4 (brand, kite_model, kite_size, year, source, id, location, title, date_added, quality, price, link_id) VALUES (%(brand)s, %(kite_model), %(kite_size)s, %(year)s, %(source)s, %(id)s, %(location)s, %%(title)s%, %%(date_added)s%, %(quality)s, %(price)s, %(id)s)", data)
db.commit()
db.close()

I tried to add an additional % before the fields which could have such characters but it did not work...

 Traceback (most recent call last):
File "C:\Program Files (x86)\JetBrains\PyCharm Community Edition   3.1.1\helpers\pydev\pydevd.py", line 1534, in <module>
debugger.run(setup['file'], None, None)
File "C:\Program Files (x86)\JetBrains\PyCharm Community Edition 3.1.1\helpers\pydev\pydevd.py", line 1145, in run
pydev_imports.execfile(file, globals, locals) #execute the script
File "C:/Users/Joao/PycharmProjects/Olympia/olympiaspt/scripts/test_sql.py", line 30, in <module>
cursor.executemany("INSERT INTO search_kite_products_4 (brand, kite_model, kite_size, year, source, id, location, title, date_added, quality, price, link_id) VALUES (%(brand)s, %(kite_model), %(kite_size)s, %(year)s, %(source)s, %(id)s, %(location)s, %%(title)s%, %%(date_added)s%, %(quality)s, %(price)s, %(id)s)", data)
File "C:\Python27\lib\site-packages\MySQLdb\cursors.py", line 252, in executemany
self.errorhandler(self, exc, value)
File "C:\Python27\lib\site-packages\MySQLdb\connections.py", line 36, in defaulterrorhandler
raise errorclass, errorvalue

ValueError: unsupported format character ',' (0x2c) at index 25

Answer 1

You missed out an s:

%(kite_model),

should be

%(kite_model)s,

You also added % characters too many here:

%%(title)s%, %%(date_added)s%

that should be just:

%(title)s, %(date_added)s

If you need to insert literal % characters around your column data, add these to your Python strings or use SQL functions to explicitly concatenate these server side.