Python、MySQL 使用变量时不插入。可以就地使用变量的精确值，并且它可以工作

Question

有以下代码:

import os
import sys
import time
import MySQLdb

if __name__=="__main__":

dbcon = MySQLdb.connect(host="host", port=3306, user="db", passwd="passwd", db="adki")
dbcur = dbcon.cursor()

deliveryCount = 0
bounceBadMailbox = 0
bounceInactiveAccount = 0
bouncePolicyRelated = 0
bounceSpamRelated = 0
bounceQuotaIssues =0

while True:
    #type, timeLogged,timeQueued,orig,rcpt,orcpt,dsnAction,dsnStatus,dsnDiag,dsnMta,bounceCat,srcType,srcMta,dlvType,dlvSourceIp,dlvDestinationIp,dlvEsmtpAvailable,dlvSize,vmta,jobId,envId,queue,vmtaPool
    line = sys.stdin.readline()
    logList = line.split(',')
    bounceType = str(logList[0])
    if bounceType != "type":
        bounceType = str(logList[0])
        bounceCategory = logList[10]
        emailAddress = logList[4]
        jobId = str(logList[23])
        fwrite = open("debug.log","a")
        fwrite.write(jobId)

        if bounceType == "d":
            deliveryCount += 1
            fwrite = open("debug2.log","a")
            fwrite.write(str(jobId))
            dbcur.execute('UPDATE campaign_stat_delivered SET pmta_delivered = pmta_delivered + 1 WHERE id = ' + jobId)
            dbcon.commit()





fwrite = open("debug2.log","w")
fwrite.write("out of true loop")

dbcon.close()

Python 新手。这实在难倒我了。上面的SQL语句不起作用(pmta_delivered的值仍然是0)，但我可以运行:

dbcur.execute('UPDATE campaign_stat_delivered SET pmta_delivered = pmta_delivered + 1 WHERE id = ' + '42')

并且它有效!？!在“debug2.log”中，我写入了值“42”。这是怎么回事？？

数据库架构:

--
-- Table structure for table `campaign_stat_delivered`
--

CREATE TABLE IF NOT EXISTS `campaign_stat_delivered` (
  `id` int(9) NOT NULL AUTO_INCREMENT,
  `pmta_delivered` int(9) NOT NULL DEFAULT '0',
  `async_bounces` int(9) NOT NULL DEFAULT '0',
  `bad_mailbox` int(11) NOT NULL DEFAULT '0',
  `inactive_account` int(11) NOT NULL DEFAULT '0',
  `policy_related` int(11) NOT NULL DEFAULT '0',
  `spam_related` int(11) NOT NULL DEFAULT '0',
  `quota_issues` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

我再次可以将 sql 更改为:

dbcur.execute('UPDATE campaign_stat_delivered SET pmta_delivered = pmta_delivered + 1 WHERE id = 1')

而且效果很好。真是傻眼了。

Answer 1

假设您的 jobId 是显示整数值的字符串，我建议

添加断言，确保字符串的长度非零确保字符串采用整数格式。

assert len(jobId) > 0
try:
    int(jobId)
except ValueError:
    print "ERROR: jobId is not in format of integer"
    raise

这可能会告诉您原因是什么。