在MySQL插入查询中转义字符串
我正在用下面的代码把一个字典列表插入到mysql表里，但遇到了一些问题，比如怎么处理逗号（','）和其他可能出错的字符...
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()
我尝试在可能包含这些字符的字段前面加一个额外的%符号，但没有成功...
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: 不支持的格式字符 ',' (0x2c)，在索引25的位置
1 个回答
1
你漏掉了一个 s：
%(kite_model),
应该是
%(kite_model)s,
你这里还多加了太多 % 字符：
%%(title)s%, %%(date_added)s%
应该只是：
%(title)s, %(date_added)s
如果你需要在你的列数据周围插入实际的 % 字符，可以把这些加到你的 Python 字符串里，或者使用 SQL 函数在服务器端明确地连接这些。