· 10 years ago · May 23, 2016, 07:45 PM
1
2#!/usr/bin/python
3import psycopg2
4import sys
5import os
6import xml.etree.ElementTree as ET
7import csv
8from time import gmtime, strftime
9import time
10import re
11import subprocess
12
13files = 10
14test = False
15# os.environ[]
16
17CREATE_DIN_STR = """
18 CREATE TABLE IF NOT EXISTS din (
19 id SERIAL PRIMARY KEY, id_brany INTEGER, name VARCHAR(50),
20 sts INTEGER, val INTEGER, pic INTEGER,
21 cmo INTEGER, cnt INTEGER, hour INTEGER, display_time VARCHAR(20), time TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
22 )
23 """
24
25
26CREATE_DOUT_STR = """
27 CREATE TABLE IF NOT EXISTS dout (
28 id SERIAL PRIMARY KEY, id_brany INTEGER, name VARCHAR(50),
29 sts INTEGER, val INTEGER, pic INTEGER,
30 mde INTEGER, pars VARCHAR(50) , time TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
31 )
32 """
33CREATE_TEMP_STR = """
34 CREATE TABLE IF NOT EXISTS temp (
35 id SERIAL PRIMARY KEY, id_brany INTEGER,
36 sts INTEGER, val INTEGER, tenb INTEGER,
37 th FLOAT, tl FLOAT , time TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT NOW()
38 )
39 """
40
41
42DROP_DIN_TABLE = """
43 DROP TABLE din
44 """
45DROP_DOUT_TABLE = """
46 DROP TABLE dout
47 """
48DROP_TEMP_TABLE = """
49 DROP TABLE temp
50 """
51
52INSERT_DOUT_STR = """
53 INSERT INTO "dout" (id_brany, name, sts, val, pic, mde, pars)
54 VALUES (%s,%s,%s,%s,%s,%s,%s)
55 """
56
57
58INSERT_DIN_STR = """
59 INSERT INTO "din" (id_brany, name, sts, val, pic, cmo, cnt, hour, display_time)
60 VALUES (%s,%s,%s,%s,%s,%s,%s, %s, %s)
61 """
62
63INSERT_TEMP_STR = """
64 INSERT INTO "temp" (id_brany, sts, val, tenb, th, tl)
65 VALUES (%s,%s,%s,%s,%s,%s)
66 """
67
68
69xml_file = "./example.xml"
70
71try:
72 conn_string = "host='127.0.0.1' port='5432' dbname='papouch_data' user='monitoring_vyroby' password='|Mq@Pv|E'"
73 print "Connecting to database\n ->%s" % (conn_string)
74 conn = psycopg2.connect(conn_string)
75 cursor = conn.cursor()
76 print "Connected!\n"
77
78except psycopg2.DatabaseError, e:
79 print 'Error %s' % e
80 sys.exit(1)
81
82
83
84
85def initialize_db():
86 try:
87 cur = conn.cursor()
88 cur.execute(CREATE_DIN_STR)
89 cur.execute(CREATE_DOUT_STR)
90 cur.execute(CREATE_TEMP_STR)
91 conn.commit()
92
93 except psycopg2.DatabaseError, e:
94 print "Error %s" %e
95 sys.exit(1)
96
97
98def p_initialize_db():
99 try:
100 cur = conn.cursor()
101 cur.execute(CREATE_DIN_STR)
102
103 conn.commit()
104
105 except psycopg2.DatabaseError, e:
106 print "Error %s" %e
107 sys.exit(1)
108
109def drop_tables():
110 try:
111 cur = conn.cursor()
112 cur.execute(DROP_DIN_TABLE)
113 cur.execute(DROP_DOUT_TABLE)
114 cur.execute(DROP_TEMP_TABLE)
115 conn.commit()
116
117 except psycopg2.DatabaseError, e:
118 print "Error %s" %e
119 sys.exit(1)
120
121def p_drop_tables():
122 try:
123 cur = conn.cursor()
124 cur.execute(DROP_DIN_TABLE)
125
126 conn.commit()
127
128 except psycopg2.DatabaseError, e:
129 print "Error %s" %e
130 sys.exit(1)
131
132
133# example: din id val
134def get_xml_item(xml_t1, ident, xml_t2):
135 # print xml_t1
136 # print ident
137 # print xml_t2
138 if os.path.isfile(xml_file) and os.access(xml_file, os.R_OK):
139
140 try:
141 tree = ET.parse(xml_file)
142 root = tree.getroot()
143 except ET.parseError as e:
144 print "Error %s" %e
145 sys.exit(1)
146
147 for i in root.iter(xml_t1):
148 # print i.get("id")
149 if i.get("id") == ident:
150 # print i.get(xml_t2)
151 value = i.get(xml_t2)
152 return value
153 break
154
155def p_get_xml_item(xml_t1, ident, xml_t2):
156 # print xml_t1
157 # print ident
158 # print xml_t2
159 if os.path.isfile(xml_file) and os.access(xml_file, os.R_OK):
160 # print xml_file
161 try:
162 tree = ET.parse(xml_file)
163 root = tree.getroot()
164 # print root
165 except ET.parseError as e:
166 print "Error %s" %e
167 sys.exit(1)
168 # print root
169 for i in root.iter(xml_t1):
170 # print i.get("id")
171 if i.get("id") == ident:
172 # print i.get(xml_t2)
173 value = i.get(xml_t2)
174 return value
175 break
176
177def cnt_id(xml_t1):
178 # print "count"
179 # print xml_file
180 if os.path.isfile(xml_file) and os.access(xml_file, os.R_OK):
181 # print "idemna to "
182 try:
183 tree = ET.parse(xml_file)
184 root = tree.getroot()
185 # print root
186 except ET.parseError as e:
187 print "Error %s" %e
188 sys.exit(1)
189 count = 0
190 # print "pred forom "
191 # print xml_t1
192 # print root.iter("din")
193 for i in root.iter(xml_t1):
194 # print i
195 count += 1
196 return count
197
198def insert_din():
199 # count = cnt_id("din")
200 for i in xrange(1,9):
201 name = get_xml_item("din", str(i), "name")
202 # print name
203 sts = get_xml_item("din", str(i), "sts")
204 # print sts
205 val = get_xml_item("din", str(i), "val")
206 # print val
207 pic = get_xml_item("din", str(i), "pic")
208 # print pic
209 cmo = get_xml_item("din", str(i), "cmo")
210 # print cmo
211 cnt = get_xml_item("din", str(i), "cnt")
212 # print cnt
213 hour = time.strftime("%H")
214 display_time = time.strftime("%H:%M:%S")
215 try:
216 cur = conn.cursor()
217 cur.execute(INSERT_DIN_STR,(i, name, sts, val, pic, cmo, cnt, hour, display_time))
218
219 except psycopg2.DatabaseError, e:
220 print "Error %s" %e
221 sys.exit(1)
222 cur.connection.commit()
223
224def p_insert_din():
225 # count = cnt_id("din")
226 # for i in xrange(1,count+1):
227 i = "1"
228 name = ""
229 # print name
230 sts = 0
231 # print sts
232 val = 0
233 # print val
234 pic = 0
235 # print pic
236 cmo = 0
237 # print cmo
238 # print "idem ziskat count"
239 cnt = p_get_xml_item("din", "1", "cnt")
240 # print "zikasny count"
241 print cnt
242 try:
243 cur = conn.cursor()
244 cur.execute(INSERT_DIN_STR,(i, name, sts, val, pic, cmo, cnt))
245
246 except psycopg2.DatabaseError, e:
247 print "Error %s" %e
248 sys.exit(1)
249 cur.connection.commit()
250
251def insert_dout():
252 count = cnt_id("dout")
253 for i in xrange(1,count+1):
254 name = get_xml_item("dout", str(i), "name")
255 # print name
256 sts = get_xml_item("dout", str(i), "sts")
257 # print sts
258 val = get_xml_item("dout", str(i), "val")
259 # print val
260 pic = get_xml_item("dout", str(i), "pic")
261 # print pic
262 mde = get_xml_item("dout", str(i), "mde")
263 # print cmo
264 pars = get_xml_item("dout", str(i), "pars")
265 # print cnt
266 try:
267 cur = conn.cursor()
268 cur.execute(INSERT_DOUT_STR,(i, name, sts, val, pic, mde, pars))
269
270 except psycopg2.DatabaseError, e:
271 print "Error %s" %e
272 sys.exit(1)
273 cur.connection.commit()
274
275def insert_temp():
276 count = cnt_id("temp")
277 for i in xrange(1,count+1):
278 sts = get_xml_item("temp", str(i), "sts")
279 # print sts
280 val = get_xml_item("temp", str(i), "val")
281 if val == "":
282 val = 0
283 # print val
284 tenb = get_xml_item("temp", str(i), "tenb")
285 # print tenb
286 th = get_xml_item("temp", str(i), "th")
287 # print th
288 tl = get_xml_item("temp", str(i), "tl")
289 # print tl
290 # print cnt
291 try:
292 cur = conn.cursor()
293 cur.execute(INSERT_TEMP_STR,(i, sts, val, tenb, th, tl))
294
295 except psycopg2.DatabaseError, e:
296 print "Error %s" %e
297 sys.exit(1)
298 cur.connection.commit()
299
300def insert_tag(xml_t):
301 if xml_t == "din":
302 insert_din()
303 elif xml_t == "dout":
304 insert_dout()
305 elif xml_t == "temp":
306 insert_temp()
307 else:
308 print "ine tagy"
309
310def insert_all():
311 test = True
312 insert_din()
313 # insert_dout()
314 # insert_temp()
315
316def export_table_old(table):
317 select = "SELECT * FROM " + table
318 file_name = table + ".xls"
319 try:
320 cur = conn.cursor()
321 outputquery = "COPY ({0}) TO STDOUT WITH CSV HEADER".format(select)
322 with open(file_name, 'w') as f:
323 cur.copy_expert(outputquery, f)
324
325
326 except psycopg2.DatabaseError, e:
327 print "Error %s" %e
328 sys.exit(1)
329
330def export_table(table):
331 select = "SELECT * FROM " + table
332 file_name = table + ".xls"
333 try:
334 cur = conn.cursor()
335 cur.execute(select)
336 rows = cur.fetchall()
337 # print rows
338 # print "\nRows: \n"
339 # for row in rows:
340 # for i in row:
341 # print " ", i
342 with open(filena, 'w') as f:
343 writer = csv.writer(f, delimiter=',')
344 for row in rows:
345 writer.writerow(row)
346
347 except psycopg2.DatabaseError, e:
348 print "Error %s" %e
349 sys.exit(1)
350
351def insert_xml(file_to_insert):
352 # test = False # pouzijem realne xml a nie moje testoavcie
353 # print xml_file
354 insert_din()
355 insert_dout()
356 insert_temp()
357
358def p_insert_xml(file_to_insert):
359 p_insert_din()
360
361
362def backup_xml(file_to_bkp):
363 file_to_move = file_to_bkp
364 new_file = re.split("\/",file_to_bkp)[1]
365 cmd = "mv " + file_to_bkp + " xml_bkp/" + new_file
366 os.system(cmd)
367
368# stiahnem xml, vlozim ho do db, a zalohujem do bkp zlozky
369def xml_test():
370 for i in xrange(1,101):
371 print("%04d" % i)
372 wget_cmd = "wget -O - http://37.9.171.216/scripts/%04d" % i + ".xml > " + xml_file
373 print wget_cmd
374def get_xml():
375 global xml_file
376 # for i in xrange(1,101):
377 time = strftime("%Y-%m-%d %H:%M:%S", gmtime()).replace(" ","_")
378 xml_file = "xml/xml" + "-" + time + ".xml"
379 # wget_cmd = "wget -O - http://37.9.171.216/xml/%04d" % i + ".xml > " + xml_file
380 wget_cmd = "wget -O - http://37.9.171.216/xml2/xml.xml > " + xml_file
381 os.system(wget_cmd)
382 insert_xml(xml_file)
383 backup_xml(xml_file)
384def p_get_xml():
385 global xml_file
386 time = strftime("%Y-%m-%d %H:%M:%S", gmtime()).replace(" ","_")
387 xml_file = "/home/nbw213/xml2db/xml/xml" + "-" + time
388 xml_file2 = xml_file + "dirt.xml"
389 xml_file = xml_file + ".xml"
390 # print xml_file2
391 wget_cmd = "wget -O - http://192.168.1.254/fresh.xml > " + xml_file2
392 os.system(wget_cmd)
393 replace_cmd = "cat " + xml_file2 + " | sed 's/\ xmlns=\"http:\/\/www\.papouch\.com\/xml\/quido\/act\"//g' "
394 os.system(replace_cmd + "> " + xml_file)
395 # print xml_file
396 # cmd = ['sed \'s/\ xmlns="http:\/\/www\.papouch\.com\/xml\/quido\/act"//g\'', xml_file2 ]
397 # with open('output.txt', 'w') as out:
398 # return_code = subprocess.call(cmd, stdout=out)
399
400 insert_xml(xml_file)
401 backup_xml(xml_file)
402
403def usage():
404 print "just use it!"
405
406
407
408
409def main():
410 initialize_db()
411 while True:
412 p_get_xml()
413 time.sleep(20)
414
415
416
417
418
419
420if __name__ == "__main__":
421 main()
422p