MYSQL and Python

At this point you might start to think I only work with python and mysql problems. Not true just happens to be what I have had to work with lately. I am a big python fan and do use it when I can to accomplish simple tasks. Anyway the problem at hand.... I needed to query a mysql database to determine how many orders a user had placed on a website where the orders came to a particular total. This data was stored into two different tables, "orders" and "orders_total". The tables have a lot of columns so I am only showing a few.

mysql> select customers_name,customers_id,orders_id from orders where customers_name ="John Does";
+----------------+--------------+-----------+
| customers_name | customers_id | orders_id |
+----------------+--------------+-----------+
| John Does      |            4 |         3 | 
| John Does      |            4 |         4 | 
| John Does      |            4 |         5 | 
| John Does      |            4 |         6 | 
| John Does      |            4 |         7 | 
| John Does      |            4 |         8 | 
+----------------+--------------+-----------+
6 rows in set (0.00 sec)


mysql> select orders_id,title,text  from orders_total;
+-----------+-----------------------+----------------+
| orders_id | title                 | text           |
+-----------+-----------------------+----------------+
|         1 | Sub-Total:            | $69.99         | 
|         1 | Flat Rate (Best Way): | $5.00          | 
|         1 | Total:                | $74.99  | 
|         2 | Sub-Total:            | $89.99         | 
|         2 | Flat Rate (Best Way): | $5.00          | 
|         2 | Total:                | $94.99  | 
|         3 | Sub-Total:            | $529.97        | 
|         3 | Flat Rate (Best Way): | $5.00          | 
|         3 | Total:                | $534.97 | 
|         4 | Sub-Total:            | $71.99         | 
|         4 | Flat Rate (Best Way): | $5.00          | 
|         4 | Total:                | $76.99  | 
|         5 | Sub-Total:            | $29.99         | 
|         5 | Flat Rate (Best Way): | $5.00          | 
|         5 | Total:                | $34.99  | 
|         6 | Sub-Total:            | $29.99         | 
|         6 | Flat Rate (Best Way): | $5.00          | 
|         6 | Total:                | $34.99  | 
|         7 | Sub-Total:            | $29.99         | 
|         7 | Flat Rate (Best Way): | $5.00          | 
|         7 | Total:                | $34.99  | 
|         8 | Sub-Total:            | $29.99         | 
|         8 | Flat Rate (Best Way): | $5.00          | 
|         8 | Total:                | $34.99  | 
+-----------+-----------------------+----------------+
24 rows in set (0.00 sec)

For simplicity I only wanted to have to use one sql query to obtain the information I was looking for. This required me to craft a sql statement using a "join". Being rusty on more than the basic sql syntax this took me a few attempts. However see the final statement below.

mysql> select customers_name,x.orders_id,y.title,y.text from orders x join orders_total y on x.orders_id = y.orders_id where customers_name = "John Does" and y.title = "Total:";

+----------------+-----------+--------+----------------+
| customers_name | orders_id | title  | text           |
+----------------+-----------+--------+----------------+
| John Does      |         3 | Total: | $534.97 | 
| John Does      |         4 | Total: | $76.99  | 
| John Does      |         5 | Total: | $34.99  | 
| John Does      |         6 | Total: | $34.99  | 
| John Does      |         7 | Total: | $34.99  | 
| John Does      |         8 | Total: | $34.99  | 
+----------------+-----------+--------+----------------+
6 rows in set (0.00 sec)

This statement grabs the columns customers_name from the "orders" table and "orders_id","title" and "text" from the "orders_total" table. The "join" is used to link orders_id from both tables as the common value. The "where" statement is then used to limit the results to only "John Does" orders and only the "Total: " cost for each order. Now that I have a condensed result I wanted to count the orders that "John Does" made that had a value of "34.99". I first was planning on using python to step through the results and count how many entries had the "34.99" cost, however then I realized one more where statement would do it for me!

mysql> select customers_name,x.orders_id,y.title,y.text from orders x join orders_total y on x.orders_id = y.orders_id where customers_name = "John Does" and y.title = "Total:" and text = "$34.99";
+----------------+-----------+--------+---------------+
| customers_name | orders_id | title  | text          |
+----------------+-----------+--------+---------------+
| John Does      |         5 | Total: | $34.99 | 
| John Does      |         6 | Total: | $34.99 | 
| John Does      |         7 | Total: | $34.99 | 
| John Does      |         8 | Total: | $34.99 | 
+----------------+-----------+--------+---------------+
4 rows in set (0.00 sec)

