#! /usr/bin/env python3.5

# import standard libraries
import os
import csv
from datetime import datetime
# import additional libraries
import mysql.connector # import library mysql.connector
from mysql.connector import errorcode # import library errorcod from mysql.conn

# db configuration
DB_CONFIG = dict(
    user='root',
    password='t00r0DGn1',
    host='localhost',
    database='odgn'
)
# range id of sensors(1 - 34)
SENSOR_IDS = range(23, 45)

try:
    cnx = mysql.connector.connect(**DB_CONFIG) # open connection to the MySQL server and store the conn object in the variable cnx

except mysql.connector.Error as err:
    if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        raise Exception("Something is wrong with your user name or password")

    elif err.errno == errorcode.ER_BAD_DB_ERROR:
        raise Exception("Database does not exist")

    else:
        raise Exception(err)

cursor = cnx.cursor() #create a new cursor

for sensor_id in SENSOR_IDS:

    filename = 'file_sensor_%s.csv' % sensor_id
    # быстрый способ чтения последней строки файла в байтовой строке, как работает - хз, взял с форума
    with open(filename, 'rb') as f:
        f.seek(-min(os.path.getsize('file_sensor_23.csv'),200),2)
        txt = ((f.read().splitlines()[-1]).decode('utf-8')).split(';') # get a string and split it into an array
        dt = datetime.strptime(txt[0], "%d.%m.%Y %H:%M") # formating date to MySQL datetime format

    query = "SELECT date_format(date, '%d.%m.%Y %H:%i') as date, sensor, id, h FROM data_hydrolevel WHERE sensor = '%s' AND date_format(date, '%Y-%m-%d %H:%i') > %s;"
    cursor.execute(query, (sensor_id, dt))

    with open(filename, 'a') as f:
        writer = csv.writer(f, delimiter=';')
        writer.writerows(cursor)

cursor.close()

cnx.close()