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

What error are you seeing? What database adapter are you using? — Martijn Pieters
– Martijn Pieters, Commented Apr 20, 2014 at 18:11
mysql database and the error message is raise errorclass, errorvalue ValueError: unsupported format character ',' (0x2c) at index 25 — user2950162
– user2950162, Commented Apr 20, 2014 at 18:13
You should not escape those characters at all. You should use parameters. — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Apr 20, 2014 at 18:21
@LasseV.Karlsen: The syntax posted uses SQL parameters; which is why it is puzzling. But not all database adapters support the same SQL parameter syntax (the Python database adapter standard allows 4 variants). — Martijn Pieters
– Martijn Pieters, Commented Apr 20, 2014 at 18:24
Does using "SQL Parameters" actually result in parameters being used, or will it end up in values.append(fmt % self._process_params(params)), as per bugs.mysql.com/bug.php?id=67146? — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Apr 20, 2014 at 18:31

Martijn Pieters · Accepted Answer · 2014-04-20 18:34:33Z

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.

answered Apr 20, 2014 at 18:34

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2950162 Over a year ago

Thanks Martijn! I had looked over it countless times and did not spot the missing s... fixed as you said and is working perfectly! Many thanks!!!

Collectives™ on Stack Overflow

escape string in mysql insert query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related