Now the number of rows returned is value I am interested in. I created a python script that will connect to the database and obtain this result. This will allow me to run this script with a cron job and keep track of the number of sales at a certain cost. If you have never used python injunction with mysql it is very easy. There is a library called MySQLdb which has everything you will need. Below is the final script I wrote which takes all of the sql parameters using "argparse". If you have any questions post them and I will answer them.

#!/usr/bin/python

import sys 
import MySQLdb
import argparse

def parseOptions():
    parser=argparse.ArgumentParser(description="Check mysql db for purchases")
    parser.add_argument(dest="host",action="store",help="IP address of mydql server",metavar="HOST")
    parser.add_argument("-u","--username",dest="username",help="Username for db default is user",metavar="USERNAME",default="user")
    parser.add_argument("-p","--password",dest="passwd",help="Password for user. Default is'password' ",metavar="PASSWORD",default="password")
    parser.add_argument("-d","--database",dest="db",help="Database to use. Default is 'db'",metavar="DATABASE",default="db")
    parser.add_argument("-c","--cost",dest="orderCost",help="Total expected cost of order as it appears in mysql. Default is '$34.99'",metavar="COST",default="$34.99")
    parser.add_argument("-n","--searchName",dest="customerSearchName",help="Name to search by in db. Default is 'John Does'",metavar="NAME",default="John Does")

    args = parser.parse_args()

    print "Running using arguments:"
    print "Host:",args.host
    print "Username:",args.username
    print "Password:",args.passwd
    print "Database:",args.db
    print "Order Cost:",args.orderCost
    print "Customer Name:",args.customerSearchName

    return args

def checkDB(username,passwd,host,db,orderCost,customerSearchName):
    try:
        conn = MySQLdb.connect(host,username,passwd,db)
    except MySQLdb.Error as err:
        print("Could not connect to database: {}".format(err))
        sys.exit(-1)

    cursor = conn.cursor()
    sqlCommand = "select customers_name,x.orders_id,y.title,y.text from orders x join orders_total y on x.orders_id = y.orders_id where customers_name = \"%s\" and y.title = \"Total: \" and text = \"%s\";" %(customerSearchName,orderCost)
    try:
        cursor.execute(sqlCommand)
    except MySQLdb.Error as err:
        print("Could not execute sql command: {}".format(err))
        sys.exit(-1)

    return len(cursor.fetchall())

if __name__=='__main__':
    #parse options
    options = parseOptions();
    numOfOrders = checkDB(options.username,options.passwd,options.host,options.db,options.orderCost,options.customerSearchName)

    if numOfOrders == 0:
        print "No Orders"
        sys.exit(1)
    else:
        print "Number of valid orders:",numOfOrders
        sys.exit(0)

SED and VIM to the rescue!

Almost every blog I come across on the internet has at least one blog entry about SED and its amazing powers. Why should this one be any different!? It is unfortunate but you can't control how other people provide you information. The other day I was given a very large list of IP address,subnets and gateways which needed to be put into a SQL database. They looked like this

10.1.0.0,255.255.0.0,10.1.0.1
10.2.0.0,255.255.0.0,10.2.0.1
10.3.0.0,255.255.0.0,10.3.0.1
10.4.0.0,255.255.0.0,10.4.0.1
10.5.0.0,255.255.0.0,10.5.0.1
10.6.0.0,255.255.0.0,10.6.0.1
10.7.0.0,255.255.0.0,10.7.0.1
10.8.0.0,255.255.0.0,10.8.0.1
10.9.0.0,255.255.0.0,10.9.0.1
...

I needed them to look like this

INSERT INTO `ipaddressranges` VALUES (2,'10.1.0.0/16','255.255.0.0','10.1.0.1');

To get from point A to point B I turned to sed. Instead of creating one crazy sed statement I usually find it easier to separate my task into multiple sed statements piped together. First I started with adding the "/16" to the end of the IP address range. To do this I first told sed to replace the first "," with "/16','". Notice how I am adding the "," back and adding a single quote on each side. The single quote will be needed around each address.

cat ipaddresses | sed "s/,/\/16','/" 

Notice that the "\" is escaped by putting a "/" in front of it. Also the entire sequence is in double quotes. I found this makes it easier to escape special characters. I first made the mistake of adding a "/g" at the end of said statement, however this means global and will replace all the "," which we don't want todo. We only want to replace the first one. Now we have:

10.1.0.0/16','255.255.0.0,10.1.0.1
10.2.0.0/16','255.255.0.0,10.2.0.1
10.3.0.0/16','255.255.0.0,10.3.0.1
10.4.0.0/16','255.255.0.0,10.4.0.1
10.5.0.0/16','255.255.0.0,10.5.0.1
10.6.0.0/16','255.255.0.0,10.6.0.1
10.7.0.0/16','255.255.0.0,10.7.0.1
10.8.0.0/16','255.255.0.0,10.8.0.1
10.9.0.0/16','255.255.0.0,10.9.0.1
...

