Python mysql error #1064

Question

I'm trying to store the data into MySQL by writing pipeline, but it occurs:

_mysql_exceptions.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 'desc,pic)values ('xe4\xbe\x9b\xe5\xba.......' at line 1")

Here is my code:

def __init__(self):
    #self.file = codecs.open('tutorial_data.json','wb',encoding='utf-8')
    self.dbpool = adbapi.ConnectionPool('MySQLdb',
                                        host = 'localhost',
                                        port = 3306,
                                        db = 'project',
                                        user = 'root',
                                        passwd = '',
                                        cursorclass = MySQLdb.cursors.DictCursor,
                                        charset = 'utf8',
                                        use_unicode = True)
def process_item(self, item, spider):
    query = self.dbpool.runInteraction(self._conditional_insert, item)
    query.addErrback(self.handle_error)
    return item

def _conditional_insert(self,tx,item):

        tx.execute(
        "INSERT INTO raw (title,area,date,sclass,desc,pic )\ 
         VALUES (%s, %s, %s, %s, %s, %s)",

               (item['title'][0],
                item['area'][0],
                item['date'][0],
                item['sclass'][0],
                item['desc'][0],
                item['pic'][0])
                )

My MySQL version is 5.6.17 and mysql-python version is 1.2.5_

John Conde · Accepted Answer · 2015-03-19 13:16:44Z

1

DESC is a reserved keyword in MySQL. If you are going to name a column identifier DESC you must wrap it in ticks:

 tx.execute(
    "INSERT INTO raw (title,area,date,sclass,`desc`,pic )\ 
     VALUES (%s, %s, %s, %s, %s, %s)",

However, a better solution would be to not use a reserved keyword as a column identifier as it can cause confusing and potential errors elsewhere (plus the full word "description" is clearer and makes for more maintainable code).

 tx.execute(
    "INSERT INTO raw (title,area,date,sclass,description,pic )\ 
     VALUES (%s, %s, %s, %s, %s, %s)",

answered Mar 19, 2015 at 13:16

John Conde

220k100 gold badges464 silver badges504 bronze badges

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

1 Comment

Will Over a year ago

BIG THX! I am too careless

Collectives™ on Stack Overflow

Python mysql error #1064

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