When the script is first run, it will create a new SQLite database file called mydatabase.db, which will add a table named recordeddata. The table is generated by createTable(), which runs the following SQLite command:
CREATE TABLE recordeddata
(
itm_date DEFAULT (date('now','localtime')),
itm_time DEFAULT (time('now','localtime')),
itm_name,
itm_value
)
The new table will contain the following data items:
|
Name |
Description |
|
itm_date |
Used to store the date of the data sample. When the data record is created, the current date (using date('now','localtime')) is applied as the default value. |
|
itm_time |
Used to store the time of the data sample. When the data record is created, the current time (using time('now','localtime')) is applied as the default value. |
|
itm_name |
Used to record the name of the sample. |
|
itm_value |
Used to keep the sampled value. |
We then use the same method to capture 10 data samples from the ADC as we did in the Logging and plotting data recipe previously (as shown in the captureSamples() function). However, this time, we will then add the captured data into our new SQLite database table, using the following SQL command (applied using cursor.execute(sqlquery)):
INSERT INTO recordeddata
(itm_name, itm_value) VALUES ('0:Light', 210)
The current date and time will be added by default to each record as it is created. We end up with a set of 40 records (4 records for every cycle of ADC samples captured), which are now stored in the SQLite database:
After the records have been created, we must remember to call cursor.commit(), which will save all the new records to the database.
The last part of the script calls displayAll(), which will use the following SQL command:
SELECT * FROM recordeddata
This will select all of the data records in the recordeddata table, and we use cursor.fetch() to provide the selected data as a list we can iterate through:
for x,column in enumerate(cursor.fetchall()):
print(FORMATLIST%(x,str(column[0]),str(column[1]),
str(column[2]),str(column[3])))
This allows us to print out the full contents of the database, displaying the captured data.
First, we ensure that if the database directory doesn't exist, we create it. Next, we try opening the database file; this process will automatically create a new database file if one doesn't already exist. If either of these initial steps fail (because they don't have read/write permissions, for example) we cannot continue, so we report that we cannot open the database and simply exit the script.
Next, we try to open the required table within the database and display it. If the database file is brand new, this operation will always fail, as it will be empty. However, if this occurs, we just catch the exception and create the table before continuing with the script to add our sampled data to the table and display it.
This allows the script to gracefully handle potential problems, take corrective action, and then continue smoothly. The next time the script is run, the database and table will already exist, so we won't need to create them a second time, and we can append the sample data to the table within the same database file.