使用MySQL持久化报错

[November-Dy]

BUG描述

使用MySQL保存数据时会报错，MySQL版本8.0.37。堆栈信息：

2026-06-09 07:10:54,041 - ERROR - weibo.py[:2376] - (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 '), '', '2026-06-07 17:05:34', 'iPhone 16', 537, 37, 30, '', '', 1, 1, '') ON\n ' at line 1")
Traceback (most recent call last):
File "/mnt/data/Project/weibo-crawler/weibo.py", line 2372, in mysql_insert
cursor.executemany(sql, [tuple(data.values()) for data in data_list])
File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 191, in executemany
self.rowcount = sum(self.execute(query, arg) for arg in args)
File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 191, in
self.rowcount = sum(self.execute(query, arg) for arg in args)
File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 153, in execute
result = self._query(query)
File "/usr/local/lib/python3.9/site-packages/pymysql/cursors.py", line 322, in _query
conn.query(q)
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 563, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 825, in _read_query_result
result.read()
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 1199, in read
first_packet = self.connection._read_packet()
File "/usr/local/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_packet
packet.raise_for_error()
File "/usr/local/lib/python3.9/site-packages/pymysql/protocol.py", line 219, in raise_for_error
err.raise_mysql_exception(self._data)
File "/usr/local/lib/python3.9/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
raise errorclass(errno, errval)

BUG原因

排查后发现是links字段造成的，links对应的变量为list类型，在持久化过程中无法自动转换为VARCHAR。

解决方案

在weibo.py的mysql_insert函数中添加一段转换代码：

    import pymysql

    if len(data_list) > 0:
        +++++++++++++++++++++++++++++++++
        for row in data_list:
            for k, v in row.items():
                if isinstance(v, (list, dict, tuple, set)):
                    row[k] = json.dumps(v, ensure_ascii=False)
        +++++++++++++++++++++++++++++++++
        keys = ", ".join(data_list[0].keys())
        values = ", ".join(["%s"] * len(data_list[0]))

在解决这个问题后，发现创建weibo表的过程中，缺少了links字段，因此还需要在weibo.py的weibo_to_mysql函数中进行补充：

    create_table = """
            CREATE TABLE IF NOT EXISTS weibo (
            id varchar(20) NOT NULL,
            bid varchar(12) NOT NULL,
            user_id varchar(20),
            screen_name varchar(30),
            text text,
            article_url varchar(100),
            topics varchar(200),
            at_users varchar(1000),
            pics varchar(3000),
            video_url varchar(1000),
            live_photo_url varchar(1000),
            +++++++++++++++++++++++++++++++++
            links text,
            +++++++++++++++++++++++++++++++++
            location varchar(100),
            created_at DATETIME,
            source varchar(30),
            attitudes_count INT,
            comments_count INT,
            reposts_count INT,
            retweet_id varchar(20),
            edited BOOLEAN DEFAULT 0,
            edit_count INT DEFAULT 0,
            PRIMARY KEY (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4"""
    self.mysql_create_table(mysql_config, create_tab 

[dataabc]

感谢反馈。您不但发现了问题，也给出了解决方案。如果您方便，您可以通过Pull request的方式提交您的修改，这样您还可以成为本项目的Contributor。这不是必须的，如果您不方便，我会在几天之后自行修改代码。不管您是否Pull request，都非常感谢您的热心反馈和建议。