Source code for meterReadingBackup_01

#!/usr/bin/env python
"""Simple backup routine that will connect to zeropi, read the 'Readings' table in the 'meterReadings' database, create a new database on b4Pio and copy the meterReadings data into that database.  This is a self contained program that makes no use of any of the DatabaseManager.py classes."""
import mysql.connector # for connectivity to MySQL database
from mysql.connector import Error
import datetime        # for date.today, timedelta and date.weekday functions
import helper_functions as h


def initialiseDatabase(name="testDatabase"):
    try:
        connection = mysql.connector.connect(
            host = "localhost",
            port = "3306",
            user = "andrew",
            passwd = "password123",
            )
        if connection.is_connected():
            my_cursor = connection.cursor()
            my_cursor.execute("DROP DATABASE IF EXISTS " + name)
            my_cursor.execute("CREATE DATABASE " + name)
            my_cursor.execute("SHOW DATABASES")
            db_Info = connection.get_server_info()
            print(" ")
            print("Connected to b4Pio MySQL Server, version", db_Info)
            print(" ")          
            print("List of Databases on b4Pio")
            print("==========================")
            for db in my_cursor:
                print(db[0])
            print(" ")
            my_cursor.execute("USE " + name)
            mySql_Create_Table_Query = """CREATE TABLE Readings ( 
                                readingId int(11) NOT NULL,
                                readingDate Date NOT NULL,
                                readingType smallint NOT NULL,
                                readingValue float NOT NULL,
                                PRIMARY KEY (readingId)) """
            my_cursor.execute(mySql_Create_Table_Query)
            print("Readings table created successfully in " + name + " database.")
    except mysql.connector.Error as error:
        print("Failed to create table in MySQL: {}".format(error))
    finally:
        if (connection.is_connected()):
            my_cursor.close()
            connection.close()
            print("b4Pio MySQL connection is closed")

def updateReadings(readings, dataBaseName):
    try:
        connection = mysql.connector.connect(host='localhost',
                                             database=dataBaseName,
                                             user='andrew',
                                             password='password123')
        cursor = connection.cursor()
        mySql_insert_query = """INSERT INTO Readings (readingId, readingDate, readingType, readingValue) 
                               VALUES (%s, %s, %s, %s) """
        readings_to_insert = []
        for reading in readings:
            readings_to_insert.append(reading)
        cursor.executemany(mySql_insert_query, readings_to_insert)
        connection.commit()
        print(cursor.rowcount, "Records inserted successfully into Readings table in " + dataBaseName + " database on b4Pio.")
    except mysql.connector.Error as error:
        print("Failed to insert record into MySQL table {}".format(error))
    finally:
        if (connection.is_connected()):
            cursor.close()
            connection.close()
            print("b4Pio MySQL connection is closed")

def readMeterReadings():
    try:
        connection = mysql.connector.connect(
            host = "zeropi",
            port = "3306",
            user = "andrew",
            passwd = "password123",
            database = "meterReadings"
            )
        if connection.is_connected():
            db_Info = connection.get_server_info()
            print(" ")
            print("Connected to zeroPi MySQL Server, version", db_Info)
            cursor = connection.cursor()
            cursor.execute("select database();")
            record = cursor.fetchone()
            print("You're connected to database: ", record)
            cursor.execute("SELECT * FROM Readings")
            return cursor.fetchall()
    except Error as e:
        print("Error while connecting to MySQL", e)
    finally:
        if (connection.is_connected()):
            cursor.close()
            connection.close()
            print("zeroPi MySQL connection is closed")
            print(" ")
            print(" ")

def main():
    # now, to clear the screen
    h.cls()
    x = datetime.datetime.now()
    y = str(x.strftime("%Y"))
    d = str(x.strftime("%j"))
    tag = y + d
    dataBaseName = "meterReadingsBackup" + tag

    initialiseDatabase(dataBaseName)
    readings = readMeterReadings()
    updateReadings(readings, dataBaseName)      
    
if __name__ == "__main__":
    main()