StephenM StephenM - 5 months ago 12
SQL Question

Python and MySQL UPDATE

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()

3kt 3kt
Answer

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.