SQLite is a lightweight but powerful database management tool. It occupies a little space on the device – a bit more than 500KB, but offers most of the features that are implemented on other SQL database tools.
SQLite is a serverless engine, meaning that the whole database is stored in a single file, and all transactions are reads and write from the file. There are no initial server configurations required – all you need is to create a .db file and start writing queries. This is why SQLite is preferred on small devices like mobile phones, embedded devices, and Raspberry Pi.
Previously, we have installed an SQLite into Raspberry Pi, created database and table with only a few lines of script. We performed all the tasks from the command line tool.
Eventually, you will want to have full control of the database and see the whole picture. Therefore, you should also consider installing SQLite manager, which brings a graphical user interface to work with SQL databases.
To run the SQLite manager, you will need to work from the Raspberry Pi desktop. You may want to connect a monitor, or like me, use a VNC client to access the Raspberry Pi desktop. VNC already comes with Raspbian; all you need is to download client software to your PC.
Probably most convenient and open source SQLite manager is so-called “DB Browser for SQLite.” It works with all OS platforms including Windows, MacOS, and Linux. DB Browser allows all basic controls including creating, modifying, deleting tables, records. You can import, export databases to CSV, SQL dumps, perform queries. Keep the LOG of all commands. Besides, there is an ability to draw graphs directly from data tables.
First, you need to install SQLite into Raspberry Pi. First, there is a good practice to update the cache with the command run from terminal:
sudo apt-get update
Then install the SQLite package:
sudo apt-get install sqlite3
Then you can install SQLite browser:
sudo apt-get install sqlitebrowser
This is all you need to start using the SQLite manager. From this point, you can close the terminal screen and perform all necessary operations from the GUI.
You can start by creating a new database file. To do so, you can click on the ‘New Database’ button where you will be asked for the database file name and location:
After the database is ready, you can create your first table by clicking on the ‘New Table’ button and in the new window, you can start adding table fields. In the bottom a script of execution is also visible:
To fill your new table with data, go to ‘Browse Data’ tab and fill it by pressing the ‘New Record’ button:
Entering data manually is a tedious task, and it is not performed in such a way. Usually, the database is filled with data by application, like a python script, which reads temperature and barometric pressure sensor and stores values it to SQLite automatically. SQLite manager is better used to analyze, edit or modify data.
Tables can be created and filled with data by executing scripts in the manager. As an example, they look as follows:
CREATE TABLE sensor(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, pressure NUMERIC, currentdate DATE, currentime TIME); INSERT INTO sensor(temperature, pressure, currentdate, currentime) values(23.7, 1000.1, date('now'), time('now'));
SQLite manager is a great visual tool for replacing command line scripts. You can perform most of the tasks without prior knowledge of SQL, but in time, you should learn at least basic SQL to understand how things work and how to fix various problems related to databases.
I see data plotting feature very convenient. You can draw basic graphs from a data table with only two button clicks. Plots can be exported to an image for your own use.