Update and fetch data MySql records using python

I have decided to look further into python in order to progressively move some of my bash scripts, using python instead.

Some scripts (initially in bash) that I have used to update MySQL content, so below some examples of mechanisms used in python.


The idea is to use/set a config file (config.ini) which includes the Database details.

Example below:

[mysql]
host = localhost
database = my_database
user = userdb
password = password123

The script does the following:

    • Fetch specific column from the Database (IP address) and generate a file of it.
    • use that file to validate that these IP that are online, and update the database respectively.

Below it fetch the data and generate the file:

from ConfigParser import SafeConfigParser
import pymysql.cursors
import subprocess
import os, time

parser = SafeConfigParser()
parser.read('config.ini')

host = parser.get('mysql', 'host')
user = parser.get('mysql', 'user')
pass1 = parser.get('mysql', 'password')
database = parser.get('mysql', 'database')

# CONNECTION TO THE DB.
connection = pymysql.connect(host=host,
                             user=user,
                             password=pass1,
                             db=database,
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:

         sql = "SELECT ip from `sensor`"
         cursor.execute(sql)
         result = cursor.fetchall()
         tweets = open("host.txt", "w") 
         #cursor.execute(sql)
         for row in result:
            d = "%s" % (row["ip"],)
            tweets.write(d + '\n')

tweets.close()
print "Extracted data from DB...Done"
time.sleep( 3 )

The following, run ping for each IP part of the host file generated:

def check_ping():
    
    hosts_file = open("host.txt","r")
    lines = hosts_file.readlines()
    hosts_file.close()

    for hostname in lines:
    	# CHANGED IT FOR NO OUTPUT
    	response = subprocess.call(['ping', '-c', '1', hostname], stdout=subprocess.PIPE)
        #response = os.system("ping -c 1 " + hostname)

        if response == 0:
        	#try:
                  with connection.cursor() as cursor:

                    sql = "UPDATE `sensor` SET `status` = %s WHERE `ip` = %s"
                    cursor.execute(sql, ('1', hostname.rstrip()))
                    connection.commit()
                    pingstatus = "Network Active for " + hostname.rstrip()
                    print pingstatus
                    print "DB Data Status updated for " + hostname.rstrip()
                    
        else:
                  with connection.cursor() as cursor:

                    sql = "UPDATE `sensor` SET `status` = %s WHERE `sensor`.`ip` = %s"
                    cursor.execute(sql, ('0', hostname.rstrip()))
                    connection.commit()
                    pingstatus = "Network Down for " + hostname.rstrip()
                    print pingstatus
                    print "DB Data Status updated for " + hostname.rstrip()
    return pingstatus



pingstatus = check_ping()
connection.close()

Leave a Reply

Your email address will not be published. Required fields are marked *