MoonPoint Support Logo

 


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



Advanced Search
March
Sun Mon Tue Wed Thu Fri Sat
     
23 24 25
26 27 28 29 30 31  
2017
Months
Mar
Apr May Jun
Jul Aug Sep
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

Tue, Jun 14, 2016 10:12 pm

Count Records with SQL

I needed to count the number of records in a table I use for tracking tasks within an SQLite database. The table is named "Tasks" and, in addition to other columns has a Received column, where I record when I received a task and an Approved column, where I record the date I approved the task. If I only want to see the task numbers and the dates on which I received and approved tasks and I want the records ordered by approval date, I can use the following Structured Query Language (SQL) command:
sqlite> select Task, Received, Approved from Tasks ORDER BY Approved;
TAS000000204813|2016-04-20|
TAS000000160855|2016-06-09|
TAS000000302389|2016-06-08|
TAS000000289579|2016-05-04|2016-05-06
TAS000000297385|2016-05-11|2016-05-11
TAS000000294896|2016-05-04|2016-05-12
TAS000000294136|2016-05-04|2016-05-12
TAS000000291991|2016-05-04|2016-05-13
TAS000000289882|2016-05-10|2016-05-13
TAS000000293978|2016-05-04|2016-05-16
TAS000000262109|2016-05-18|2016-05-19
TAS000000293289|2016-05-24|2016-05-24
TAS000000300934|2016-05-18|2016-05-24
TAS000000192984|2016-05-20|2016-05-24
TAS000000268274|2016-05-19|2016-05-24
TAS000000280549|2016-05-24|2016-05-24
TAS000000289548|2016-05-18|2016-05-24
TAS000000291959|2016-05-24|2016-05-24
TAS000000293970|2016-05-26|2016-05-26
TAS000000280569|2016-05-31|2016-05-31
TAS000000297347|2016-05-31|2016-05-31
TAS000000283262|2016-06-01|2016-06-07
TAS000000304021|2016-06-01|2016-06-07
TAS000000311853|2016-06-01|2016-06-07
TAS000000311293|2016-06-01|2016-06-07
TAS000000312411|2016-06-08|2016-06-08
TAS000000309299|2016-06-09|2016-06-09
TAS000000270128|2016-06-09|2016-06-09
TAS000000255397|2016-06-13|2016-06-13
TAS000000295302|2016-06-09|2016-06-14
TAS000000299922|2016-06-08|2016-06-14
TAS000000298570|2016-06-14|2016-06-14
TAS000000315505|2016-06-08|2016-06-14
sqlite>

I can count all of the records in the table with the SQL command below:

sqlite> select COUNT(*) FROM Tasks;
33
sqlite>

Within the table, I have a field named "Approved" that I use to track the approval date for tasks; I store the date as a text field, since SQLite doesn't have a storage class specifically for dates. If I want to know how many tasks I've approved since a specific date, I can use a command like the one below to determine the number approved since the specified date.

sqlite> select COUNT(*) FROM Tasks WHERE Approved > "2016-06-07";
8
sqlite>

[ More Info ]

[/software/database/sqlite] permanent link

Wed, May 04, 2016 9:45 pm

DB Browser for SQLite on OS X

If you would like a graphical user interface (GUI) to view and manage SQLite databases, you can use DB Browser for SQLite, which was originally developed by Mauricio Piacentini of Tabuleiro Producoes as the Arca Database Browser. The program was later modified to be compatible with SQLite 2.x databases and rleased into the public domain. In 2014, the project was renamed to "Database Browser for SQLite" at the request of Richard Hipp , the creator of SQLite. The software is available for Microsoft Windows, Apple OS X, and Linux systems. You can use it to view the records in tables in existing databases, add new records to tables, etc.

[ More Info ]

[/software/database/sqlite] permanent link

Fri, Apr 08, 2016 10:26 pm

Using SQLite

An easy way to create and maintain Structured Query Language (SQL) databases at no cost is to use the free SQLite software, which is available for Linux, OS X, and Microsoft Windows systems and also as C source code.

SQLite is provided by Apple with the current version of OS X; I don't know when it was first included with OS X, but I know it has been present from at least OS X 10.8.5 (Mountain Lion). If you open the Terminal application, which you can find in /Applications/Utilities, and type which sqlite3, you should see the program.

[ 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