MoonPoint Support Logo

 


Shop Amazon Warehouse Deals - Deep Discounts on Open-box and Used ProductsAmazon Warehouse Deals



Advanced Search
February
Sun Mon Tue Wed Thu Fri Sat
     
11
       
2017
Months
Feb
Dec


Sat, Feb 11, 2017 10:27 pm

Using Python to query an SQLite database and return a count of records

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 numApproved

The 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:

  1. sqlite3 — DB-API 2.0 interface for SQLite databases
    Python documentation

[/languages/python] permanent link

Once You Know, You Newegg AliExpress by Alibaba.com

Shop Amazon Local - Subscribe to Deals in Your Neighborhood

Valid HTML 4.01 Transitional

Privacy Policy   Contact

Blosxom logo