如何解决pymysql.err.ProgrammingError：1064-在mysql上保存数据？

Question

我正在尝试将scrapy抓取的数据保存到mysql。但是，我有以下问题：

1. 不再支持MySQLdb。所以，我必须使用

   import pymysql

   pymysql.install_as_MySQLdb()在settings.py文件

2. 在python 3%s上已弃用，我必须将.format与以下代码结合使用：

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)

还有其他更简单/有效的方法吗？因为即时通讯会在抓取任务结束时保存数据，并且如果我获得了更多结果（3000个项目），那么将来在更大的网站上可能会遇到问题吗？

Answer 1

转义字符串可以帮助您def close(self, reason):&nbsp; &nbsp; csv_file = max(glob.iglob('*.csv'), key=os.path.getctime)&nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; mydb = MySQLdb.connect(host='localhost',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;user='demo',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;passwd='123456',&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;db='testdb')&nbsp; &nbsp; cursor = mydb.cursor()&nbsp; &nbsp; csv_data = csv.reader(open(csv_file))&nbsp; &nbsp; row_count = 0&nbsp; &nbsp; for row in csv_data:&nbsp; &nbsp; &nbsp; &nbsp; if row_count != 0:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; product = mydb.escape_string(row[0])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; category = mydb.escape_string(row[1])&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #print category , product&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; sql = 'INSERT IGNORE INTO testtb(product, category) VALUES ( "{}","{}")'.format(product,category)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #print sql&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cursor.execute(sql)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row_count += 1&nbsp; &nbsp; mydb.commit()&nbsp; &nbsp; cursor.close()