Managing database size
While it’s a great idea to save our local data into a database, we stand the risk of gradually letting that database fill up until it exceeds the capacity of our storage.
What we’re looking for is a script that will run on a repeating schedule and remove old records. Sound familiar? That’s a very similar process to what we are doing when we record our data. A python script that is executed regularly by
cron.
Here’s how we can do it.
The following python script (which we can name
db-manage.py) opens our database, deletes any records older than a year, cleans up and exits.#!/usr/bin/python
#encoding:utf-8
#Import SQLite library
import sqlite3
# Opens a database file called measurements
conn = sqlite3.connect('/home/pi/measurements', isolation_level=None)
db = conn.cursor()
# Delete any records that are older than 1 year
db.execute('DELETE FROM light WHERE dtg<DATETIME("now","localtime", "-1 years\
")')
# VACUUM the database to remove any unnecessary data
db.execute('VACUUM')
# Commit the changes to the database and close the connection
conn.commit()
conn.close
The file is available as
db-manage.py and can be found in the code sample extras that can be downloaded with this book.
It’s a pretty simple script and we can schedule its operation by editing the
crontab file like so;
We want to add in an entry at the end of the file that looks like the following;
1 0 */1 * * /usr/bin/python /home/pi/db-manage.py
This instructs the computer that at 1 minute past the hour at midnight (hence the
0) on the 1st day of every month we run the command /usr/bin/python /home/pi/db-manage.py (which, if we were at the command line in the pi home directory, we would run as python db-manage.py, but since we can’t guarantee where we will be when running the script, we are supplying the full path to the pythoncommand and the db-manage.py script.
Save the file and every month our program will run on its designated schedule and will make sure to delete any records older than a year.
For more info on using the Raspberry Pi or for a range of other free to download books (there are no catches) check out the list here.
Ncie
ReplyDelete