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
     
8
       
2017
Months
Feb
Oct Nov Dec


Wed, Feb 08, 2017 8:41 pm

Running SQL commands from a text file using sqlite3

Every week I need to determine the number of tasks I reviewed and approved in the prior week. I track the tasks in a database on my MacBook Pro laptop using the SQLite utility that is part of the OS X operating system - see Using SQLite. I have a database named "CRQ" that contains a table named "Tasks". The schema for the table is shown below; approval dates are stored as integers; SQLite does not have a storage class specifically for dates, instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values1.

$ sqlite3 ~/Documents/Work/CRQ/CRQ.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .schema Tasks
CREATE TABLE "Tasks" (
`CRQ`	TEXT NOT NULL,
`Task`	TEXT NOT NULL,
`URL`	TEXT,
`Received`	INTEGER,
`Reviewed`	INTEGER,
`Approved`	INTEGER,
`Notes`	TEXT,
`Summary`	TEXT
);
sqlite> .exit
$

I can open the database and manually type the command to count the records with an approval date on or after a date I specify using an SQL command like the one below:

$ sqlite3 ~/Documents/Work/CRQ/CRQ.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> SELECT COUNT(*) FROM Tasks WHERE Approved >= '2017-02-01';
11
sqlite>

[ More Info ]

[/software/database/sqlite] 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