Buy Me a Coffee

Monday, 23 April 2018

Managing the size of an SQLite database

This post is part of the book Raspberry Pi Computing: Analog Measurement which can be downloaded from Leanpub for free (or donate if you wish).

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.

No comments:

Post a Comment