libImportADCP.py

#!/usr/bin/env python
# -*- coding: utf-8 -*-
#require sudo apt-get install python-mysqldb

# version : 31.05.2013
# author : chauvet

import MySQLdb;
import os
DBuser='root'
DBpasswd=''
DBserv='localhost'
DBname='BaseADCP'

########################The Class for importing ADCP DATA###################
class AdcpToSql:

	#constructeur
	def __init__(self,mode='BT',parent=0,nema='', nemb='', rena='', coma='', usa=DBuser, pasa=DBpasswd, serva=DBserv, basa=DBname):
		"""
			Class to load classic ASCII file generated by WinRIVER I and II.
			
			[options]
				mode [default='BT'] : specify the reference of ascii output 'BT','GGA' or 'VTG', to upload a VTG or GGA reference you must first import a BT reference file
				parent [default=0] : This is only required when importing GGA or VTG reference and refer to the id (i.e. Profcode cols in ProfileInfo table) of the BT reference, already stored in MySQL, for the same measurement.
				nema [default=''] : The path and name of the input ASCII file
				nemb [default=''] : the path and name for the output SQL file generated by parsADCP method
				rena [default=''] : The rivername (only needed for BT reference)
				coma [default=''] : The comment on the measurement (only needed for BT reference)
				
				Other arguments are for mysql account specification [default: username root password '' server localhost and database name BaseADCP]			
		"""
		self.nIn=nema
		self.nOut=nemb
		self.rname=rena
		self.comm=coma
		self.user=usa
		self.passwd=pasa
		self.serv=serva
		self.error=''
		self.base=basa
		self.mode=mode
		self.idparent=parent
			
	#methode
	def Idparent(self, id):
		self.idparent=id
	def Mode(self, mod):
		self.mode=mod
	def NameIn(self, name):
		self.nIn=name
	def NameOut(self, name):
		self.nOut=name
	def RiverName(self, name):
		self.rname=name
	def RiverComm(self, com):
		self.comm=com
	def User(self, name):
		self.user=name
	def Passwd(self, rep):
		self.passwd=rep
	def Serv(self, add):
		self.serv=add
	
	def getnids(self):
		"""
			method to get new id and new enscode from Mysql.
			This update self.nid with good id
		"""
		################Mysql connection######################################
		conn = MySQLdb.connect(self.serv,self.user,self.passwd,self.base)
		################################################################### 
		curs = conn.cursor() 
		#find the id of the last river  
		result=curs.execute('SELECT MAX(Profcode) FROM ProfileInfo')
		resa=curs.fetchone()
		result=curs.execute('SELECT MAX(GGAcode) FROM ProfileInfo')
		resb=curs.fetchone()
		result=curs.execute('SELECT MAX(VTGcode) FROM ProfileInfo')
		resc=curs.fetchone()
		
		result=max([resa[0],resb[0],resc[0]])
		#set the new id for the river
		self.nid=result+1
		#Find the max last Ensemblecode
		result=curs.execute('SELECT MAX(Ensemblecode) FROM EnsembleInfo')
		result=curs.fetchone()
		self.enscode=result[0]+1
		#close mysql connection
		curs.close();
		conn.close();
	
	def readinfo(self):
		"""
			method to pre-read file  in order to get name and comment on river (if they are set)
			Name is in the first line (originally blank)
			Comment is in the second line
		"""
		f_in=open(self.nIn,'r') #open file
		#The two first line are informations (rstrip for remove \n)
		coma=f_in.readline().rstrip()
		comb=f_in.readline().rstrip()
		if coma!='':
			#Caution name is the first 
			temp=coma.split()
			self.rname=temp[0].strip()
			self.comm=coma+' '+comb
		else:
			self.rname='NONE'
			self.comm='NONE'
		#close file
		f_in.close()

	#methode pour parser le fichier
	def parsADCP(self):
		"""
			This method read the ASCII file and parse it into a sql file that can be loaded to mysql
		"""
		#get newenscode and new id
		self.getnids()
		#open file
		f_in=open(self.nIn,'r')
		f_out=open(self.nOut+'.sql','w')
		f_out.write('-- Import sql\n'+'-- Script WinRiver .txt to Mysql \n'+'-- Author : Hugo Chauvet \n'+'-- Mailto: chauvet[at]ipgp[dot]jussieu[dot]fr\n'+'-- version : 1.01 \n'+'-- Version Date : 03.2010 \n\n'+'USE `BaseADCP`;\n')
		#The two first line are informations (rstrip for remove \n)
		coma=f_in.readline().rstrip()
		comb=f_in.readline().rstrip()
		#Test if we set a manual river name with option -rn
		if self.rname == '': #look if the river name is empty
			#Caution name is the first 
			temp=coma.split()
			rname=temp[0].strip()
		else:
			rname=self.rname
		if self.comm == '': #look if river comment is empty
			commt=coma+' '+comb
		else:
			commt=self.comm
		#The next line are general information for  Profile info table
		datal=f_in.readline().rstrip()
		#split the line with space and join them width ,
		dataf=','.join(datal.split())
		if self.mode=='BT': #If we are in BT mode we create a new Profile in ProfileInfo Table else we update the ref profile
			creat='INSERT INTO ProfileInfo (Profcode,rivername, DCL, BAT, DFCF, NDC, NPPE, TPE, PM, comment) VALUES ('
			f_out.write('-- Profile info\n')
			f_out.write(creat+str(self.nid)+',"'+rname+'",'+dataf+',"'+commt+'");\n')
		else:
			if self.mode=='GGA':
				selected='GGAcode'
			if self.mode=='VTG':
				selected='VTGcode'
				
			creat='UPDATE ProfileInfo SET '+selected+'='+str(self.nid)+' WHERE Profcode='+str(self.idparent)
			f_out.write(creat+';\n')
			
		while 1:
			#test si il y a encore des lignes
			test = f_in.readline()
			if test =='':
				break #Stop while when we get the EOF
			else:
				datal=test.rstrip()
				#For EnsembleInfo
				#The 6 lines correspond to data in EnsembleInfo table
				cpt=1
				dataf=','.join(datal.split())
				while cpt<5:
					datal=f_in.readline().rstrip()
					dataf=dataf+','+','.join(datal.split())
					cpt=cpt+1
				#for last line need somme modifications
				datal=f_in.readline().rstrip()
				datat=datal.split()
				cpt=1
				while cpt<4:
					datat[cpt]='"'+datat[cpt]+'"'
					cpt=cpt+1
				dataf=dataf+','+','.join(datat)
				#Save number of bins for ADCPData
				nbins=datat[0]
				#make the request
				creat='INSERT INTO EnsembleInfo(Profcode,Ensemblecode,ETYear,ETMonth,ETDay,ETHour,ETMin,ETSec,ETHund,ENum,NES,PITCH,ROLL,CORRHEAD,ADCPTemp,BTVelE,BTVelN,BTVelUp,BTVelErr,CBD,GGAA,GGAD,GGAHDOP,DB1,DB2,DB3,DB4,TED,TET,TDTN,TDTE,TDMG,LAT,lON,NDInv,NDfnvu,NDfnvu2,DVMP,DVTP,DVBP,DVSSDE,DVSD,DVESDE,DVED,SDML,SDBL,NBINS,MU,VR,IU,ISF,SAF) VALUES ('
				f_out.write('-- Ensemble info num:'+str(self.enscode)+' \n')
				f_out.write(creat+str(self.nid)+','+str(self.enscode)+','+dataf+');\n')
				#For ADCPData
				cpt=1
				type(nbins)
				while cpt<int(nbins)+1:
					datal=f_in.readline().rstrip()
					dataf=','.join(datal.split())
					cpt=cpt+1
					#make the request
					creat='INSERT INTO ADCPData(Ensemblecode,DEPTH,VM,VD,EVC,NVC,VVC,ERRV,BCKSB1,BCKSB2,BCKSB3,BCKSB4,PG,Q) VALUES ('
					f_out.write('-- ADCPData num:'+str(self.enscode)+' \n')
					f_out.write(creat+str(self.enscode)+','+dataf+');\n')

	
				#Set the enscode+1
				self.enscode=self.enscode+1

		#Close files
		f_out.close()
		f_in.close()

	#methode to source the sql file
	def sourceADCP(self):
		"""
			Method to load the sql file created by parsADCP to mysql
		"""
		
		#execution of source filename.sql
		if self.serv == 'localhost' :
			cmd='mysql -u'+self.user+' -p'+self.passwd+' < '+self.nOut+'.sql'
		else :
			cmd='mysql -h'+self.serv+' -u'+self.user+' -p'+self.passwd+' < '+self.nOut+'.sql'
		os.system(cmd)