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 opens our database, deletes any records older than a year, cleans up and exits.


#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

# Commit the changes to the database and close the connection

The file is available as 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/

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/ (which, if we were at the command line in the pi home directory, we would run as python, 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 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