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)