AEROS/insertNewHourlyObservationsData.py at main · UdayLab/AEROS · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
import csv
import sys
from os import listdir
from os.path import isfile, join
import psycopg2
from alive_progress import alive_bar


class insertNewHourlyObservationsData:
    def insert(inputDataFolder):
        files = [f for f in listdir(inputDataFolder) if isfile(join(inputDataFolder, f))]
        with alive_bar(len(files)) as bar:
            for file in files:
                bar()
                conn = None
                try:
                    conn = psycopg2.connect(database="", user="", password="", host="", port=5432)

                    # create a cursor
                    cur = conn.cursor()

                    # reading csv file
                    csv_file = open(inputDataFolder + '/' + file, encoding="cp932", errors="", newline="")

                    f = csv.reader(csv_file, delimiter=",", doublequote=True, lineterminator="\r\n", quotechar='"',
                                   skipinitialspace=True)

                    header = next(f)
                    for row in f:
                        date = ''
                        query = ''

                        for i in range(len(row)):
                            # filling missing values
                            # Handling empty dates
                            if i == 1 or i == 2:
                                if row[i] == '':
                                    date = 'NULL'
                            else:
                                if row[i] == '' or row[i] == '-' or '#' in row[i]:
                                    row[i] = 'NULL'

                        if date == '':
                            # writing query
                            query = 'insert into hourly_observations values(\'' + row[0] + '\',\'' + row[1] + ' ' + row[
                                2] + ':00:00\'' + ',' + \
                                    row[3] + ',' + row[4] + ',' + row[5] + ',' \
                                    + row[6] + ',' + row[7] + ',' + row[8] + ',' + row[9] + ',' + row[10] + ',' + \
                                    row[
                                        11] + ',' + \
                                    row[12] + ',' + row[13] + ',' + row[14] + ',-1' + ',' + row[16] + ',' + row[
                                        17] + ',' + row[
                                        18] + ")"
                        else:
                            # writing query
                            query = 'insert into hourly_observations values(' + row[0] + ',' + date + ',' + \
                                    row[3] + ',' + row[4] + ',' + row[5] + ',' \
                                    + row[6] + ',' + row[7] + ',' + row[8] + ',' + row[9] + ',' + row[10] + ',' + row[
                                        11] + ',' + \
                                    row[12] + ',' + row[13] + ',' + row[14] + ',-1' + ',' + row[16] + ',' + row[
                                        17] + ',' + row[
                                        18] + ")"
                        # executing the query
                        cur.execute(query)
                    conn.commit()
                    # print('Success')

                    # close the communication with the PostgreSQL
                    cur.close()


                except (Exception, psycopg2.DatabaseError) as error:
                    print(error, inputDataFolder + '/' + file)

                finally:
                    if conn is not None:
                        conn.close()
                        # print('Database connection closed.')


if __name__ == '__main__':
    """
        Start the main() Method
    """
    if len(sys.argv) < 2:
        print("Error : Incorrect number of input parameters")
        print("Format: python3  stationInfo.py  folderName")
    else:
        insertNewHourlyObservationsData.insert(sys.argv[1])