Smart Cities - SQLite3 database: Difference between revisions
Jump to navigation
Jump to search
| Line 71: | Line 71: | ||
= Creating a SQLite Database using Python = | = Creating a SQLite Database using Python = | ||
* Enter the command '''nano sqlite_test.py''' to create a new python application | * In the pi user home directory create a new directory named '''solar_python'''. We will use this directory for this activity. | ||
* Enter the command '''nano sqlite_test.py''' to create a new python application. | |||
[[File:Screen Shot 2023-01-20 at 5.44.27 pm.png | 900px]] | [[File:Screen Shot 2023-01-20 at 5.44.27 pm.png | 900px]] | ||
| Line 81: | Line 82: | ||
* Run the file with the command '''python3 sqlite_test.py | * Run the file with the command '''python3 sqlite_test.py | ||
* The python file will create a new database named ''' | * The python file will create a new database named '''SQLite_Python.db''' | ||
[[File:Screen Shot 2023-01-20 at 5.44.41 pm.png | 900px]] | [[File:Screen Shot 2023-01-20 at 5.44.41 pm.png | 900px]] | ||
* Verify that the database has been created by listing the contents of the directory. | |||
* You should see the file '''SQLite_Python.db''' | |||
[[File:Screen Shot 2023-01-20 at 5.41.57 pm.png | 900px]] | |||
* Finally, using the GUI based DB Browser for SQLite you can open the new database file. | |||
* Even though the database exists it wont have any Tables. | |||
[[File:Screen Shot 2023-01-20 at 5.42.46 pm.png | 900px]] | |||
Revision as of 08:39, 20 January 2023
Introduction
- SQLite3 is a single file database that is easy to install and access.
- It responds to SQL queries.
- Unlike other databases it does not require a database server.
- Because the SQLite3 database exists as a single file on a computer it is easy to copy and backup the database.
- Data kept on the database can be easily shared with students.
Installation of SQLite3 on Raspberry Pi
- In the Terminal enter the command sudo apt install sqlite3
Installing Database Browser software
- In the Terminal enter the command sudo apt install sqlitebrowser
- This will take approximately 30 seconds to install.
- Once the software is installed it will appear in the Raspberry Pi menu under Programming.
- The name of the program is DB Browser for SQLite
- This software gives complete access to the database to view and edit data.
Creating a database and table in SQLite3 using DB Browser for SQLite
- Open the DB Browser for SQLite
- In the main menu, click on New Database
- Navigate to the user home directory /home/pi
- Enter the file name data.db
- Click Save.
- Under Table - Give the database Table the name measurements
- Under Fields - Add field
- Name id - this is a simple Primary Key. Ensure that the following additional options are selected:
- Type integer
- No - Not null (cannot be left empty)
- PK - Primary Key (unique number for each row entry)
- AI - Auto increment
- Name id - this is a simple Primary Key. Ensure that the following additional options are selected:
- Add fields for V battery voltage and I current
- Click on OK to return to the main menu.
- More fields can be added to the table later by clicking on the Table and then selecting Modify Table from the menu.
- Click on Write Changes to commit these changes (save settings)
- Always remember to Close Database when finished.
Creating a SQLite Database using Python
- In the pi user home directory create a new directory named solar_python. We will use this directory for this activity.
- Enter the command nano sqlite_test.py to create a new python application.
- Enter the following python code the nano editor.
- Exit and save changes
- Run the file with the command python3 sqlite_test.py
- The python file will create a new database named SQLite_Python.db
- Verify that the database has been created by listing the contents of the directory.
- You should see the file SQLite_Python.db
- Finally, using the GUI based DB Browser for SQLite you can open the new database file.
- Even though the database exists it wont have any Tables.
