如何解决pymysql.err.ProgrammingError: 1064 -在mysql上保存数据？
如何解决pymysql.err.ProgrammingError: 1064 -在mysql上保存数据？
提问于 2018-08-03 02:59:12
我正在尝试将用scrapy抓取的数据保存到mysql中。但是，我有这些问题：
- 不再支持
MySQLdb。所以，我必须使用
import pymysql
settings.py文件上的pymysql.install_as_MySQLdb()
- On python 3
%s已弃用，我必须对以下code:
使用.格式
def close(self, reason):
csv_file = max(glob.iglob('*.csv'), key=os.path.getctime)
mydb = MySQLdb.connect(host='localhost',
user='demo',
passwd='123456',
db='testdb')
cursor = mydb.cursor()
csv_data = csv.reader(open(csv_file))
row_count = 0
for row in csv_data:
if row_count != 0:
cursor.execute("INSERT IGNORE INTO testtb(product, category) VALUES('{}','{}')".format(*row))
row_count += 1
mydb.commit()
cursor.close()我有以下错误
<bound method AutorSpider.close of <AutorSpider 'autor' at 0x7f64725d29b0>>
Traceback (most recent call last):
File "/home/pc/.local/lib/python3.6/site-packages/twisted/internet/defer.py", line 151, in maybeDeferred
result = f(*args, **kw)
File "/home/pc/.local/lib/python3.6/site-packages/pydispatch/robustapply.py", line 55, in robustApply
return receiver(*arguments, **named)
File "/home/pc/Escritorio/fpyautor/fpyautor/spiders/autor.py", line 109, in close
cursor.execute("INSERT IGNORE INTO autortb(frase, categoria) VALUES({},'{}')'".format(*row))
File "/home/pc/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 170, in execute
result = self._query(query)
File "/home/pc/.local/lib/python3.6/site-packages/pymysql/cursors.py", line 328, in _query
conn.query(q)
File "/home/pc/.local/lib/python3.6/site-packages/pymysql/connections.py", line 516, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/home/pc/.local/lib/python3.6/site-packages/pymysql/connections.py", line 727, in _read_query_result
result.read()
File "/home/pc/.local/lib/python3.6/site-packages/pymysql/connections.py", line 1066, in read
first_packet = self.connection._read_packet()
File "/home/pc/.local/lib/python3.6/site-packages/pymysql/connections.py", line 683, in _read_packet
packet.check_error()
File "/home/pc/.local/lib/python3.6/site-packages/pymysql/protocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "/home/pc/.local/lib/python3.6/site-packages/pymysql/err.py", line 109, in raise_mysql_exception
raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'titulo del item numero 1' at line 1")其他更简单/更有效的方法是什么？因为我在抓取任务结束时保存了数据，如果我得到了更多的结果(3000个项目)，也许这在未来更大的网站上会有问题？
回答 1
Stack Overflow用户
回答已采纳
发布于 2018-08-03 03:12:18
escape string可以帮助您
def close(self, reason):
csv_file = max(glob.iglob('*.csv'), key=os.path.getctime)
mydb = MySQLdb.connect(host='localhost',
user='demo',
passwd='123456',
db='testdb')
cursor = mydb.cursor()
csv_data = csv.reader(open(csv_file))
row_count = 0
for row in csv_data:
if row_count != 0:
product = mydb.escape_string(row[0])
category = mydb.escape_string(row[1])
#print category , product
sql = 'INSERT IGNORE INTO testtb(product, category) VALUES ( "{}","{}")'.format(product,category)
#print sql
cursor.execute(sql)
row_count += 1
mydb.commit()
cursor.close()页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51660481
复制相关文章
相似问题