I have an SQLite database that I use to track work requests on my MacBook Pro running OS X El Capitan (10.11.6) . Every week I need to determine the number of change requests I've reviewed and approved in the last week. I have been running SQL commands from a text file using sqlite3, but I wanted to start using a Python script, instead, since I want to everntually perform some additional queries as well and display the results as a web page. Initially, I had some problems displaying the record count, but found I could use
cursor.fetchone() to display the count. The script I currently
have consists of the following code:#!/usr/bin/python
import sqlite3
conn = sqlite3.connect('/Users/jasmith1/Documents/Work/CRQ/CRQ.db')
cursor = conn.cursor()
sql = 'SELECT COUNT(*) FROM Tasks WHERE Approved >= date(CURRENT_DATE,"-7 day")'
cursor.execute(sql)
result = cursor.fetchone()
numApproved = result[0]
print numApprovedThe first step I need to take is to import the sqlite3 module for use
in Python. I then need to create a
connection object for the database which I assign to conn.
After that I need to create a
cursor
instance that I will use to execute an
SQL statement.
I assign the SQL statement to the variable sql (the name is
arbitrary) and then execute the statement with
cursor.execute(sql). The variable result is then
assigned the results of cursor.fechone(). If I printed the contents
of the result variable, I would see (5,), if the count
was 5, since the result is a
tuple with
one element, but I want to see just the number, so I set
numApproved to be result[0]. Instead of setting
numApproved to result[0] and printing
numApproved, I could skip a step and use
the following two lines after sursor.execute(sql):
(numApproved,) = cursor.fetchone() print numApproved
If I make the script excecutable with chmod 755 and then run
the script, I will then see just the number of reccords approved
within the last week:
$ chmod 755 weeklyApprovalCount.py $ ./weeklyApprovalCount.py 5
References:
