sqlite

The usual DB choices are MySQL or postgres. Raspberry Pi servers need something simple like a file. I knew SQLite was the correct choice when I read,

    • http://sqlite.org/ is smaller than MySQL or postgres

    • SQLite competes with fopen. and

    • SQLite is not directly comparable to MySQL, or postgres because it is trying to solve a different problem.

sqlite is a lightweight database ideally suited for Raspberry Pi applications.

python3 and sqlite3 example

Step 1. Install sqlite

Determine the latest version of sqlite by going to this site: sqlite. As of 12APR2020, the latest version is sqlite3

On a laptop,

      • open a terminal window

      • ssh into the Raspberry Pi and

      • run the following command to install sqlite3:

$ sudo apt-get install sqlite3 -y

Step 2. Create an empty database

Create database called security.db or whatever you want it to be:

$ sqlite3 security.db

Create a table

sqlite > BEGIN;

sqlite > CREATE TABLE status (tdate DATE, ttime TIME, name TEXT, value TEXT);

sqlite > COMMIT;

Check the table was created correctly, and then quit:

sqlite > .schema

sqlite> .quit

If necessary make directories:

$ sudo mkdir /var/www

$ sudo mkdir /var/www/db

Move the database and change access:

$ sudo mv security.db /var/www/db/security.db

$ sudo chmod og+rw /var/www/

$ sudo chmod og+rw /var/www/db

$ sudo chmod og+rw /var/www/db/security.db