If you wish to delete all of the records from a table in an SQLite database, you can do so using a command in the form delete from tableName where tableName is the name of the table from which you wish to remove all of the rows in the table. E.g., if I wished to delete all of the records in a table named TimeStamps, I could use the delete command below.

sqlite> select * from Timestamps;
2025-05-01 21:26:22|2025-05-01 21:23|10947739
2025-05-01 21:28:33|2025-05-01 21:28|10967247
2025-05-04 13:37:47|2025-05-04 13:36|18079581
sqlite> delete from Timestamps;
sqlite> select * from Timestamps;
sqlite>

If I only wanted to delete a record or records meeting a specific condition, I could specify that condition in a WHERE clause. E.g., if the columns in the table were CurrentTimestamp, LastUpdateTimestamp, and Size and I only wanted to remove the row where the value of CurrentTimestamp is 2025-05-04 13:37:47, I could use the command below, instead.

sqlite> .schema Timestamps
CREATE TABLE "Timestamps" (
        "CurrentTimestamp"      TEXT NOT NULL,
        "LastUpdateTimestamp"   TEXT NOT NULL,
        "Size"  INTEGER NOT NULL
);
sqlite> delete from Timestamps where CurrentTimestamp='2025-05-04 13:37:47';

[/software/database/sqlite] permanent link

[More Info]

[/software/database/sqlite/db_browser] permanent link

I have an SQLite database that I use to track approval of tasks. Every Monday, I need to generate a count of the number of tasks approved from the prior Tuesday through the Monday on which I'm creating the report. The approval dates are stored in the database as an integer and I enter them in the form 2018-05-14. I use the following SQL command in a Python script to determine the number I've approved in the last week:

sql = 'SELECT COUNT(*) FROM Tasks WHERE Approved >= date(CURRENT_DATE,"-6 day")'

But I noticed that the count didn't always include the tasks I approved on the prior Tuesday. E.g., when I ran the script tonight, May 14, the number reported was 5, yet I expected the result to be 7.

[ More Info ]

[/software/database/sqlite] permanent link

I upgraded the version of DB Browser for SQLite on my MacBook Pro today to the latest version, 3.9.1v2. Before upgrading, I had version 3.8.0 on the system. When I checked the version from a command line interface, i.e., a Terminal window, using the system_profiler command, I didn't find any references to the program when I searched for "SQLite:", but I saw the version number listed for "sqlitebrowser."

$ system_profiler SPApplicationsDataType | grep -i "SQLite:" -A 2
$ system_profiler SPApplicationsDataType | grep -i "Browser:" -A 2
    sqlitebrowser:

      Version: 3.8.0
$ system_profiler SPApplicationsDataType | grep -i "sqlitebrowser" -A 2
    sqlitebrowser:

      Version: 3.8.0
--
      Location: /Applications/sqlitebrowser.app
      Get Info String: DB Browser for SQLite

$

[ More Info ]

[/software/database/sqlite/db_browser] permanent link

If you wish to query an SQLite database using PHP, you can do so by placing commands similar to those shown below within the code for a webpage:

<?php

$filename = "/Users/jasmith1/Documents/www/test/presidents.db";

$db = new SQLite3($filename);

$results = $db->query('SELECT * FROM Terms');
while ($row = $results->fetchArray()) {
    var_dump($row);
    echo "<br>\n";
}
?>

In the example above, the database location and file name is stored in the variable $filename. The file must be in a location accessible by the webserver and the file permissions must allow access to the file.

[ More Info ]

[/software/database/sqlite] permanent link

If you need to redirect the output of SQL commands to a file while using SQLite, you can do so using the .output command. E.g., I have an SQLite database on my MacBook Pro laptop running OS X that contains a table named Equipment. Within that table is a column named Device that is a description for the particular piece of equipment in the table entry. I can view just that field for all records with the SQLite command SELECT Device FROM Equipment. To direct the output of the command to a text file named device.txt, I can use the command .output device.txt. After executing the command to select the Device field from all records, I can then issue the .output command without any arguments to it to return to having the output of commands displayed on the console rather than going to the file.

$ sqlite3 ~/Documents/Work/CRQ/CRQ.db
SQLite version 3.8.10.2 2015-05-20 18:17:19
Enter ".help" for usage hints.
sqlite> .output device.txt
sqlite> SELECT Device FROM Equipment;
sqlite> .output
sqlite>

[/software/database/sqlite] permanent link

$ 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

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

[ More Info ]

[/software/database/sqlite] permanent link

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