Next we need to add the first part of the sql statement.

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" 

We pipe the previous statement into sed again and the start of each line indicated by a "^" with the first part of the sql statement. Again making sure to escape the single quotes. Also add the single quote at the end so the ip address will be now entirely in single quotes. Now we have:','

INSERT INTO `ipaddressranges` VALUES (2,'10.1.0.0/16','255.255.0.0,10.1.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.2.0.0/16','255.255.0.0,10.2.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.3.0.0/16','255.255.0.0,10.3.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.4.0.0/16','255.255.0.0,10.4.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.5.0.0/16','255.255.0.0,10.5.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.6.0.0/16','255.255.0.0,10.6.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.7.0.0/16','255.255.0.0,10.7.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.8.0.0/16','255.255.0.0,10.8.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.9.0.0/16','255.255.0.0,10.9.0.1

Next we need to finish the quotes around the subnet and start the quotes around the gateway so we replace the 3rd comma with "','".

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" | sed "s/,/','/3"

Notice the "/3" at end of the last sed statement which indicates the 3rd occurrence on each line. Now to finish the line we need to add a '); to the end of each statement. Todo this I replaced the end of the line "$" with "');".

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" | sed "s/,/','/3"| sed "s/$/');/" > output.sql

So this should produce what we need and store it into output.sql. However if you ran this you will notice two issues. One I forgot to replace the ID number in the beginning of the sql statement. This will create a problem when you go to import this into mysql. You will also notice a "^M" added in end of each line. I am not sure how I introduced this, if anyone knows, please comment. Regardless in order to fix both of these issues I turned to vim. First to replace the "^M" open output.sql in vim and enter the command :%s/ctrl V ctrlM//g. This will substitute all (/g) the "^M" with nothing hence removing them. Next to fix the id number we can use vim to replace it with an incrementing variable. To accomplish this type the command:

 :let i=1 | g/2/s//\=i/ | let i=i+1

This creates a variable "let i =1" and globally "g/" substitutes "s/" the number "2" with i's current value "/\=i". After each replacement it increments i. "let i=i+1". So finally we have the output we desire.

INSERT INTO `ipaddressranges` VALUES (1,'10.1.0.0/16','255.255.0.0','10.1.0.1');
INSERT INTO `ipaddressranges` VALUES (2,'10.2.0.0/16','255.255.0.0','10.2.0.1');
INSERT INTO `ipaddressranges` VALUES (3,'10.3.0.0/16','255.255.0.0','10.3.0.1');
INSERT INTO `ipaddressranges` VALUES (4,'10.4.0.0/16','255.255.0.0','10.4.0.1');
INSERT INTO `ipaddressranges` VALUES (5,'10.5.0.0/16','255.255.0.0','10.5.0.1');
INSERT INTO `ipaddressranges` VALUES (6,'10.6.0.0/16','255.255.0.0','10.6.0.1');
INSERT INTO `ipaddressranges` VALUES (7,'10.7.0.0/16','255.255.0.0','10.7.0.1');
INSERT INTO `ipaddressranges` VALUES (8,'10.8.0.0/16','255.255.0.0','10.8.0.1');
INSERT INTO `ipaddressranges` VALUES (9,'10.9.0.0/16','255.255.0.0','10.9.0.1');

Python and LDAP

I recently found myself working with a custom application similar to nagios. The application checked for services on a regular interval. I needed to add the ability to check and query LDAP server on this interval. The application was already written in python and therefore it made sense to create a python script to connect and query the LDAP server. Like always after a short time googling I found python has a module for communicating with an LDAP server, called python-ldap. More documentation can be found here. However before I dove into creating a python script to accomplish this I needed to setup a test environment. I currently did not have an LDAP server running and therefore naturally wanted to create a Virtual Machine (VM) to host one. Being the lazy computer geek I am, I didn't want to have to create one from the ground up. I discovered turnkey had a prebuilt OpenLDAP VM. I found this to be extremely easy to use. Upon boot the user is prompted to create a couple of passwords and is then bought to the screen show below.

As you can see in the picture the server comes complete with a LDAPadmin web interface, a web shell and a Webmin interface. Perfect utility if you need to quickly test accessing a LDAP server. Now to write that python script. For this I used a Fedora 16 x64 VM I had built for an earlier project. A simple

sudo yum install python-ldap

provided me with the library needed. For this project I was using Python 2.7. First order of business, connecting to the LDAP server.

    l = ldap.open("172.16.133.144")
    l.protocol_version = ldap.VERSION3

