-
Notifications
You must be signed in to change notification settings - Fork 0
/
insertOldHourlyObservationsData.py
138 lines (108 loc) · 5.57 KB
/
insertOldHourlyObservationsData.py
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
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
# This Program takes input as folder containing various csv files in it. Reads all csv files and handles missing dates and inserts the data into a PostgreSQL database.
#
# **Importing this japanAirAnalytics data files in a Python program**
# ---------------------------------------------------------------------
#
#
# from japanAirAnalytics.store import oldHourlyDataFormat as db
#
# obj = db()
#
# obj.insertData(inputDataFolder)
import csv
import os
import sys
from os import listdir
from os.path import isfile, join
import psycopg2
from alive_progress import alive_bar
class insertOldHourlyObservationsData:
"""
:Description: This program reads CSV files from the specified folder, handles missing dates, and inserts the data into the hourly_observations table in a PostgreSQL database.
:param inputDataFolder: str :
The path to the folder containing CSV files. Example: oldHourlyDataFormat.insert('/path/to/inputDataFolder')
:Attributes:
None
:Methods:
insert(inputDataFolder): Reads CSV files, handles missing dates, and inserts data into the hourly_observations table.
**Methods to execute japanAirAnalytics on terminal**
-------------------------------------------------------
Format:
>>>python3 insertOldHourlyObservationsData.py <inputDataFolder>
Example:
>>>python3 insertOldHourlyObservationsData.py inputDataFolder
.. note:: Specify the name of the database in database.ini file
**Importing this japanAirAnalytics data files into a python program**
------------------------------------------------------------------------
.. code-block:: python
from japanAirAnalytics.store import oldHourlyDataFormat as db
obj = db()
obj.insertData(inputDataFolder)
"""
def insert(inputDataFolder):
subFolders = [f.path for f in os.scandir(inputDataFolder) if f.is_dir() ]
conn = None
inputFileName = ""
for folder in subFolders:
try:
conn = psycopg2.connect(database="", user="", password="", host="", port=5432)
cur = conn.cursor()
print('Reading the folder: ' + str(folder))
files = [f for f in listdir(folder) if isfile(join(folder, f))]
with alive_bar(len(files)) as bar:
for file in files:
bar()
inputFileName = folder + '/' + file
csv_file = open(inputFileName, 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
# print(row[i])
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()
cur.close()
except (Exception, psycopg2.DatabaseError) as error:
print(error, inputFileName)
finally:
if conn is not None:
conn.close()
if __name__ == '__main__':
"""
Start the main() Method
"""
if len(sys.argv) < 2:
print("Error : Incorrect number of input parameters")
print("Format: python3 stationInfo.py inputFolderContainingData")
else:
insertOldHourlyObservationsData.insert(sys.argv[1])