Acoptex.com

members

Raspberry basics: Project 15a

Project name: Raspberry PI 3 model B board - SQlite

Tags: Raspberry, Raspberry PI 3 model B board, SQlite

In this project, you need these parts :

1.Raspberry PI 3 model B 1 pc

2. Micro SD card and SD card adapter 1 pc

3. Micro USB power supply (2.1 A, max 2.5 A) 1 pc

4. USB keyboard 1 pc

5. USB mouse 1 pc

6. TV or PC monitor 1 pc

General

We will learn how to install SQLite on Raspberry PI 3 model B board. We are going to make a data logging application with a Raspberry Pi 3 that will store temperature and humidity in this project.

Understanding the SQlite

SQLite is an in-process light weight library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine.

SQLite is the most widely deployed SQL database engine in the world and its source code is in the public domain.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

Features:

• A complete SQLite database is stored in a single cross-platform disk file;
• SQLite is very small and light weight;
• SQLite is self-contained (no external dependencies required)
• SQLite does not require a separate server process or system to operate (serverless)
• SQLite comes with zero-configuration (no setup needed)
• SQLite is cross-platform. It’s available on UNIX (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT)

Databases are used to store information in a way that can be accessed quickly and easily.

You need the Raspbian OS installed on your Raspberry PI 3 model B board.

Understanding the Raspberry PI 3 model B

The Raspberry Pi 3 is the third-generation Raspberry Pi. It replaced the Raspberry Pi 2 Model B in February 2016.

Specification:

• Quad Core 1.2GHz Broadcom BCM2837 64bit CPU
• 1GB RAM
• BCM43438 wireless LAN and Bluetooth Low Energy (BLE) on board
• 40-pin extended GPIO
• 4 USB 2 ports
• 4 Pole stereo output and composite video port
• Full size HDMI
• CSI camera port for connecting a Raspberry Pi camera
• DSI display port for connecting a Raspberry Pi touchscreen display
• Micro SD port for loading your operating system and storing data
• Upgraded switched Micro USB power source up to 2.5A

Signals and connections of the Raspberry PI 3 model B

Step by Step instruction

We recommend using a high-performance SD card for increased stability as well as plugging your device into an external display to see the default application booting up.

1. Setup and preparation

We assume that you have Windows 10 installed on your PC and Raspbian OS installed on your Raspberry PI 3 board.

1. Insert your micro SD card with Raspbian OS to Raspberry Pi 3 board.
2. Connect the TV or PC monitor Display port (HDMI Port) to your Raspberry PI 3 board HDMI Port (HDMI cable required).
3. Make sure that your monitor or TV is turned on, and that you have selected the right input (e.g. HDMI 1,  etc).
4. Plug in your USB mouse and USB keyboard to Raspberry PI 3 board USB ports.
5. Get connected. Connect an Ethernet cable to 10/100 LAN port of Raspberry Pi 3 or plug in WiFi adapter (see the list of supported here) to USB port of Raspberry Pi 3.
6. Connect Micro USB power supply to Raspberry PI 3 board micro USB input.
7. The Raspberry PI desktop will start up.
8. Open Terminal window and type the command: sudo apt-get update
9. Then type the command: sudo apt-get install 

2. Installing SQLite

1. InTerminal window type the command to install SQlite: sudo apt-get install sqlite3
2. 
   
3. You may need to confirm the installation by pressing Y button and Enter button.
4. When the installation done, the SQLite libraries are supplied with an SQLite shell. Use this command to invoke the shell and create a database: sqlite3 sensordata.db
5. 
6. The file sensordata.db created now. A prompt appears where you can enter commands. The shell supports two types of commands. Commands that start with a '.' are used to control the shell. Try to type this command: .help This command can quickly remind you about all supported commands and their respective usage. 
7. To quit from the SQLite shell use the .quit command.
8. You can use the up arrow to scroll through previous commands.

3. Using SQL

Structured Query Language (SQL) is a language used for interacting with databases. It can be used to create tables, insert, update, delete and search for data. SQL works with different database solutions such as SQLite, MySQL and others. SQL statements must end with a semicolon (;). It’s common for SQL commands to be capitalized, but this isn’t strictly necessary. Most people prefer to use capitalized letters, because it increases readability.

CREATE TABLE

1. We will create a simple table with 6 columns that could be used for temperature and humidity logging application in different parts of a house. Use these commands:
2. BEGIN;
3. CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currentime TIME, device TEXT);
4. COMMIT;
5. 
6. You can see all tables available with the command: .tables
7. You can see the fullschema of the tables when you enter the command: .fullschema
8. 
   

INSERT

1. To insert new temperature and humidity readings in the database, you use these commands:
2. BEGIN;
3. INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(24.5, 50, date('now'), time('now'), "manual");
4. COMMIT;

SELECT

1. To access the data stored in the database, we use the SELECT SQL statement: SELECT * FROM dhtreadings;
2. We have one reading inserted in the database now. Let's insert one more:
3. BEGIN;
4. INSERT INTO dhtreadings(temperature, humidity, currentdate, currentime, device) values(23.1, 50.2, date('now'), time('now'), "manual");
5. COMMIT;
6. 
7. When we use the SELECT SQL statement: SELECT * FROM dhtreadings; now it will be two readings in the table.
8. For better understanding of SQL table check the MSExcel table below:
9. 

DROP

1. If you want to completely delete the table from your database, you can use this command: DROP TABLE dhtreadings;
2. It will delete the dhtreadings table from your database.
3. Type this command to check for a table in database: .tables . It doesn’t return anything as your table deleted.
4. 

Summary

We have learnt how to install SQLite on Raspberry PI 3 model B board.

Library

• No libraries needed in this project

Sketch

• No sketches needed in this project