Source code for meterReadingBackup_02c

import mysql.connector # for connectivity to MySQL database
import os              # for the 'cls' command to clear the screen
import datetime        # for date.today, timedelta and date.weekday functions
import webbrowser

class DatabaseManager:
    """
The Card class represents a single database accessed across a specific mysql connector.  It is initialised by passing a set of credentials
for opening a connection to a MySQL server; these credentials are passed as a dictionary which defines the values for the 'user', 'password',
'host', 'port' and 'database' variables to be used to access an existing database on a MySQL server and the value of the 'raise_on_warnings' flag
to be used over the connection to that database.
The class supports query and update functions for a specific (Readings) table in the database, and 'serverInfo' and 'close' functions for the
connection.
Work ongoing!
    """
    def __init__(self, credentials):
        self.conn = mysql.connector.connect(**credentials)
        self.cur = self.conn.cursor()

    def newTable(self, tableName, tableColumns):
        genericCode = "CREATE TABLE {} ({})"
        sqlCode = genericCode.format(tableName, tableColumns)
        self.cur.execute(sqlCode)
            
    def query(self, sqlCode):
        self.cur.execute(sqlCode)
        return self.cur.fetchall()
    
    def update(self, sqlCode, data):
        self.cur.executemany(sqlCode, data)
        self.conn.commit()
    
    def serverInfo(self):
        return self.conn.get_server_info()
    
    def close(self):
        self.conn.close()
        
def cls():             # defining a function to clear the screen
    os.system('cls' if os.name=='nt' else 'clear')

def dateSuffix(date):
    if date == 1 or date == 21 or date == 31:
        string= "st "
    elif date == 2 or date == 22: 
        string = "nd "
    elif date == 3 or date == 23:
        string = "rd "
    else:
        string = "th "
    return string

def formatReading(result, fuelFlag):
    listOfReadings = []
    record = {}
    for x in result:
        if x[2] == fuelFlag:
            date = x[1]
            yearday = int(date.strftime("%j"))
            year = int(date.strftime("%Y"))
            month = (date.strftime("%b"))
            week = int(date.strftime("%W"))
            day = int(date.strftime("%d"))
            reading = x[3]
            record = (day, week, month, year, yearday, reading)
            listOfReadings.append(record)
    return listOfReadings

def printReadings(readings, fuelString):
    if fuelString == 'Electricity':
        extra = "========"
    elif fuelString == 'New electricity':
        extra = "============"
    elif fuelString == 'New gas':
        extra = "===="
    else:
        extra = ""
    print(" ")
    print(" ")
    print(fuelString + ' readings:')
    print('=============' + extra)
    print(" ")
    for x in readings:
        count = readings.index(x) + 1
        try:
            previous_entry = readings[count]
            string = dateSuffix(x[0])
            if x[3] > previous_entry[3]:
                if (previous_entry[3]%4) == 0:    # looking for leap years - fails with 2100, 2200, 2300, 2500, etc
                    days = int(366 + x[4] - previous_entry[4])
                else:
                    days = int(365 + x[4] - previous_entry[4])
            else:
                days = x[4] - previous_entry[4]
            units_used_per_week = int(7 * (x[5] - previous_entry[5])/days)
            day = str(x[3]) + " Week " + str(x[1]) + ":\t" + str(x[0]) + string + str(x[2] + "\t")
            print(day, "-\t", x[5], "\t", "Units used per week:\t", units_used_per_week)
        except IndexError:
            break

def main():
    cls()

    localhost = {
      'user': 'andrew',
      'password': 'password123',
      'host': 'localhost',
      'port': '3306',
      'database': 'testDatabase2020006',
      'raise_on_warnings': True
    }

    zeropi1 = {
      'user': 'root',
      'password': 'password123',
      'host': 'zeropi',
      'port': '3306',
      'database': 'Meter_Readings',
      'raise_on_warnings': True
    }

    zeropi2 = {
      'user': 'andrew',
      'password': 'password123',
      'host': 'zeropi',
      'port': '3306',
      'database': 'meterReadings',
      'raise_on_warnings': True
    }

    # to read the contents of the existing Readings database on PiZero 

    zpR1 = DatabaseManager(zeropi1)
    sqlQuery = "SELECT * FROM Readings WHERE idReadings > 369"
    result = zpR1.query(sqlQuery)
    print(result)
    zpR1.close()

    print("")
    print("")
    nextDate = result[0][1]
    go = input("Do you want to enter readings for the next available reading date, which is: " + str(nextDate) + "? y/n  ")
    if go == 'n':
        print("In that case there is nothing further to do - bye!")   #   if user's input was 'n' then program stops here!  Need to consider error conditions, or maybe other choices
        exit()
    else:
        readingDate = nextDate
        print("The date recorded for these readings will be " + str(readingDate))
    zpR2 = DatabaseManager(zeropi2)
    gas = 9591.08
    electric = 39355.0
    index1 = 356
    index2 = 357
    records = [
        (index1, readingDate, 0, electric),
        (index2, readingDate, 1, gas)
        ]
    sqlQuery = "INSERT INTO Readings (readingID, readingDate, readingType, readingValue) VALUES (%s, %s, %s, %s)"
    zpR2.update(sqlQuery, records)
    elecReadings = []
    gasReadings = []
    sqlQuery = "SELECT * FROM Readings ORDER BY readingID DESC LIMIT 8"
    result = zpR2.query(sqlQuery)
    elecReadings = formatReading(result, 0)
    gasReadings = formatReading(result, 1)
    printReadings(gasReadings, "New gas")
    printReadings(elecReadings, "New electricity")
    zpR2.close()
    webbrowser.open("https://octopus.energy/dashboard/accounts/A-A4570FF5/properties/1327603/meter-readings/meter/")

    
if __name__ == "__main__":
    main()