Cleaning up the database

So I guess its my DBA background from many years ago - its certainly not the programmer in me - im just a hacker... went from DBA to Enterprise Architect to the dark side of management.... LOL

I am not sure how long the database can store all the values its reading so often, maybe a year or two, maybe more - but a 16gb card with all the other software - does not leave a lot !

So on my list of things to do - was to write a Database Cleanup routine.... I have something basic - which appears to work... Just another hack, I place it here in case its of interest to others, and maybe for any comment if you can see something wrong in what I have done... Been running for a couple of days now - seems to work.... Time will tell.

So there are 4 tables in the database - and this is only useful if you have the database logging on - in your conflocal.py file with enable_MySQL_Logging = True

For me I am not using Solar at present - so I have no interest in keeping any data related to power, Yes I am interested in looking at maybe the last week of system type events, but I really only care about a months worth of weather recording data.... So I decided to make things variable....

This is what I did
In conflocal.py I added 4 new variables - see them after the comment about Retention;

#MySQL Logging and Password Information


enable_MySQL_Logging = True
MySQL_Password = "dapwd"


#Retention Periods of keeping data in MySQL (nbr of Days to keep)
DB_PowerSystem_Ret = 1
DB_Sunlight_Ret = 3
DB_Systemlog_Ret = 5
DB_WeatherData_Ret = 32


And in SkyWeather.py I needed to add the routine to delete things from the database - I placed it just before "def patTheDog():" - Again does not matter that much....


def CleanDB():

    print "About to do a database cleanup"
    print("trying database")
    con = mdb.connect('localhost', 'root', config.MySQL_Password, 'SkyWeather');
    cur = con.cursor()
    print("Cleaning up - PowerSystem Table")
    query = "DELETE FROM `PowerSystem` WHERE `TimeStamp` < NOW() - INTERVAL " + str(config.DB_PowerSystem_Ret) + " DAY;"
    print query
   cur.execute(query)
    con.commit()

    # Commence cleanup on Sunlight Table
    print("Cleaning up - Sunlight Table")
    query = "DELETE FROM `Sunlight` WHERE `TimeStamp` < NOW() - INTERVAL " + str(config.DB_Sunlight_Ret) + " DAY;"
    print query
    cur.execute(query)
    con.commit()
   
    # Commence cleanup on SystemLog Table
    print("Cleaning up - SystemLog Table")
    query = "DELETE FROM `systemlog` WHERE `TimeStamp` < NOW() - INTERVAL " + str(config.DB_Systemlog_Ret) + " DAY;"
    print query
    cur.execute(query)
    con.commit()

    # Commence cleanup on WeatherData Table
    print("Cleaning up - WeatherData Table")
    query = "DELETE FROM `WeatherData` WHERE `TimeStamp` < NOW() - INTERVAL " + str(config.DB_WeatherData_Ret) + " DAY;"
    print query
    cur.execute(query)
    con.commit()

    cur.close()
    con.close()

    del cur
    del con


And also needed to add this to the scheduler to run each day - for me I made it 23:45 each day - but there is no rules here... See the CleanDB line was added so it only executes if the logging to MYSQL is being used...


if (config.enable_MySQL_Logging == True):
    scheduler.add_job(writeWeatherRecord, 'interval', seconds=5*60)
    scheduler.add_job(writePowerRecord, 'interval', seconds=5*60)
    scheduler.add_job(CleanDB, 'cron', day_of_week='mon-sun', hour=23, minute=45


I think that was all of it.... Your welcome to use it - make backup of your files first, just in case I forgot something else that I needed to change....

Hope it was of interest to some.... I will keep sharing my mods / hacks if your interested....