折腾：
【未解决】pyspider运行出错：FETCH_ERROR HTTP 599 Connection timed out after milliseconds
期间，发现个问题：
insert没有按照期望去把数据插入到mysql数据库中
且即使出错了，except部分的代码也没有执行到
更关键的是：mysql数据库中没有进数据：
不过此处后来却又继续输出log信息，看起来好像又是对的：
通过log：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | keyListSql= `品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`eachValueInSql= "福特"eachValueInSql= "长安福特"eachValueInSql= "翼虎"eachValueInSql= "2863"eachValueInSql= "2018款 EcoBoost 180 两驱豪翼型"eachValueInSql= "32128"eachValueInSql= "在售"eachValueInSql= 192800eachValueInSql= 192800valueListSql= "福特", "长安福特", "翼虎", "2863", "2018款 EcoBoost 180 两驱豪翼型", "32128", "在售", 192800, 192800insertSql= INSERT INTO tbl_autohome_car_info (`品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`) VALUES ("福特", "长安福特", "翼虎", "2863", "2018款 EcoBoost 180 两驱豪翼型", "32128", "在售", 192800, 192800)executeSql: sqlStr=INSERT INTO tbl_autohome_car_info (`品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`) VALUES ("福特", "长安福特", "翼虎", "2863", "2018款 EcoBoost 180 两驱豪翼型", "32128", "在售", 192800, 192800), actionDescription=Insert value to table tbl_autohome_car_infocursor= <pymysql.cursors.DictCursor object at 0x10a650eb8>Execute sql INSERT INTO tbl_autohome_car_info (`品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`) VALUES ("福特", "长安福特", "翼虎", "2863", "2018款 EcoBoost 180 两驱豪翼型", "32128", "在售", 192800, 192800) occur error (1054, "Unknown column '品牌' in 'field list'") for Insert value to table tbl_autohome_car_infoinsertOk=False |
和代码：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | for eachValue in dictValueList: # print("eachValue=", eachValue) eachValueInSql = "" valueType = type(eachValue) # print("valueType=", valueType) if valueType is str: eachValueInSql = '"%s"' % eachValue elif valueType is int: eachValueInSql = '%d' % eachValue # TODO: add more type formatting if necessary print("eachValueInSql=", eachValueInSql) formattedDictValueList.append(eachValueInSql)valueListSql = ", ".join(eachValue for eachValue in formattedDictValueList)print("valueListSql=", valueListSql)insertSql = """INSERT INTO %s (%s) VALUES (%s)""" % (tablename, keyListSql, valueListSql)print("insertSql=", insertSql)# INSERT INTO tbl_car_info_test (`url`, `mainBrand`, `subBrand`, `brandSerie`, `brandSerieId`, `model`, `modelId`, `modelStatus`, `cityDealerPrice`, `msrpPrice`) VALUES ("https://www.autohome.com.cn/spec/5872/#pvareaid=2042128", "宝马", "华晨宝马", "宝马3系", "66", "2010款 320i 豪华型", "5872", "停售", 325000, 375000)return self.executeSql(sqlStr=insertSql, actionDescription=("Insert value to table %s" % tablename)) |
发现
此处是insert mysql出错了，返回false
但是对于：
1 | insertSql= INSERT INTO tbl_autohome_car_info (`品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`) VALUES ("福特", "长安福特", "翼虎", "2863", "2018款 EcoBoost 180 两驱豪翼型", "32128", "在售", 192800, 192800) |
看起来没毛病，应该正常插入的啊
怎么看起来感觉是：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | def executeSql(self, sqlStr, actionDescription=""): print("executeSql: sqlStr=%s, actionDescription=%s" % (sqlStr, actionDescription)) if self.connection is None: print("Please connect mysql first before %s" % actionDescription) return False cursor = self.connection.cursor() print("cursor=", cursor) try: cursor.execute(sqlStr)self.connection.commit() return True except pymysql.Error as err: print("Execute sql %s occur error %s for %s" % (sqlStr, err, actionDescription)) return False |
中的except部分没有执行到？
否则为何error的信息没有print呢？
pymysql cursor.execute except
pymysql cursor.execute except not work
python – PyMysql failing on insert hitting except block – Stack Overflow
好像是应该去在except时：
rollback
Python操作MySQL数据库的三种方法-技术-51CTO博客
python – MySQLdb.cursors.Cursor.execute does not work – Stack Overflow
Can not insert data mysql using python with pymysql – Ask Ubuntu
此处已经commit了。
pymysql insert except not work
python – Pymysql Insert Into not working – Stack Overflow
也可以在pymsql的connect初始化加上：
autocommit=True
重新去试试，结果sql的执行还是false
去Sequel中试试sql
1 | INSERT INTO tbl_autohome_car_info (`品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`) VALUES ("斯柯达", "斯柯达(进口)", "速派(进口)", "357", "2010款 Combi 四驱", "7040", "停售", 0, 0) |
“Unknown column ‘品牌’ in ‘field list’”
然后才注意到：
此处，是有问题的，是自己之前写代码，没注意，没有用：mysql中的field字段，应该改为：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | allSerieDictList = []for curIdx, eachModelDetailDict in enumerate(modelDetailDictList): """ defined in mysql CREATE TABLE `tbl_autohome_car_info` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增，主键', `cityDealerPrice` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '经销商参考价', `msrpPrice` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '厂商指导价', `mainBrand` char(20) NOT NULL DEFAULT '' COMMENT '品牌', `subBrand` varchar(20) NOT NULL DEFAULT '' COMMENT '子品牌', `brandSerie` varchar(20) NOT NULL DEFAULT '' COMMENT '车系', `brandSerieId` varchar(15) NOT NULL DEFAULT '' COMMENT '车系ID', `model` varchar(50) NOT NULL DEFAULT '' COMMENT '车型', `modelId` varchar(15) NOT NULL DEFAULT '' COMMENT '车型ID', `modelStatus` char(5) NOT NULL DEFAULT '' COMMENT '车型状态', `url` varchar(200) NOT NULL DEFAULT '' COMMENT '车型url', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; """ curSerieDict = { # "品牌": mainBrandDict["text"], # "子品牌": subBrandDict["text"], # "车系": brandSerieDict["text"], # "车系ID": eachModelDetailDict["车系ID"], # "车型": eachModelDetailDict["车型"], # "车型ID": eachModelDetailDict["车型ID"], # "状态": eachModelDetailDict["状态"] "mainBrand": mainBrandDict["text"], "subBrand": subBrandDict["text"], "brandSerie": brandSerieDict["text"], "brandSerieId": eachModelDetailDict["brandSerieId"], "model": eachModelDetailDict["model"], "modelId": eachModelDetailDict["modelId"], "modelStatus": eachModelDetailDict["modelStatus"] } allSerieDictList.append(curSerieDict)# curSerieDict["经销商参考价"] = cityDealerPriceInt# curSerieDict["厂商指导价"] = msrpPriceIntcurSerieDict["cityDealerPrice"] = cityDealerPriceIntcurSerieDict["msrpPrice"] = msrpPriceInt |
然后就正常可以插入了才对。
然后去看看是否正常能插入数据
那问题来了：
为何之前的代码：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | def executeSql(self, sqlStr, actionDescription=""): print("executeSql: sqlStr=%s, actionDescription=%s" % (sqlStr, actionDescription)) if self.connection is None: print("Please connect mysql first before %s" % actionDescription) return False cursor = self.connection.cursor() print("cursor=", cursor) try: cursor.execute(sqlStr) self.connection.commit() return True except pymysql.Error as err: print("Execute sql %s occur error %s for %s" % (sqlStr, err, actionDescription)) return False |
中的except部分：
except pymysql.Error as err:
的print error错误信息：
print(“Execute sql %s occur error %s for %s” % (sqlStr, err, actionDescription))
没有执行到，终端中看不到mysql insert出现的错误信息呢？
单独去调试此处文件：
AutohomeResultWorker.py
模拟出错现象，看看是否可以执行except，打印出error
然后才注意到，之前的log中：
1 | Execute sql INSERT INTO tbl_autohome_car_info (`品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`) VALUES ("福特", "长安福特", "翼虎", "2863", "2018款 EcoBoost 180 两驱豪翼型", "32128", "在售", 192800, 192800) occur error (1054, "Unknown column '品牌' in 'field list'") for Insert value to table tbl_autohome_car_info |
已经打印出错误信息了
(1054, "Unknown column '品牌' in 'field list'")
只是自己没注意到。。。
改为：
1 2 | except pymysql.Error as err: print("!!! %s when execute sql %s for %s" % (err, sqlStr, actionDescription)) |
就容易被看到error，注意到error了。
所以再去运行，看看是否解决了此问题了。
部分log是：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | ictKeyList= dict_keys(['mainBrand', 'subBrand', 'brandSerie', 'brandSerieId', 'model', 'modelId', 'modelStatus', 'cityDealerPrice', 'msrpPrice']) dictValueList= dict_values(['五十铃', '五十铃', 'D-MAX(海外)', '3564', '2017款 基本型', '1005279', '未上市', 0, 0])keyListSql= `mainBrand`, `subBrand`, `brandSerie`, `brandSerieId`, `model`, `modelId`, `modelStatus`, `cityDealerPrice`, `msrpPrice`eachValueInSql= "五十铃"eachValueInSql= "五十铃"eachValueInSql= "D-MAX(海外)"eachValueInSql= "3564"eachValueInSql= "2017款 基本型"eachValueInSql= "1005279"eachValueInSql= "未上市"eachValueInSql= 0eachValueInSql= 0valueListSql= "五十铃", "五十铃", "D-MAX(海外)", "3564", "2017款 基本型", "1005279", "未上市", 0, 0insertSql= INSERT INTO tbl_autohome_car_info (`mainBrand`, `subBrand`, `brandSerie`, `brandSerieId`, `model`, `modelId`, `modelStatus`, `cityDealerPrice`, `msrpPrice`) VALUES ("五十铃", "五十铃", "D-MAX(海外)", "3564", "2017款 基本型", "1005279", "未上市", 0, 0)executeSql: sqlStr=INSERT INTO tbl_autohome_car_info (`mainBrand`, `subBrand`, `brandSerie`, `brandSerieId`, `model`, `modelId`, `modelStatus`, `cityDealerPrice`, `msrpPrice`) VALUES ("五十铃", "五十铃", "D-MAX(海外)", "3564", "2017款 基本型", "1005279", "未上市", 0, 0), actionDescription=Insert value to table tbl_autohome_car_infocursor= <pymysql.cursors.DictCursor object at 0x104ec9fd0>+++ Ok to execute sql INSERT INTO tbl_autohome_car_info (`mainBrand`, `subBrand`, `brandSerie`, `brandSerieId`, `model`, `modelId`, `modelStatus`, `cityDealerPrice`, `msrpPrice`) VALUES ("五十铃", "五十铃", "D-MAX(海外)", "3564", "2017款 基本型", "1005279", "未上市", 0, 0) for Insert value to table tbl_autohome_car_infoinsertOk=True[I 180512 15:12:44 scheduler:906] task done autohomeBrandData:143e98652f547f3746ebf24fbd304b08 https://www.autohome.com.cn/spec/16833/#pvareaid=2042128[I 180512 15:12:44 scheduler:906] task done autohomeBrandData:f7f2ae4b9b39236ef304a9ac780f3655 https://www.autohome.com.cn/spec/27298/#pvareaid=2042128[I 180512 15:12:44 scheduler:906] task done autohomeBrandData:91394084c52dcb20d10dac81059cd98f https://www.autohome.com.cn/spec/1005279/#pvareaid=2042128[I 180512 15:12:44 scheduler:965] select autohomeBrandData:90bdac1036821cbbe82606d520bedb22 https://www.autohome.com.cn/spec/24789/#pvareaid=2042128[I 180512 15:12:44 scheduler:965] select autohomeBrandData:77bdfca05e696137ee612ddf34f02e4c https://www.autohome.com.cn/spec/24788/#pvareaid=2042128[I 180512 15:12:44 scheduler:965] select autohomeBrandData:471f2413644997a8bf40b97acfcad4de https://www.autohome.com.cn/spec/24787/#pvareaid=2042128[I 180512 15:12:44 scheduler:965] select autohomeBrandData:74d9ca5c6638993d0c9f5ab888a58e33 https://www.autohome.com.cn/spec/24777/#pvareaid=2042128[I 180512 15:12:45 scheduler:965] select autohomeBrandData:3dc7a45356c56edb0ca84ab38c1319d1 https://www.autohome.com.cn/spec/24785/#pvareaid=2042128[I 180512 15:12:45 scheduler:965] select autohomeBrandData:553f8908ba7f9a2109cd3d702d6a2017 https://www.autohome.com.cn/spec/24784/#pvareaid=2042128[I 180512 15:12:45 scheduler:965] select autohomeBrandData:4d51782447850ce18fc10c1040d60dd0 https://www.autohome.com.cn/spec/24783/#pvareaid=2042128[I 180512 15:12:45 scheduler:965] select autohomeBrandData:7ffca60d365c98063bd7c278418a5ca1 https://www.autohome.com.cn/spec/24782/#pvareaid=2042128 |
好像insert ok了。
去看看
mysql数据库中是有数据了。
再去重新执行了一次，也是正常有数据的了：
【总结】
所以此处是：
自己看错了，事实上是：
mysql部分的insert出错时，pymysql中except部分是执行到了，显示了错误信息：
(1054, "Unknown column '品牌' in 'field list'")
从而得知自己此处是不小心把insert的sql中的的字段的名字的key，写错了，去pyspder中的代码中，改为正确的：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | curSerieDict = { "url": eachModelDetailDict["url"], # "品牌": mainBrandDict["text"], # "子品牌": subBrandDict["text"], # "车系": brandSerieDict["text"], # "车系ID": eachModelDetailDict["车系ID"], # "车型": eachModelDetailDict["车型"], # "车型ID": eachModelDetailDict["车型ID"], # "状态": eachModelDetailDict["状态"] "mainBrand": mainBrandDict["text"], "subBrand": subBrandDict["text"], "brandSerie": brandSerieDict["text"], "brandSerieId": eachModelDetailDict["brandSerieId"], "model": eachModelDetailDict["model"], "modelId": eachModelDetailDict["modelId"], "modelStatus": eachModelDetailDict["modelStatus"]}# curSerieDict["经销商参考价"] = cityDealerPriceInt# curSerieDict["厂商指导价"] = msrpPriceIntcurSerieDict["cityDealerPrice"] = cityDealerPriceIntcurSerieDict["msrpPrice"] = msrpPriceInt |
和mysql中的create table时的sql：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | CREATE TABLE `tbl_autohome_car_info` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增，主键', `cityDealerPrice` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '经销商参考价', `msrpPrice` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '厂商指导价', `mainBrand` char(20) NOT NULL DEFAULT '' COMMENT '品牌', `subBrand` varchar(20) NOT NULL DEFAULT '' COMMENT '子品牌', `brandSerie` varchar(20) NOT NULL DEFAULT '' COMMENT '车系', `brandSerieId` varchar(15) NOT NULL DEFAULT '' COMMENT '车系ID', `model` varchar(50) NOT NULL DEFAULT '' COMMENT '车型', `modelId` varchar(15) NOT NULL DEFAULT '' COMMENT '车型ID', `modelStatus` char(5) NOT NULL DEFAULT '' COMMENT '车型状态', `url` varchar(200) NOT NULL DEFAULT '' COMMENT '车型url', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; |
从而使得对应，从而就可以把result传递到自己的resultworker中，从而调用insert去正常保存数据到mysql数据库了。