How to resolve pymysql.err.ProgrammingError: 1064 - save data on mysql?

Question

I'm trying to save the data scraped with scrapy to mysql. But, I have these issues:

No more support for MySQLdb. So, I have to use

import pymysql

pymysql.install_as_MySQLdb() on settings.py file
On python 3 %s is deprecated and I have to use .format with the following 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()

I have the following error

<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")

is other simplier/eficcient way? because im saving the data at the end of the scraping task and if i got more results (3000 items )maybe this is a problem in future with bigger sites?

is product a varchar column ?

Madhan Varadhodiyil
– Madhan Varadhodiyil

2018-08-02 19:04:56 +00:00
Commented Aug 2, 2018 at 19:04 — Madhan Varadhodiyil
– Madhan Varadhodiyil, Commented Aug 2, 2018 at 19:04
yes, thanks for comment

davidm
– davidm

2018-08-02 19:05:36 +00:00
Commented Aug 2, 2018 at 19:05 — davidm
– davidm, Commented Aug 2, 2018 at 19:05
And your braces are not properly closed.

Madhan Varadhodiyil
– Madhan Varadhodiyil

2018-08-02 19:07:50 +00:00
Commented Aug 2, 2018 at 19:07 — Madhan Varadhodiyil
– Madhan Varadhodiyil, Commented Aug 2, 2018 at 19:07
edited, edited.

davidm
– davidm

2018-08-02 19:08:58 +00:00
Commented Aug 2, 2018 at 19:08 — davidm
– davidm, Commented Aug 2, 2018 at 19:08
Wait, are you still getting the error?

Madhan Varadhodiyil
– Madhan Varadhodiyil

2018-08-02 19:15:12 +00:00
Commented Aug 2, 2018 at 19:15 — Madhan Varadhodiyil
– Madhan Varadhodiyil, Commented Aug 2, 2018 at 19:15

Madhan Varadhodiyil · Accepted Answer · 2018-08-02 21:23:20Z

1

escape string can help you

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()

edited Aug 2, 2018 at 21:23

answered Aug 2, 2018 at 19:12

Madhan Varadhodiyil

2,1061 gold badge16 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

How to resolve pymysql.err.ProgrammingError: 1064 - save data on mysql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related