Executing a MySQL query on command line via os.system in Python

Question

I am trying to pass the 'day' from the while loop into a sql statement that then gets passed into a MySQL command line to be executed with -e

I can not use the DB module or other python libraries to access MySQL, it needs to be done via command line. It also looks like I might need to convert the day to a string before concatenating to sql?

#!/usr/bin/python

import datetime


a = datetime.date(2009, 1, 1)
b = datetime.date(2009, 7, 1)
one_day = datetime.timedelta(1)

day = a

while day <= b:
 print day

 sql="SELECT Date,SUM(CostUsd) FROM Stats d WHERE d.Date = " + day + " GROUP BY Date"

 print "SELECT Date,SUM(CostUsd) FROM Stats d WHERE d.Date = " + day + " GROUP BY Date"

 os.system('mysql -h -sN -u -p -e " + sql + " > /home/output/DateLoop-" + day + ".txt db')
 day += one_day

Would it be possible to set this up to have the SQL as an input file and pass the day as a string to that? The query might become more complex or even require several queries and that might become a problem trying to pass as a string.

I am open to any ideas as long as the query can take the date as input, name the output file with the same date and do it from the command line MySQL client

Answer 1

Try explicit formatting and quoting resulting string:

sql = "....WHERE d.Date = '" + date.isoformat() + "' GROUP BY ..."

Quotes at os.system call are messy and redirection look weird (if it's not a typo)

os.system("mysql db -h -sN -u -p -e '" + sql + "' > /home/output/DateLoop-" + day + ".txt")

Answer 2

Well, you can save the mysql template query in a config file and parse it with ConfigParser:

The config file will look like that:

[mysql query configuration]
dbhost = 
db = 
username = guest
password = 

[query template]
template = SELECT Date, SUM(CostUsd).......

or you can just store it to a separate file and then read it with the standard open(filename).read, etc. If you think that the query will become more complex in the future, the config file approach may be simpler to manage and understand, but it is not a big difference.

To get the date as a parameter, you can use sys.argv, or a library like optparse

Answer 3

Code below might help you out. It isn't particularly exciting and is deliberately simple. This is not the way many programmers would tackle this problem, but without more info it seems to fulfil your requirements.

I have also made an assumption that you are new to python; If I'm wrong, feel free to ignore this post.

• Allows the passing of database credentials, output directory and dates (start and end) on the command line.
• Uses subprocess in place of os.system. Subprocess provides the preferred mechanisms to call external executables from python. This code uses the simplest of them; call() as it is similar to os.system()
• Uses optparse to process the command line arguments. Whilst the code is certainly longer and more verbose, it will be easier for you to make additions and modifications to the arg processing in the future. It is also pretty clear what is going on (and code is always read far more often than it is written).
• The command line setup only runs when the script is executed as it is within the __main__ block. As the "logic" of the script is within the main() method, you can also import it and provide the options object (and arg list) from another source.

If you can remove the need to output each date in a separate file, you can have the database engine calculate the SUM() and group them by date. You would get all sums back in one db call which would be quicker and could yield simpler code.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import datetime
import os
import subprocess
from optparse import OptionParser

SQL = """SELECT d.Date, SUM(d.CostUsd) FROM Stats d WHERE d.Date = '%s' GROUP BY d.Date"""


def get_stats(options, dateobj):
    """Return statistics for the date of `dateobj`"""
    _datestr = dateobj.strftime('%Y-%m-%d')
    sql = SQL % _datestr
    filepath = os.path.join(options.outdir, 'DateLoop-%s.txt' % _datestr)
    return subprocess.call('mysql -h %s -u %s -p -sN -e "%s" db > %s' % (options.dbhost, options.dbuser, sql, filepath), shell=True)


def main(options, args):
    """"""
    _date = options.startdate
    while _date <= options.enddate:
        rs = get_stats(options, _date)
        _date += datetime.timedelta(days=1)


if __name__ == '__main__':
    parser = OptionParser(version="%prog 1.0")
    parser.add_option('-s', '--startdate', type='string', dest='startdate', 
        help='the start date (format: yyyymmdd)')

    parser.add_option('-e', '--enddate', type='string', dest='enddate', 
        help='the end date (format: yyyymmdd)')

    parser.add_option('--output', type='string', dest='outdir', default='/home/output/', 
        help='target directory for output files')

    parser.add_option('--dbhost', type='string', dest='dbhost', default='myhost', 
        help='SQL server address')

    parser.add_option('--dbuser', type='string', dest='dbuser', default='dbuser', 
        help='SQL server user')

    options, args = parser.parse_args()

    ## Process the date args
    if not options.startdate:
        options.startdate = datetime.datetime.today()
    else:
        try:
            options.startdate = datetime.datetime.strptime('%Y%m%d', options.startdate)
        except ValueError:
            parser.error("Invalid value for startdate (%s)" % options.startdate)

    if not options.enddate:
        options.enddate = options.startdate + datetime.timedelta(days=7)
    else:
        try:
            options.enddate = datetime.datetime.strptime('%Y%m%d', options.enddate)
        except ValueError:
            parser.error("Invalid value for enddate (%s)" % options.enddate)

    main(options, args)