import pymysql
import serial
import time

#Serial Constants
port = "COM3"
baud = 9600

#MySQL DB Constants
hostname = '104.196.69.254'
username = 'aquaponics'
password = 'duck'
database = 'Controller_1'

#Connects to MySQL DB Using Constants
def connectToSQL():
    print("Connecting to SQL...")
    try:
        c = pymysql.connect(host=hostname, user=username, password=password, db=database, charset='utf8',
                        cursorclass=pymysql.cursors.DictCursor)
        print("Connected to: " + str(c))
        return c
    except:
        print("Error connecting to MySQL.")
        return False

#Writes Temperature to MySQL DB Table 'temp"
def writeTemp(t, c):
    print("Attempting to write temp " + str(t) + " to " + str(c) + ".")
    try:
        with c.cursor() as cursor:
            sql = "INSERT INTO `temp` (`celsius`) VALUES (%s)"
            cursor.execute(sql, (t))
        c.commit()
        print("Temp wrote.")
    except:
        print("Failed to write to MySQL Database.")
        return False

#Connects to Arduino from Serial
def connectToArduino():
    print("Connecting to arduino...")
    try:
        connection = serial.Serial(port, baud)
        print("Connected to: " + str(connection))
        return connection
    except:
        print("Failed to connect to arduino.")
        return False

#Reads Arduino line from Serial
def readArduino(a):
    print("Reading arduino...")
    try:
        d = float(a.readline())
        print("Read data: " + str(d))
        return d
    except:
        print("Failed to read data from Arduino.")
        return False

print("Init.")
arduino = connectToArduino()
SQL = connectToSQL()
time.sleep(5)


print("Loop.")
while True:
    temp = readArduino(arduino)
    print(temp)
    print(arduino)
    print(SQL)
    if readArduino(arduino) == False:
        print("Failed to connect arduino, attempting reconnection in 5 seconds...")
        time.sleep(5)
        arduino = connectToArduino()
    elif writeTemp(temp, SQL) == False:
        print("Failed to connect to SQL, attempting reconnection in 5 seconds...")   #Possibly change this into a Try function. Right now this line also will write temp to MySQL and we dont want redundant data.
        time.sleep(5)
        SQL = connectToSQL()
    else:
        time.sleep(1)