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