db.py

import os
import sqlite3 as sqlite
from datetime import datetime
import mopiapi
from time import sleep, time
from datetime import datetime
from gpiozero import OutputDevice
import water
import temp
mp = mopiapi.mopiapi()

db_path='/home/pi/.config/balcony.db'

path = '/'.join(db_path.split('/')[:-1])
filename = db_path.split('/')[:-1]

if not os.path.exists(path):
    os.makedirs(path)

db = sqlite.connect(db_path, check_same_thread=False)
cursor = db.cursor()

def create_table():
    cursor.execute("""
        CREATE TABLE IF NOT EXISTS
            balcony (
                datetime TEXT,
                temperature1 REAL,
                temperature2 REAL,
		        batt REAL,
         		solar REAL,
                top_water TEXT,
                btm_water TEXT
                )
    """)
    db.commit()

def record_data():

    battv = mp.getVoltage(1) / 1000 # Get voltage from the 12 V battery - convert mV to V
    solarv = mp.getVoltage(2) / 1000 # Get voltage from 20w solar panel - convert mV to V

    d = get_timestamp()
    t1 = temp.temp1()
    t2 = temp.temp2()
    bv = battv
    sv = solarv
    tw = water.top_reservoir()
    bw = water.btm_reservoir()
    values = (d, t1, t2, bv, sv, tw, bw)

    cursor.execute("""
        INSERT INTO
            balcony
        VALUES
            (?, ?, ?, ?, ?, ?, ?)
    """, values)
    db.commit()

def get_timestamp():
    dt = datetime.now()
    dt_date = str(dt.date())
    dt_time = str(dt.time())
    timestamp = "%s %s" % (dt_date, dt_time[:8])
    return timestamp

def retreive_data():
    cursor.execute("""
        SELECT
            *
        FROM
            balcony
        ORDER BY
            datetime(datetime) DESC
        LIMIT
            0, 1
    """)
    result = cursor.fetchone()
    if not result:
        return None

    return result

def export_to_csv():
    file_path = '/home/pi/balcony/data/balcony.csv'

    cursor.execute("""
        SELECT
            *
        FROM
            balcony
    """)

    results = cursor.fetchall()

    with open(file_path, 'w') as f:
        f.write('Date/Time,Temperature 1, Temperature 2, Battery V, Solar V, Top Water, Btm Water\n')
        for result in results:
            for data in result:
                f.write('%s,' % data)
            f.write('\n')

def main():
    create_table()
    record_data()
    export_to_csv()

if __name__ == '__main__':
    main()