Smart Cities - SQLite3 database: Difference between revisions
Jump to navigation
Jump to search
| Line 69: | Line 69: | ||
[[File:Screen Shot 2023-01-17 at 12.43.45 am.png | 900px]] | [[File:Screen Shot 2023-01-17 at 12.43.45 am.png | 900px]] | ||
= | = Interacting with SQLite Databases using Python = | ||
* Python script can create SQLite databases, tables and data entries [https://pynative.com/python-sqlite/#h-python-sqlite-database-connection]. | |||
* Python allows full interaction with the database using SQL commands. | |||
== 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. | * 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 command '''nano sqlite_test.py''' to create a new python application. | ||
Revision as of 08:45, 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.
Interacting with SQLite Databases using Python
- Python script can create SQLite databases, tables and data entries [1].
- Python allows full interaction with the database using SQL commands.
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.
