PFR_DB/script.py at master · kappelmeyer/PFR_DB · 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
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
#!/usr/bin/python
# -*- encoding: iso-8859-15 -*-

import sys, csv, getopt, re
import MySQLdb
from datetime import datetime

def main(argv):
   inputfile = ''
   try:
      opts, args = getopt.getopt(argv,"hi:o:",["ifile="])
   except getopt.GetoptError:
      print 'test.py -i <inputfile>'
      sys.exit(2)
   for opt, arg in opts:
      if opt == '-h':
         print 'test.py -i <inputfile> '
         sys.exit()
      elif opt in ("-i", "--ifile"):
         inputfile = arg
   print 'Input file is "', inputfile

if __name__ == "__main__":
   main(sys.argv[1:])

def check_exist(reactor_check, date_check):
	mysql = MySQLdb.connect(mysql_opts['host'], mysql_opts['user'], mysql_opts['pass'], mysql_opts['db'])
	mysql.apilevel = "2.0"
	mysql.threadsafety = 2
	mysql.paramstyle = "format"
	cursor = mysql.cursor()
	cursor.execute("SELECT * FROM reactor_%d WHERE Date='%s'" % (int(reactor_check), date_check))
	#print("SELECT * FROM reactor_%d WHERE Date='%s'" % (int(reactor_check), date_check))
	row_count = cursor.rowcount
	if row_count == 0:
	    return 1
	else:
	    return 0

mysql_opts = {
	'host': "here host name",
	'user': "mySQL User name",
	'pass': "mySQL password",
	'db':   "pfr_new"
}

ifile  = open(sys.argv[2], "rb")
reader = csv.reader(ifile)
rownum = 0
for row in reader:
    if rownum == 0:
        header = row
    else:
        colnum = 0
        for col in row:
	    if header[colnum]=='Datum':
		date = col
            elif header[colnum]=='Zeit':
                time = col
            elif header[colnum][0:5]=='pH-We':
		ph = col
            elif header[colnum][0:5]=='Redox':
                redox = col
            elif header[colnum][0:5]=='Sauer':
                oxy = col
            elif header[colnum][0:5]=='Tempe':
                temp = col
            elif header[colnum][0:5]=='Füll':
                fill = col
 	        reactor_num = re.findall(r'\d+', header[colnum])
		if int(reactor_num[0]) != 5 and int(reactor_num[0]) != 6:
			if int(reactor_num[0]) != 7 and int(reactor_num[0]) != 8:
				mysql = MySQLdb.connect(mysql_opts['host'], mysql_opts['user'], mysql_opts['pass'], mysql_opts['db'])
				mysql.apilevel = "2.0"
				mysql.threadsafety = 2
				mysql.paramstyle = "format"
				cursor = mysql.cursor()
				x = mysql.cursor()
				dat_string = date + " " + time
				if check_exist(int(reactor_num[0]), datetime.strptime(dat_string, '%m/%d/%Y %H:%M'))==1:
					try:
			   			x.execute("""INSERT INTO reactor_%d (Date, PH, Redox, Oxy, Temp, Fill) VALUES ('%s', '%s', '%s', '%s', '%s', '%s')""" % (int(reactor_num[0]), datetime.strptime(dat_string, '%m/%d/%Y %H:%M'), ph, redox, oxy, temp, fill))
			   			mysql.commit()
					except:
			   			mysql.rollback()
					mysql.close()
	    elif header[colnum][0:5]=='Diffe':
		diff = col
		if int(reactor_num[0]) == 7 or int(reactor_num[0]) == 8:
			mysql = MySQLdb.connect(mysql_opts['host'], mysql_opts['user'], mysql_opts['pass'], mysql_opts['db'])
			mysql.apilevel = "2.0"
			mysql.threadsafety = 2
			mysql.paramstyle = "format"
			cursor = mysql.cursor()
			x = mysql.cursor()
			dat_string = date + " " + time
			if check_exist(int(reactor_num[0]), datetime.strptime(dat_string, '%m/%d/%Y %H:%M'))==1:
				try:
			   		x.execute("""INSERT INTO reactor_%d (Date, PH, Redox, Oxy, Temp, Fill, Diff) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s')""" % (int(reactor_num[0]), datetime.strptime(dat_string, '%m/%d/%Y %H:%M'), ph, redox, oxy, temp, fill, diff))
			   		mysql.commit()
				except:
			   		mysql.rollback()
				mysql.close()
	    elif header[colnum][0:7]=='Masse-A':
		mass1 = col
	    elif header[colnum][0:8]=='Masse Du':
		mass2 = col
 	        reactor_num = re.findall(r'\d+', header[colnum])
		mysql = MySQLdb.connect(mysql_opts['host'], mysql_opts['user'], mysql_opts['pass'], mysql_opts['db'])
		mysql.apilevel = "2.0"
		mysql.threadsafety = 2
		mysql.paramstyle = "format"
		cursor = mysql.cursor()
		x = mysql.cursor()
		dat_string = date + " " + time
		if check_exist(int(reactor_num[0]), datetime.strptime(dat_string, '%m/%d/%Y %H:%M'))==1:
			try:
		   		x.execute("""INSERT INTO reactor_%d (Date, PH, Redox, Oxy, Temp, Fill, Mass1, Mass2, Diff) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')""" % (int(reactor_num[0]), datetime.strptime(dat_string, '%m/%d/%Y %H:%M'), ph, redox, oxy, temp, fill, mass1, mass2, diff))
		   		mysql.commit()
			except:
		   		mysql.rollback()
			mysql.close()
            colnum += 1
    rownum += 1
ifile.close()