【已解决】pyspider中pymysql中insert失败且except部分代码没有执行

折腾：

【未解决】pyspider运行出错：FETCH_ERROR HTTP 599 Connection timed out after milliseconds

期间，发现个问题：

insert没有按照期望去把数据插入到mysql数据库中

且即使出错了，except部分的代码也没有执行到

更关键的是：mysql数据库中没有进数据：

不过此处后来却又继续输出log信息，看起来好像又是对的：

通过log：

keyListSql= `品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`
eachValueInSql= "福特"
eachValueInSql= "长安福特"
eachValueInSql= "翼虎"
eachValueInSql= "2863"
eachValueInSql= "2018款 EcoBoost 180 两驱豪翼型"
eachValueInSql= "32128"
eachValueInSql= "在售"
eachValueInSql= 192800
eachValueInSql= 192800
valueListSql= "福特", "长安福特", "翼虎", "2863", "2018款 EcoBoost 180 两驱豪翼型", "32128", "在售", 192800, 192800
insertSql= 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_info
cursor= <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_info
insertOk=False

和代码：

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

但是对于：

insertSql= INSERT INTO tbl_autohome_car_info (`品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`) VALUES ("福特", "长安福特", "翼虎", "2863", "2018款 EcoBoost 180 两驱豪翼型", "32128", "在售", 192800, 192800)

看起来没毛病，应该正常插入的啊

怎么看起来感觉是：

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

pymysql 操作数据库 – jl_bai – 博客园

好像是应该去在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

INSERT INTO tbl_autohome_car_info (`品牌`, `子品牌`, `车系`, `车系ID`, `车型`, `车型ID`, `状态`, `经销商参考价`, `厂商指导价`) VALUES ("斯柯达", "斯柯达(进口)", "速派(进口)", "357", "2010款 Combi 四驱", "7040", "停售", 0, 0)

“Unknown column ‘品牌’ in ‘field list’”

然后才注意到：

此处，是有问题的，是自己之前写代码，没注意，没有用：mysql中的field字段，应该改为：

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["厂商指导价"] = msrpPriceInt
curSerieDict["cityDealerPrice"] = cityDealerPriceInt
curSerieDict["msrpPrice"] = msrpPriceInt

然后就正常可以插入了才对。

然后去看看是否正常能插入数据

那问题来了：

为何之前的代码：

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中：

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'")

只是自己没注意到。。。

改为：

except pymysql.Error as err:
    print("!!! %s when execute sql %s for %s" % (err, sqlStr, actionDescription))

就容易被看到error，注意到error了。

所以再去运行，看看是否解决了此问题了。

部分log是：

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= 0
eachValueInSql= 0
valueListSql= "五十铃", "五十铃", "D-MAX(海外)", "3564", "2017款 基本型", "1005279", "未上市", 0, 0
insertSql= 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_info
cursor= <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_info
insertOk=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中的代码中，改为正确的：

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["厂商指导价"] = msrpPriceInt
curSerieDict["cityDealerPrice"] = cityDealerPriceInt
curSerieDict["msrpPrice"] = msrpPriceInt

和mysql中的create table时的sql：

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数据库了。

转载请注明：在路上 » 【已解决】pyspider中pymysql中insert失败且except部分代码没有执行