error occurred while inserting data mysql database

Question

class MysqlPipeline(object): 
        def __init__(self):
            **Connect the mysql**
            self.conn = MySQLdb.connect('localhost','root','root','zhihu',
            charset='utf8')
            self.cursor = self.conn.cursor()
        def process_item(self, item, spider):
            **insert**
            insert_sql = """
            insert into 
            users_info(img_url,user_name,business,user_followingCount,
            user_followerCount,idea_num,gender,favoriteCount,voteupCount,
            followingColumnsCount,participatedLiveCount,followingFavlistsCount,
            favoritedCount,uid,school_list,
            job_list,place_list,major_list,company_list,url_token)
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
            """
            param= (item["img_url"],item["user_name"],item["business"],
                    item["user_followingCount"],item["user_followerCount"],
                    item["idea_num"],item["gender"],item["favoriteCount"],
                    item["voteupCount"],item["followingColumnsCount"],
                    item["participatedLiveCount"],
                    item["followingFavlistsCount"],
                    item["favoritedCount"],item["uid"],item["school_list"],
                    item["job_list"],item["place_list"],item["major_list"],
                    item["company_list"],item["url_token"]
                    )
           self.cursor.execute(insert_sql,param)

Error

How should I solve this problem?

Traceback (most recent call last):
 File "C:\Users\Administrator.JQ8B7UF6EZUHOOH\Envs\article_spider\lib\site-packages\twisted\internet\defer.py", line 653, in _runCallbacks
    current.result = callback(current.result, *args, **kw)
  File "D:/分布式爬虫相关测试/Zhihu\Zhihu\pipelines.py", line 38, in process_item
    self.cursor.execute(insert_sql,param)
  File "C:\Users\Administrator.JQ8B7UF6EZUHOOH\Envs\article_spider\lib\site-packages\MySQLdb\cursors.py", line 250, in execute
    self.errorhandler(self, exc, value)
  File "C:\Users\Administrator.JQ8B7UF6EZUHOOH\Envs\article_spider\lib\site-packages\MySQLdb\connections.py", line 50, in defaulterrorhandler
    raise errorvalue
  File "C:\Users\Administrator.JQ8B7UF6EZUHOOH\Envs\article_spider\lib\site-packages\MySQLdb\cursors.py", line 247, in execute
    res = self._query(query)
  File "C:\Users\Administrator.JQ8B7UF6EZUHOOH\Envs\article_spider\lib\site-packages\MySQLdb\cursors.py", line 411, in _query
    rowcount = self._do_query(q)
  File "C:\Users\Administrator.JQ8B7UF6EZUHOOH\Envs\article_spider\lib\site-packages\MySQLdb\cursors.py", line 374, in _do_query
    db.query(q)
  File "C:\Users\Administrator.JQ8B7UF6EZUHOOH\Envs\article_spider\lib\site-packages\MySQLdb\connections.py", line 277, in query
    _mysql.connection.query(self, query)
_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 '),(),(),(),(),'qiu-shuo-47')' at line 4")

I would mogrify the SQL query and run it in actual MySQL and see what the error actually is. Often this is generated from a type error at the SQL level (text into int field) but is masked by a general error from the MySqlDb module. — eatmeimadanish
– eatmeimadanish, Commented Apr 6, 2018 at 15:51
@zjian please add some text to your question. some verbal explaining can go really far when asking for help on SO :-) — Thatkookooguy
– Thatkookooguy, Commented Apr 6, 2018 at 15:53
I found the problem. I inserted a number of variables in the list type. I did not convert them to STR type, so there was a mistake.Thank you very much for your help. — zjian
– zjian, Commented Apr 7, 2018 at 6:26

Ivan Vinogradov · Accepted Answer · 2018-04-06 15:58:18Z

1

You are trying to insert 19 values in 20 columns:

print(len(param))  # 19
print(insert_sql.count('%s'))  # 20

answered Apr 6, 2018 at 15:58

Ivan Vinogradov

4,5236 gold badges34 silver badges42 bronze badges

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

1 Comment

Cris Luengo Over a year ago

@zjian: If you want to comment on an answer, do so in the comment box below, don't edit the answer. Also, since it solved your problem, please accept this answer.

Collectives™ on Stack Overflow

error occurred while inserting data mysql database

Error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Error

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related