![]() At the shell, you would do it, data by data using SQL statements like this (For our example, you will do it 3 times): sqlite> INSERT INTO DHT_data VALUES(datetime('now'), 20.5, 30) Īnd in Python, you would do the same but at once: import sqlite3 as liteĬur.execute("INSERT INTO DHT_data VALUES(datetime('now'), 20.5, 30)")Ĭur.execute("INSERT INTO DHT_data VALUES(datetime('now'), 25.8, 40)")Ĭur.execute("INSERT INTO DHT_data VALUES(datetime('now'), 30.3, 50)") Same way was done with table creation, you can insert data manually via SQLite shell or via Python. Should you want to output the date in localized time, just convert it to the appropriate time zone afterward. Note that the time is in "UTC", what is good because you don’t have to worry about issues related to daylight saving time and other matters. The component timestamp will be real and taken from the system, using the built-in function 'now' and temp and hum are dummy data in oC and % respectively. Let's input on our database 3 sets of data, where each set will have 3 components each: (timestamp, temp, and hum). table command, the created tables names will appear (in our case will be only one: "DHT_table". Open the database shell: sqlite3> sensorsData.db You can verify it on SQLite Shell using the “.table” command. ![]() Wherever the method used, the table should be created. Run it on your Terminal: python3 createTableDHT.py Open the above code from my GitHub: createTableDHT.py It is not mandatory, but a good practice.Ĭur.execute("DROP TABLE IF EXISTS DHT_data")Ĭur.execute("CREATE TABLE DHT_data(timestamp DATETIME, temp NUMERIC, hum NUMERIC)") Also usually, those statements are written using capital letters. Sqlite> CREATE TABLE DHT_data (timestamp DATETIME, temp NUMERIC, hum NUMERIC) Īll SQL statements must end with " ". Open the database that was created in the last step: sqlite3 sensorsData.db And entering with SQL statements: sqlite> BEGIN Our table will be named "DHT_data" and will have 3 columns, where we will log our collected data: Date and Hour (column name: timestamp), Temperature (column name: temp), and Humidity (column name: hum). In order to log DHT sensor measured data on the database, we must create a table (a database can contain several tables). The "sqlite>" above is only to ilustrated how the SQLite shell will appear. ![]() The above Terminal print screen shows what was explained. Quit the shell to return to the Terminal: sqlite>. sqlite> Commands starts with a ".", like “.help”, ".quit", etc.Ĥ. Give a name and create a database like databaseName.db (in my case "sensorsData.db"): sqlite3 sensorsData.dbĪ "shell" will appear, where you can enter with SQLite commands. Move to this directory: cd mkdir Sensors_Database/ģ. Create a directory to develop the project: mkdir Sensors_Databaseģ. Install SQLite to Raspberry Pi using the command: sudo apt-get install sqlite3Ģ. So, be it! Let's install SQLite on our Piįollow the below steps to create a database.ġ. ( More on Wikipedia) We will not enter into too many details here, but the full SQLite documentation can be found at this link: SQLite has bindings to many programming languages like Python, the one used on our project. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems (such as mobile phones), among others. SQLite is a popular public domain choice as embedded database software for local/client storage in application software such as web browsers. Rather, it is embedded into the end program. In contrast to many other database management systems, SQLite is not a client-server database engine. SQLite is a relational database management system contained in a C programming library. Another handy thing is that SQLite stores data in a single file which can be stored anywhere. Because it is serverless, lightweight, opensource and supports most SQL code (its license is "Public Domain"). SQLite is probably the most suitable choice. MySQL is very known but a little bit "heavy" for use on simple Raspberry based projects (besides it is own by Oracle!). There are many options in the market and probably the 2 most used with Raspberry Pi and sensors are MySQL and SQLite. OK, the general idea will be collect data from a sensor and store them in a database.īut what database "engine" should be used?
0 Comments
Leave a Reply. |