It is also important to set the ldap version since there is a difference in searching. In LDAP v2 it is required to do a bind to search where in LDAP v3 a bind is not required. Now that we have a session we need to get a message ID for the operation we want to complete, in this case a search. In order to get the message id we will need to pass the search parameters to the LDAP server. First declare the parameters as variables.

    baseDN = "dc=example,dc=com"
    searchScope = ldap.SCOPE_SUBTREE
    retrieveAttributes = None
    searchFilter = "cn=*"

We are using the default Turnkey LDAP server configuration for simplicity and testing. You would want to change these parameters based on what you actually wanted to search. Here we are setting up to search the example dc and search for everything. Next we pass these parameters to the ldap server to receive a message ID. A message ID is unique to each session and operation. Since we want to search the server we will use the search function and store the returned id.

     ldap_result_id = l.search(baseDN, searchScope, searchFilter, retrieveAttributes)

Now that we have the message ID we can loop through the results of the search. Using the ldap result function we can pull each result which is returned as a tuple. I will first store each result type and result data separately, then check to make sure data isn't empty. If data isn't empty and result type is indeed a search result I will append the data to a list.

    result_set = []
    while 1:
        result_type, result_data = l.result(ldap_result_id, 0)
        if (result_data == []):
            break
        else:
            if result_type == ldap.RES_SEARCH_ENTRY:
                result_set.append(result_data)
    print result_set

I am appending the data to a list only to print it. In a more effective application you could use the returned data to preform what ever purpose your application has. That's it. We have connected to the server and preformed a query. There is no reason to unbind since we used LDAP version 3. If you used version 2 you would want to preform and l.unbind_s(). Below is two sets of code, one is a basic test code with a little error handling built in, and the other does the same task however also takes the server IP, and search parameters as command line arguments. Please if you have any questions, comments or feel I have stated something inaccurate post below. Super basic:

#!/usr/bin/python

import ldap

## first you must open a connection to the server
try:
   print "Connecting"
   l = ldap.open("172.16.133.144")
   l.protocol_version = ldap.VERSION3 
except ldap.LDAPError, e:
   print "Exception caught while trying to connect."
   print e
   exit(-1)

baseDN = "dc=example,dc=com"
searchScope = ldap.SCOPE_SUBTREE
retrieveAttributes = None 
searchFilter = "cn=*"

try:
   ldap_result_id = l.search(baseDN, searchScope, searchFilter, retrieveAttributes)
   result_set = []
   while 1:
    print "Searching..."
    result_type, result_data = l.result(ldap_result_id, 0)
    if (result_data == []):
     break
    else:
     if result_type == ldap.RES_SEARCH_ENTRY:
      result_set.append(result_data)
   print result_set
except ldap.LDAPError, e:
   print "Exception caught while trying to search."
   print e
   exit(-1)

Command line arguments:

#!/usr/bin/python

import ldap
import sys,getopt

def ldapCheck(ip,baseDN,searchFilter):
   ## first you must open a connection to the server
   try:
    print "Connecting"
    l = ldap.open(ip)
    l.protocol_version = ldap.VERSION3 
   except ldap.LDAPError, e:
    print "Exception caught while trying to connect."
    print e
    exit(-1)

   #baseDN = "dc=example,dc=com"
   searchScope = ldap.SCOPE_SUBTREE
   retrieveAttributes = None 
   #searchFilter = "cn=*"

   try:
    ldap_result_id = l.search(baseDN, searchScope, searchFilter, retrieveAttributes)
    result_set = []
    while 1:
     print "Searching..."
     result_type, result_data = l.result(ldap_result_id, 0)
     if (result_data == []):
      break
     else:
      if result_type == ldap.RES_SEARCH_ENTRY:
       result_set.append(result_data)
    print result_set
   except ldap.LDAPError, e:
    print "Exception caught while trying to search."
    print e
    exit(-1)

def main(argv):
   ip = ''
   baseDN = ''
   searchFilter = ''
   try:
      opts, args = getopt.getopt(argv,"hi:b:s:",["ipAddr=","lbaseDN=","lsearchFilter="])
   except getopt.GetoptError,e:
      print 'ldapCheck.py -i  -b  -s '
      print e
      sys.exit(2)
   if not opts:
      print 'ldapCheck.py -i  -b  -s '
      exit(-1)
   for opt, arg in opts:
      if opt == '-h':
         print 'ldapCheck.py -i  -b  -s '
         sys.exit(2)
      elif opt in ("-i", "--ipAddr"):
         ip = arg
      elif opt in ("-b", "--lbaseDN"):
         baseDN = arg
      elif opt in ("-s","--lsearchFilter"):
         searchFilter = arg

   ldapCheck(ip,baseDN,searchFilter)


if __name__ == "__main__":
   main(sys.argv[1:])