Python and MySQL UPDATE

Question

Everything is working as it should for the ping tool but MySQL updates are failing.

It should pull the ip address it is currently doing and update it in the MYSQL.

import MySQLdb
db = MySQLdb.connect(host="10.1.1.151",    # your host, usually localhost
                     user="root",         # your username
                     passwd="**************",  # your password
                     db="main_system")        # name of the data base
cur = db.cursor()

from threading import Thread
import subprocess
from Queue import Queue

num_threads = 10
queue = Queue()

ips = ["10.1.1.151", "10.1.1.152"]

#wraps system ping command
def pinger(i, q):
    """Pings subnet"""
    while True:
        ip = q.get()
        ret = subprocess.call("ping -i .1 -c 1 -W 50 %s" % ip,
            shell=True,
            stdout=open('/dev/null', 'w'),
            stderr=subprocess.STDOUT)
        if ret == 0:
            cur.execute("UPDATE application_status SET status = 1 WHERE ip_address = %s")
            print "%s: is alive" % ip
        else:
            cur.execute("UPDATE application_status SET status = 0 WHERE ip_address = %s")
            print "%s: did not respond" % ip
        q.task_done()
#Spawn thread pool
for i in range(num_threads):

    worker = Thread(target=pinger, args=(i, queue))
    worker.setDaemon(True)
    worker.start()
#Place work in queue
for ip in ips:
    queue.put(ip)
#Wait until worker threads are done to exit    
queue.join()

Mureinik · Accepted Answer · 2016-06-16 06:15:56Z

1

You're missing the binding of the data (ip) to the cursor:

if ret == 0:
    cur.execute("UPDATE application_status SET status = 1 WHERE ip_address = %s", (ip,))                
    print "%s: is alive" % ip
else:
    cur.execute("UPDATE application_status SET status = 0 WHERE ip_address = %s", (ip,))
    print "%s: did not respond" % ip

edited Jun 16, 2016 at 6:15

answered Jun 16, 2016 at 6:13

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

StephenM Over a year ago

How do I do that? I am new to python

Mureinik Over a year ago

@StephenM see the code snippet I shared in my answer

StephenM Over a year ago

Ohhh I am blind. Thanks for your help :)

3kt · Accepted Answer · 2016-06-16 06:12:46Z

0

The problem may be that you need to commit the transactions, after calling cur.execute().

Hence you'll need to call db.commit() after all your transactions are executed.

Hope it'll be helpful.

answered Jun 16, 2016 at 6:12

3kt

2,5432 gold badges19 silver badges30 bronze badges

1 Comment

StephenM Over a year ago

Alright I made that change and still having issues 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 '%s' at line 1")

Collectives™ on Stack Overflow

Python and MySQL UPDATE

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related