1 #!/usr/bin/env python
2
3 # DESCRIPTION: Use pysqlite to import the mag data into the database
4
5 # $Id: build-db.py,v 1.17 2005/02/22 00:54:01 schwehr Exp $
6
7 ######################################################################
8 # Copyright (C) 2005 Kurt Schwehr
9 #
10 # This program is free software; you can redistribute it and/or modify
11 # it under the terms of the GNU General Public License as published by
12 # the Free Software Foundation; either version 2 of the License, or
13 # (at your option) any later version.
14 #
15 # This program is distributed in the hope that it will be useful,
16 # but WITHOUT ANY WARRANTY; without even the implied warranty of
17 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
18 # GNU General Public License for more details.
19 #
20 # You should have received a copy of the GNU General Public License
21 # along with this program; if not, write to the Free Software
22 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
23 ######################################################################
24
25 import os # os.remove()
26 import sys # sys.exit()
27 import string
28 import sqlite # pysqlite
29 import ams # Support routines for AMS stuff - by Kurt
30
31
32 # Labview/GEE: 1/3/05 4:33 PM
33 # SQL: 2005-03-01 16:33:00
34 def makeSqlDate (date,time,ampm):
35 """ Convert ' 1/3/05 4:33 PM' -> '2005-03-01 16:33:00' """
36 month,day,year=date.split("/")
37 year = int (year); month = int(month); day = int(day)
38 hour,min=time.split(":")
39 hour = int(hour)
40 if ("AM"==ampm): pass
41 elif ("PM"==ampm):
42 if (12 != hour): hour += 12
43 else: print ("oh crap!")
44 # FIX: Watch out for 2080!
45 if (year > 80): year += 1900
46 else: year += 2000
47 sqlDate = str(year) + "-" + ("%02d" % month) + "-" + ("%02d" % day)
48 sqlDate += " " + str(hour) + ":" + min + ":00"
49 #print "sqlDate: ", sqlDate
50 return sqlDate
51
52 def build_mag (db_cx,magfileName):
53 """ Create the magnetometer data. nrm, afdemag, etc"""
54 print " building mag table "
55 cu = db_cx.cursor();
56 print "FIX: add my new fields on to the end."
57 createStr = "create table mag ( samplename VARCHAR(40), treatment REAL, csd REAL, intensity REAL, dec REAL, inc REAL, timeMeasured TIMESTAMP"
58 #createStr += ", magnetometer VARCHAR(1), positions, numMeasurements, "
59 createStr += ", type VARCHAR(10), operator VARCHAR(20)"
60 createStr += ");"
61 cu.execute(createStr);
62
63 magfile = open (magfileName);
64
65 for line in magfile.xreadlines():
66 if '#' == line[0]:
67 continue
68 # csd = circular standard deviation
69 tmp1,time_ampm,flags,numsamples=line.split(';')
70 name,treatment,csd,intensity,dec,inc,date=tmp1.split()
71 time,ampm=time_ampm.split()
72 sqlDate = makeSqlDate(date, time, ampm)
73
74 intensity = "%.10f" % float(intensity);
75
76 # FIX: parse the flags for the database.
77
78 lst = line.split()
79 assert 10 == len(lst) # FIX: make this a more obvious error message
80 type = lst[8]
81 operator = lst[9]
82 #print type,operator
83
84 entryStr = "insert into mag (samplename,treatment,csd,intensity,dec,inc,timeMeasured, type,operator) values (";
85 entryStr += " '" + name +"'";
86 entryStr += "," + treatment;
87 entryStr += "," + csd;
88 entryStr += "," + intensity;
89 entryStr += "," + dec;
90 entryStr += "," + inc;
91 entryStr += ", '" + sqlDate + "'";
92
93 entryStr += ", '" + type + "'";
94 entryStr += ", '" + operator + "'";
95
96 entryStr += ");";
97
98 #print entryStr
99 cu.execute(entryStr);
100
101 db_cx.commit();
102
103
104 def build_coreloc(db_cx):
105 print " building coreloc table"
106 cu = db_cx.cursor()
107 cu.execute ("create table CoreLoc (id INTEGER PRIMARY KEY, cruise VARCHAR(20), corenum INTEGER, coretype varchar(1), datecollected TIMESTAMP, lat REAL, lon REAL, depth REAL, sheetURL TEXT);")
108
109 # (time in UTC)
110 insertStr="insert into CoreLoc (cruise, corenum, coretype, datecollected, lat, lon, depth, sheetURL) values "
111 baseURL="http://schwehr.org/Gaviota/bpsio-Aug04/bpsio-log/images/"
112
113 cu.execute (insertStr + "('bp04',1,'g','2004-08-04 21:09:15', 34.36116666, 120.108 , 480, '" + baseURL + "bpsio-log-35.jpg');")
114 cu.execute (insertStr + "('bp04',2,'g','2004-08-04 23:06:23', 34.36999999, 120.1075 , 439, '" + baseURL + "bpsio-log-36.jpg');")
115 cu.execute (insertStr + "('bp04',3,'g','2004-08-05 01:11:42', 34.36566666, 120.05883333, 452, '" + baseURL + "bpsio-log-37.jpg');")
116 cu.execute (insertStr + "('bp04',4,'g','2004-08-05 02:24:35', 34.37899999, 120.05716666, 322, '" + baseURL + "bpsio-log-38.jpg');")
117 cu.execute (insertStr + "('bp04',5,'g','2004-08-05 16:10:00', 34.33704999, 120.02356666, 541, '" + baseURL + "bpsio-log-42.jpg');")
118 cu.execute (insertStr + "('bp04',6,'g','2004-08-05 17:51:30', 34.32998333, 119.97723333, 503, '" + baseURL + "bpsio-log-43.jpg');")
119
120 db_cx.commit();
121
122 def build_sections(db_cx):
123 """ How long is each section of cores. Cores are split to fit into D-tubes."""
124 print " building sections table"
125 cu = db_cx.cursor()
126 cu.execute ("create table sections (id INTEGER PRIMARY KEY, cruise VARCHAR(20), corenum INTEGER, section INTEGER, sectopdepth REAL, sectionlength REAL);")
127
128 insertStr="insert into sections (cruise,corenum,section,sectopdepth,sectionlength)values"
129
130 # Sec 2 130 from description. Give s1 37.5 cm
131 cu.execute (insertStr + "('bp04',1,1,00.0,037.5);") # CORE 1
132 cu.execute (insertStr + "('bp04',1,2,37.5,130.0);") # CORE 1
133 cu.execute (insertStr + "('bp04',2,1,00.0,073.0);") # CORE 2
134 cu.execute (insertStr + "('bp04',3,1,00.0,040.0);") # CORE 3 - total processCore - 191.5 cm
135 cu.execute (insertStr + "('bp04',3,2,39.5,151.5);") # CORE 3 - total processCore - 191.5 cm
136 cu.execute (insertStr + "('bp04',4,1,00.0,125.0);") # CORE 4 - total processCore - 125 cm
137 cu.execute (insertStr + "('bp04',5,1,00.0,044.0);") # CORE 5 - total processCore - 194
138 cu.execute (insertStr + "('bp04',5,2,44.0,150.0);") # CORE 5 - total processCore - 194
139 cu.execute (insertStr + "('bp04',6,1,00.0,147.5);") # CORE 6 - total length 147.5
140
141 db_cx.commit()
142
143 def build_weights(db_cx, weightsfileName):
144 cu = db_cx.cursor()
145 cu.execute ("create table weights (id INTEGER PRIMARY KEY, samplename VARCHAR(40), weight REAL, timeMeasured TIMESTAMP);")
146 weightsfile = open(weightsfileName,"r")
147
148 for line in weightsfile.xreadlines():
149 if '#' == line[0]:
150 continue
151 #print line,
152 name,weight,date,time=line.split()
153 insertStr = "insert into weights (samplename,weight,timeMeasured)values("
154 insertStr += "'" + name + "'," + weight
155 insertStr += ",'" + date + " " +time+ "'"
156 insertStr += ");"
157 #print insertStr
158 cu.execute (insertStr)
159 cx.commit()
160
161
162 # This is the big kahuna... 4 line file format:
163 #
164 # bp04-6gw-s1-141.2 schwehr 1/3/05 4:33 PM 1716 -4.75
165 # 9.3800E+1 9.2950E+1 9.3550E+1 9.3550E+1 9.2950E+1
166 # 8.9150E+1 8.9100E+1 9.2900E+1 8.9150E+1 8.8900E+1
167 # 8.9950E+1 8.8750E+1 8.5100E+1 8.9950E+1 8.8800E+1
168
169 def build_ams(db_cx,k15fileName):
170 """ Parse Jeff Gee's kappa bridge format file """
171 print " building ams table..."
172
173 cu = db_cx.cursor()
174 createStr = "create table ams "
175 createStr += "(id INTEGER PRIMARY KEY, samplename VARCHAR(40), "
176 createStr += "user VARCHAR(40), datemeasured TIMESTAMP, cruise VARCHAR(20), "
177 createStr += "corenum INTEGER, coretype VARCHAR(1), corehalf VARCHAR(1), "
178 createStr += "section INTEGER, sectionoffset REAL," #, depth REAL,"
179 createStr += "counts INTEGER, sampleholder REAL,"
180 createStr += "k1 REAL, k2 REAL, k3 REAL, k4 REAL, k5 REAL, "
181 createStr += "k6 REAL, k7 REAL, k8 REAL, k9 REAL, k10 REAL, "
182 createStr += "k11 REAL, k12 REAL, k13 REAL, k14 REAL, k15 REAL, "
183 # k15_s
184 createStr += "s1 REAL, s2 REAL, s3 REAL, s4 REAL, s5 REAL, s6 REAL, sigma REAL, "
185 # k15_hext
186 createStr += "bulksusc REAL, F REAL, F12 REAL, F23 REAL,"
187 createStr += "tau1 REAL, dec1 REAL, inc1 REAL,"
188 createStr += "tau2 REAL, dec2 REAL, inc2 REAL,"
189 createStr += "tau3 REAL, dec3 REAL, inc3 REAL"
190 createStr += ");"
191 cu.execute (createStr)
192
193 k15file = open (k15fileName,"r")
194
195 four = [] # Keep the 4 lines from
196 for line in k15file.xreadlines():
197 if '#' == line[0]: continue # Skippy the commenties
198 four.append(line)
199 if 4==len(four):
200 # We have a sample, so process it!
201 insertStr = "insert into ams "
202 insertStr += "(samplename, user, datemeasured, cruise, corenum, coretype, corehalf, section, sectionoffset, " # depth,
203 insertStr += "counts, sampleholder, "
204 insertStr += "k1, k2, k3, k4, k5, k6, k7, k8, k9, k10, k11, k12, k13, k14, k15 "
205 insertStr += ",s1, s2, s3, s4, s5, s6, sigma"
206 insertStr += ",bulksusc, F, F12, F23, "
207 insertStr += "tau1, dec1, inc1, "
208 insertStr += "tau2, dec2, inc2, "
209 insertStr += "tau3, dec3, inc3 "
210 insertStr += ") values ("
211
212 sampleNameStr, user, date, time, ampm, counts, sampleholder = four[0].split()
213 sn = ams.SampleName(sampleNameStr)
214 dateMeasured = makeSqlDate (date,time,ampm)
215 insertStr += "'" + sampleNameStr
216 insertStr += "','" + user
217 insertStr += "','" + dateMeasured + "'"
218 insertStr += ",'" + sn.cruise + "'," + str(sn.coreNum) + ",'" + sn.coreType + "','" + sn.coreHalf + "'"
219 insertStr += ", " + str(sn.section) + "," + str(sn.depthOffset) + "," + counts + "," + sampleholder
220
221 k1_5 = four[1].split()
222 k6_10 = four[2].split()
223 k11_15 = four[3].split()
224 insertStr += ", " + k1_5[0] + ", " + k1_5[1] + ", " + k1_5[2] + ", " + k1_5[3] + ", " + k1_5[4]
225 insertStr += ", " + k6_10[0] + ", " + k6_10[1] + ", " + k6_10[2] + ", " + k6_10[3] + ", " + k6_10[4]
226 insertStr += ", " + k11_15[0] + ", " + k11_15[1] + ", " + k11_15[2] + ", " + k11_15[3] + ", " + k11_15[4]
227
228 s_Matrix = ams.S_Matrix(four)
229
230 # Last value is also known as sigma
231 for i in range(7):
232 insertStr += ", " + str(s_Matrix.values[i])
233
234 hext = ams.Hext(four)
235
236 insertStr += ", " + hext.bulk_susc + "," + hext.F + "," + hext.F12 + "," + hext.F23
237 for i in range(3):
238 insertStr += ", " + hext.tau[i] + "," + hext.dec[i] + "," + hext.inc[i]
239
240 insertStr += ");"
241
242 #print insertStr
243 cu.execute(insertStr)
244 four = []
245
246 cx.commit()
247 print " done"
248
249
250
251 if __name__ == '__main__':
252 print "Starting to build the data base"
253 dbFileName="bpsio04.db"
254
255 cx = sqlite.connect (dbFileName);
256 build_mag(cx, "bpsio04.mag")
257 build_coreloc(cx)
258 build_sections(cx); depthLookup = ams.DepthLookup(cx)
259 build_weights(cx,"weights.dat")
260 build_ams(cx,"bpsio04.k15")
261 #build_ams(cx,"t.k15")
262
263
264 else:
265 print "How can this not be __main__? What the heck do you think you are doing?"
syntax highlighted by Code2HTML, v. 0.9.1