I track work requests in an SQlite database. For each request in a
table named "Tasks" in the database, there is a
column holding the date the request was approved. Every week I need to include
the number of requests approved that week in a weekly report. I can do that with
the Structured
Query Language (SQL) command SELECT COUNT(*) FROM Tasks WHERE Approved
>= date(CURRENT_DATE,"-7 day"). I have a
Python script that queries the
SQL database to count the requests approved within the last week,
but sometimes I want to determine the number of requests approved
since a particular date. So I modified the script to accept a date
provided as an argument on the command line and, if a date is
specified, to determine the number of entries where the approval
date matches the specified date or is later than the specified date.
That code for that script,
weeklyApprovalCount.py, is included below:
#!/usr/bin/python
# Name: weeklyApprovalCount
# Purpose: Count the number of approved requests since a specified date or, if
# no date is specified, the number approved in the last 7 days.
# Version: 1.10
# Last modified: 2017-04-07
import sys
# Check for a date argument on the command line. If one has been entered use
# that date as the starting date for locating requests by approval date. Dates
# are stored in the database as integers. Enter the date in the form YYYY-MM-DD
# where YYY is the year, MM the month, and DD the day.
#
# If a date has not been provided on the command line, provide a count of requests
# approved within the last 7 days.
try:
sys.argv[1]
except IndexError:
theDate = ""
else:
theDate = sys.argv[1]
import sqlite3
conn = sqlite3.connect('/Users/jasmith1/Documents/Work/CRQ/CRQ.db')
cursor = conn.cursor()
if theDate is not "":
sql = "SELECT COUNT(*) FROM Tasks WHERE Approved >= date('" + theDate + "')"
print "SQL:", sql
else:
sql = 'SELECT COUNT(*) FROM Tasks WHERE Approved >= date(CURRENT_DATE,"-7 day")'
cursor.execute(sql)
result = cursor.fetchone()
numApproved = result[0]
print numApprovedI import the module, sys to check for an argument on the command
line, sys.argv[1]. If it is present, the
variable theDate is set to be that date. If it is not
present, the SQL query will use a date that is equal to the current date minus 7
days. The SQL query is constructed from SELECT COUNT(*) FROM Tasks WHERE
Approved >= date(' with the value for theDate appended to the
line with "+", which is the Python
string concatenation operator - see
Python Strings -
') is appended to end the SQL command.
I import the Python module sqlite3 so that I can use Python to query the
SQLite database, which in this case is stored on my MacBook Pro laptop running
OS X. I can then use
sqlite3.connect('Database_Location_and_Name') where
Database_Location_and_Name is the path to the database file and the
name of the file to connect to the database. I can then create a "cursor object"
as explained at the Python Software Foundation page
sqlite3 — DB-API 2.0
interface for SQLite databases that will allow me to then call
its execute method to execute SQL commands. The Python command
cursor.execute(sql) executes the SQL command stored
in the variable sql. The result of the query, which is
the number of approved requests, is stored in the
tuple
result with the command cursor.fetchone(). The
cursor.fetchone() method retrieves the next row of a query result
set and returns a single sequence, or None if no more rows are available.
By default, the returned tuple consists of data returned by the MySQL server,
converted to Python objects.