Python, A MySQL statement works when I put in actual value of variable, but not when using variable?
Question: Have the following code, it is part of a script used to read stdin, and
process logs.

    
    
    jobId = loglist[19]
    deliveryCount += 1
    dbcur.execute('UPDATE campaign_stat_delivered SET pmta_delivered = pmta_delivered + %s WHERE id = %s') % (deliveryCount,jobId)
    dbcon.commit()
    dbcon.close()
    

I can run the following:

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

and it will work. Not really sure whats going on, and its hard for me to test
quickly because I can't actually see the script running since my program feeds
directly into it. I have to make changes, restart program that feeds, send an
email, then check database. Have other scripts, and am able to use variables
in SQL statements with no problem.

Any suggestions as to what may be going on? And, any suggestions on how I can
test quicker?

full code:

    
    
    import os
    import sys
    import time
    import MySQLdb
    import csv
    
    if __name__=="__main__":
    
    dbcon = MySQLdb.connect(host="tattoine.mktrn.net", port=3306, user="adki", passwd="pKhL9vrMN8BsFrJ5", db="adki")
    dbcur = dbcon.cursor()
    
        #type, timeLogged,timeQueued,orig,rcpt,orcpt,dsnAction,dsnStatus,dsnDiag,dsnMta,bounceCat,srcType,srcMta,dlvType,dlvSourceIp,dlvDestinationIp,dlvEsmtpAvailable,dlvSize,vmta,jobId,envId,queue,vmtaPool
    while True:
        line = sys.stdin.readline()
        fwrite = open("debug.log","w")
       # fwrite.write(str(deliveryCount))
        fwrite.write("test2")
        dbcur.execute("INSERT INTO test(event_type) VALUES ('list')")
        dbcon.commit()
    
        loglist = line.split(',')
    
    
    
        deliveryCount = 0
        bounceType = loglist[0]
        bounceCategory = loglist[10]
        email = loglist[4]
        jobId = loglist[19]
    
        if bounceType == 'd':
            deliveryCount += 1
    
    
    fwrite = open("debug2.log","w")
       # fwrite.write(str(deliveryCount))
    fwrite.write("test3")
    
    dbcur.execute("INSERT INTO test(event_type) VALUES (%d)", deliveryCount)
    dbcon.commit()
    dbcur.execute('UPDATE campaign_stat_delivered SET pmta_delivered = pmta_delivered + %s WHERE id = %s',(deliveryCount,jobId))
    dbcon.commit()
    dbcon.close()
    


Answer: Never use string interpolation to run a sql query.

You should do:

    
    
    dbcur.execute(
        'UPDATE campaign_stat_delivered SET pmta_delivered = pmta_delivered + %s WHERE id = %s',
        (deliveryCount,jobId)
    )
    

There's two arguments to the execute function. The query with placeholders,
and a tuple of parameters. This way, mysql will escape your parameters for you
and prevent sql injection attacks (
<http://en.wikipedia.org/wiki/SQL_injection> )

Your error must have come from the fact that you use the % operator on the
result of the query `'UPDATE campaign_stat_delivered SET pmta_delivered =
pmta_delivered + %s WHERE id = %s'`. This query in itself (without parameters)
is syntactically incorrect for mysql. You have to pass the tuple of parameters
to the execute function as a second argument.

