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 ‘CREATE TABLE `sinatop20` ( `snid` int(11) NOT NULL AUTO_INCREMENT, `id` varchar(‘ at line 1”)
对于报错解析:很明显这是语法错误,我上看下看,左看右看,都没有看出问题来。
在pycharm中执行报语法错误,但是我复制到mysql数据库去执行又是正常的。就很纳闷。
目录
一、执行的语句
二、分析
1、 在pycharm运行就报错了:
2、在mysql运行是正常的
3、错误寻找
4、答案在这:
一、执行的语句
原来的执行语句的功能:”””如果存在表就卸载表;然后创建表”””
DROP TABLE IF EXISTS `sinatop20`; CREATE TABLE `sinatop20` ( `snid` int(11) NOT NULL AUTO_INCREMENT, `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `media` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `author` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `comment_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_date` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `cat_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `top_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `top_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext4` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext5` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`snid`) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
DROP TABLE IF EXISTS `sinatop20`; CREATE TABLE `sinatop20` ( `snid` int(11) NOT NULL AUTO_INCREMENT, `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `media` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `author` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `comment_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_date` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `cat_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `top_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `top_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext4` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext5` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`snid`) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
二、分析
1、 在pycharm运行就报错了:
Traceback (most recent call last):
File "E:WorkSpaceGraProDISHSASysBTM_ManageSysDataCrawlsinaTop20.py", line 62, in
cur.execute(create_sql)
File "C:ProgramsPythonPython310libsite-packagespymysqlcursors.py", line 170, in execute
result = self._query(query)
File "C:ProgramsPythonPython310libsite-packagespymysqlcursors.py", line 328, in _query
conn.query(q)
File "C:ProgramsPythonPython310libsite-packagespymysqlconnections.py", line 517, in query
self._affected_rows = self._read_query_result(unbuffered=unbuffered)
File "C:ProgramsPythonPython310libsite-packagespymysqlconnections.py", line 732, in _read_query_result
result.read()
File "C:ProgramsPythonPython310libsite-packagespymysqlconnections.py", line 1075, in read
first_packet = self.connection._read_packet()
File "C:ProgramsPythonPython310libsite-packagespymysqlconnections.py", line 684, in _read_packet
packet.check_error()
File "C:ProgramsPythonPython310libsite-packagespymysqlprotocol.py", line 220, in check_error
err.raise_mysql_exception(self._data)
File "C:ProgramsPythonPython310libsite-packagespymysqlerr.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 'CREATE TABLE `sinatop20` ( `snid` int(11) NOT NULL AUTO_INCREMENT, `id` varchar(' at line 1")
Process finished with exit code 1
2、在mysql运行是正常的
DROP TABLE IF EXISTS `sinatop20`
> OK
> 时间: 0.001s
CREATE TABLE `sinatop20` ( snid int(11) NOT NULL AUTO_INCREMENT, `id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `title` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `media` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `author` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `comment_url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `url` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_date` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `create_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `cat_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `top_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `top_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext1` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext2` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext3` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext4` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `ext5` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`snid`) USING BTREE ) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic
> OK
> 时间: 0.003s
3、错误寻找
找了很多,有人说是使用的字段有可能是关键字,要用 “ 把关键字包起来,我也包起来了,其他什么逗号,括号都没有问题。毕竟在mysql查询里可以运行,那就语句是没有问题的。
然后想起昨晚第一次运行都没有问题,后来我是改了哪里出了问题?
4、答案在这:
因为在pycharm里,上述sql语句中,我同时执行了两个命令!!!
要把DROP和CREATE分开两次运行,不能放在一个excute执行!!!
我的代码在这:
——-报错的代码示例:
—–解决错误后代码示例: