Python : MySQL-Python , optparse

Here’s an example of how to use Python optparse.

# python –version Python 2.6.6

just referred to http://docs.python.org/library/optparse.html.

This script checks whether or not MySQL server return a string you specified.

# cat -n monitor_mySQL.py     1  #!/usr/bin/env python     2  # -*- coding: utf-8 -*-     3     4  ### usage     5  # monitor_mySQL.py --ipaddr=<ip address> --port=<port number> --user=<user> --pass=<password> --db=<DB name> --table=<table name> --checkstring=<string>     6  ###     7     8  import MySQLdb     9  import sys    10  import optparse    11    12  # parse command line options    13  parser = optparse.OptionParser()    14  parser.add_option('--ipaddr',type='string')    15  parser.add_option('--port',type='int')    16  parser.add_option('--user',type='string')    17  parser.add_option('--password',type='string')    18  parser.add_option('--db',type='string')    19  parser.add_option('--table',type='string')    20  parser.add_option('--checkstring',type='string')    21  options, args = parser.parse_args()    22    23  argvs = sys.argv    24  argc = len(argvs)    25    26  if ( 8 != argc ):    27          print "Usage: ./monitor_mySQL.py --ipaddr=<ip address> --port=<port number> --user=<user> --pass=<password> --db=<DB name> --table=<table name>"    28          sys.exit(1)    29    30  ipaddr = options.ipaddr    31  port_number = options.port    32  username = options.user    33  credentials = options.password    34  database = options.db    35  tablename = options.table    36  checkstring = options.checkstring    37    38  conn = None    39    40  try:    41          conn = MySQLdb.connect(    42                  host = ipaddr,    43                  user = username,    44                  passwd = credentials,    45                  db = database,    46                  port = port_number)    47          cur = conn.cursor()    48          cur.execute("SELECT * FROM  %s" % tablename)    49          numrows = int(cur.rowcount)    50    51  except MySQLdb.Error, e:    52          print "Result: Can't connect to MySQL";    53          sys.exit(1)    54    55  for i in range(numrows):    56          row = cur.fetchone()    57    58          if ( checkstring  == row[1]):    59                  print "Result: String matches"    60                  cur.close()    61                  conn.close()    62                  sys.exit(0)    63    64  print "Result: String does not match"    65  cur.close()    66  conn.close()    67  sys.exit(1)    68

- MySQL server

DB : testdb
Table : Testtable

mysql> use testdb mysql> select * from Testtable; +----+------+ | Id | Name | +----+------+ |  1 | foo  | |  2 | zzz  | +----+------+ 2 rows in set (0.00 sec)

Let’s test the script !

# ./monitor_mySQL.py --ipaddr=192.168.11.10 --port=3306 --user=user1 --password=pass --db=testdb --table=Testtable --checkstring=foo Result: String matches

# ./monitor_mySQL.py --ipaddr=192.168.11.10 --port=3306 --user=user1 --password=pass --db=testdb --table=Testtable --checkstring=zzz Result: String matches

# ./monitor_mySQL.py --ipaddr=192.168.11.10 --port=3306 --user=user1 --password=pass --db=testdb --table=Testtable --checkstring=abcde Result: String does not match

This script does not care about the order of the command line options.
So you can also use this script like this

# ./monitor_mySQL.py --table=Testtable –checkstring=foo --ipaddr=192.168.11.10 --port=3306 --user=user1 --password=pass --db=testdb Result: String matches