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)
