maintain_water_ch.py

# about coordinate system:
# https://www.gps-coordinates.net/

# how to use nominatim examples:
# https://www.programcreek.com/python/example/108180/geopy.geocoders.Nominatim

# default input is in format: 47.373459, 8.553239

# questions:
# - coordinates format (GPS coordinates / Lat Long) - example 47.373459, 8.553239
# - check for duplicates (how close are water sources/coordinates)
# - in coordinates_list add 26 canton_lists

# to do:
# is koordinaciu istraukia vietove itt
# - jungiasi per API openstreetmap?
# - paspaudus details: atsidaro zemelapis

# pateikia oro prognozes pagal lokoalizacija:
# - oro temperatura
# - krituliu (lietaus, sniego) tikimybe

import sqlite3
import geopy
from datetime import datetime

# geopy, nominatim will be used for pc/laptop
# leaflet will be used for mobile devices


class DataBaseConnection(object):

    def __init__(self):
        self.db = sqlite3.connect("water_ch.sqlite")
        self.cursor = self.db.cursor()

    def db_close(self):
        self.cursor.close()
        self.db.commit()
        self.db.close()


class ManageWaterSource(object):

    def __init__(self, lat_ORIGINAl, long_ORIGINAL, canton):
        self.lat_ORIGINAl = lat_ORIGINAl
        self.long_ORIGINAL = long_ORIGINAL
        self.canton = canton
        self.db_connection = DataBaseConnection()

    def insert_values(self, lat_ORIGINAl, long_ORIGINAL):
        add_date = str(datetime.now())
        cursor = self.db_connection.cursor

        # cursor.execute("INSERT INTO water_ch (lat_ORIGINAl, long_ORIGINAL, add_date, canton) VALUES ('test', 'test', strftime('%Y-%m-%d %H:%M:%S', 'now'), 'zh')")

        query_insert = "INSERT INTO water_ch (lat_ORIGINAL, long_ORIGINAL, add_date, canton) " \
                       "VALUES ({}, {}, strftime('%Y-%m-%d %H:%M:%S', 'now'), '')"\
            .format(lat_ORIGINAl, long_ORIGINAL)

        if self.is_unique_values(lat_ORIGINAl, long_ORIGINAL):
            print("Adding records lat: {} and long: {}".format(lat_ORIGINAl, long_ORIGINAL))
            cursor.execute(query_insert)
        else:
            print("Records {} and {} exist in database already.".format(lat_ORIGINAl, long_ORIGINAL))

        self.db_connection.db_close()

    def is_unique_values(self, lat_ORIGINAl, long_ORIGINAL):
        query_check_unique = "SELECT COUNT(*) FROM water_ch " \
                             "WHERE water_ch.lat_ORIGINAL = {} AND water_ch.long_ORIGINAL = {}".format(lat_ORIGINAl, long_ORIGINAL)

        cursor = self.db_connection.cursor
        cursor.execute(query_check_unique)
        nrows = cursor.fetchall()[0][0]

        if nrows >= 1:
            return False
        else:
            return True


def open_map(db_record):
    db = sqlite3.connect("water_ch.sqlite")
    cursor = db.cursor()

    return cursor.fetchall()[db_record]

db = sqlite3.connect("water_ch.sqlite")
cursor = db.cursor()
cursor.execute("SELECT * FROM water_ch")
xx = cursor.fetchall()
print(len(xx))
print(xx[1])
cursor.close()
db.close()

# check if lat, long are already in database
# zh = ManageWaterSource(47.373459, 8.553239, '')
# zh.insert_values(47.373459, 8.553239)

# insert coordinates into database
# zh = ManageWaterSource(47.382491, 8.567698, '')
# zh.insert_values(47.382491, 8.567698)

# PickFresh coordinates
# zh = ManageWaterSource(47.37568107274017, 8.559687401179092, '')
# zh.insert_values(47.37568107274017, 8.559687401179092)

# print database content
db = sqlite3.connect("water_ch.sqlite")
cursor = db.cursor()
cursor.execute("SELECT * FROM water_ch")
field_names = [i[0] for i in cursor.description]
print(field_names)
print(*cursor.fetchall(), sep = "\n")
# for records in db.execute("SELECT * FROM water_ch"):
#     print(records)
cursor.close()
db.close()