from sqlalchemy import create_engine, Column, String, Integer, Boolean, MetaData, Table, case, between, ForeignKey
from sqlalchemy.orm import mapper, create_session, relationship
from sqlalchemy.ext.declarative import declarative_base
import sqlalchemy.types as types
from sqlalchemy.sql import select, and_, or_, not_, func
from time import strptime
import csv
import re
import os
import glob
import numpy as np
import string
import pandas as pd
import seawater as sw # for sw.dpth(p,lat)
import warnings
import datetime as dt
from dateutil.parser import parse as dutparse
from dataProcess import forceNumeric, forceInt, fmtVarName, data2Tbl, adFunONC, isNum
basepath='/ocean/eolson/MEOPAR/obs/'
basedir=basepath + 'ECBuoy/'
dbname='ECBuoy'
fout=open(basedir+'createDBfromECBuoy_log.txt','w')
if os.path.isfile(basedir + dbname + '.sqlite'):
os.remove(basedir + dbname + '.sqlite')
engine = create_engine('sqlite:///' + basedir + dbname + '.sqlite')
Base=declarative_base()
# define Table Classes
class FBuoyTBL(Base):
__table__=Table('FBuoyTBL', Base.metadata,
Column('ID', Integer, primary_key=True),
Column('Time_UTC', String),
Column('DecDay', forceNumeric, index=True),
Column('Tem', forceNumeric),
Column('Turb_NTU', forceNumeric),
Column('SR', forceNumeric),
Column('O2', forceNumeric),
Column('sourceFile', String),
)
Base.metadata.create_all(engine)
session = create_session(bind = engine, autocommit = False, autoflush = True)
def adFunECB(row):
idate=dutparse(row['dates'])#+dt.timedelta(hours=8) ## column named datatimestamp is in UTC
row['Time_UTC']=idate.strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3]+'Z'
row['DecDay']=(idate.date()-dt.date(1900,1,1)).days+float(idate.hour)/24.0+float(idate.minute)/1440.0+float(idate.second)/86400.0
del row['dates']
return row
fpath='/ocean/eolson/MEOPAR/obs/ECBuoy/exportbuoy.txt'
header=('dates','turb','temp','sal','O2')
trDict={'dates':'dates','turb':'Turb_NTU','temp':'Tem','sal':'SR','O2':'O2'}
adlValDict={'sourceFile':'exportbuoy.txt'}
reqDta=('Turb_NTU',)
data2Tbl(fpath,session,FBuoyTBL,1,',',header,trDict,adlValDict,reqDta,adFunECB)
fpath2='/ocean/eolson/MEOPAR/obs/ECBuoy/BuoyWQData2016-10-31to20170721NoOverlap.csv'
header2=('dates','temp','specCond','ph','turb','O2Per','O2mlg')
trDict2={'dates':'dates','turb':'Turb_NTU','temp':'Tem','O2mlg':'O2'}
adlValDict2={'sourceFile':'BuoyWQData2016-10-31to20170721NoOverlap.csv'}
data2Tbl(fpath2,session,FBuoyTBL,1,',',header2,trDict2,adlValDict2,reqDta,adFunECB)
def adFunECBPac(row):
idate=dutparse(row['dates'])+dt.timedelta(hours=8) ## these times are UTC-8
row['Time_UTC']=idate.strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3]+'Z'
row['DecDay']=(idate.date()-dt.date(1900,1,1)).days+float(idate.hour)/24.0+float(idate.minute)/1440.0+float(idate.second)/86400.0
del row['dates']
return row
fpath3='/ocean/eolson/MEOPAR/obs/ECBuoy/FRBWQ_20170720to20190617NoOverlap.csv'
header3=('dates','temp','specCond','ph','turb','O2mlg')
trDict3={'dates':'dates','turb':'Turb_NTU','temp':'Tem','O2mlg':'O2'}
adlValDict3={'sourceFile':'FRBWQ_20170720to20190617NoOverlap.csv'}
data2Tbl(fpath3,session,FBuoyTBL,3,',',header3,trDict3,adlValDict3,reqDta,adFunECBPac)
print([row for row in session.query(FBuoyTBL.ID,FBuoyTBL.Time_UTC,FBuoyTBL.DecDay,FBuoyTBL.Turb_NTU).limit(5)])
[(1, '2009-01-01T00:10:21.000Z', 39812.0071875, 6.7), (2, '2009-01-01T01:10:21.000Z', 39812.048854166664, 8.2), (3, '2009-01-01T02:10:21.000Z', 39812.090520833335, 14.8), (4, '2009-01-01T04:10:21.000Z', 39812.173854166664, 5.3), (5, '2009-01-01T05:10:21.000Z', 39812.215520833335, 5.1)]
session.close()
engine.dispose()
# add to ONC db
basedir='/ocean/eolson/MEOPAR/obs/ONC/'
dbname='ONC'
engine = create_engine('sqlite:///' + basedir + dbname + '.sqlite', echo = False)
Base=declarative_base(engine)
connection=engine.connect()
if engine.dialect.has_table(connection,'FBuoyTBL'):
# delete existing ModelGridTBL
connection.execute('DROP TABLE FBuoyTBL')
connection.close()
# reflect existing tables
class HBDBTurbTBL(Base):
__tablename__= 'HBDBTurbTBL'
__table_args__= {'autoload':True}
class HBDBSalTBL(Base):
__tablename__= 'HBDBSalTBL'
__table_args__= {'autoload':True}
class TDPTurbTBL(Base):
__tablename__= 'TDPTurbTBL'
__table_args__= {'autoload':True}
class TDPSalTBL(Base):
__tablename__= 'TDPSalTBL'
__table_args__= {'autoload':True}
class TSBTurbTBL(Base):
__tablename__= 'TSBTurbTBL'
__table_args__= {'autoload':True}
class TSBSalTBL(Base):
__tablename__= 'TSBSalTBL'
__table_args__= {'autoload':True}
# create new Table
class FBuoyTBL(Base):
__table__=Table('FBuoyTBL', Base.metadata,
Column('ID', Integer, primary_key=True),
Column('Time_UTC', String),
Column('DecDay', forceNumeric, index=True),
Column('Tem', forceNumeric),
Column('Turb_NTU', forceNumeric),
Column('SR', forceNumeric),
Column('O2', forceNumeric),
Column('sourceFile', String))
Base.metadata.create_all(engine)
session = create_session(bind = engine, autocommit = False, autoflush = True)
fpath='/ocean/eolson/MEOPAR/obs/ECBuoy/exportbuoy.txt'
header=('dates','turb','temp','sal','O2')
trDict={'dates':'dates','turb':'Turb_NTU','temp':'Tem','sal':'SR','O2':'O2'}
adlValDict={'sourceFile':'exportbuoy.txt'}
reqDta=('Turb_NTU',)
def adFunECB(row):
idate=dutparse(row['dates'])#+dt.timedelta(hours=8)
row['Time_UTC']=idate.strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3]+'Z'
row['DecDay']=(idate.date()-dt.date(1900,1,1)).days+float(idate.hour)/24.0+float(idate.minute)/1440.0+float(idate.second)/86400.0
del row['dates']
return row
data2Tbl(fpath,session,FBuoyTBL,1,',',header,trDict,adlValDict,reqDta,adFunECB)
fpath2='/ocean/eolson/MEOPAR/obs/ECBuoy/BuoyWQData2016-10-31toPresentToCSV.csv'
header2=('dates','temp','specCond','ph','turb','O2Per','O2mlg')
trDict2={'dates':'dates','turb':'Turb_NTU','temp':'Tem','O2mlg':'O2'}
adlValDict2={'sourceFile':'BuoyWQData2016-10-31toPresentToCSV.csv'}
data2Tbl(fpath2,session,FBuoyTBL,1,',',header2,trDict2,adlValDict2,reqDta,adFunECB)
def adFunECBPac(row):
idate=dutparse(row['dates'])+dt.timedelta(hours=8) ## these times are UTC-8
row['Time_UTC']=idate.strftime("%Y-%m-%dT%H:%M:%S.%f")[:-3]+'Z'
row['DecDay']=(idate.date()-dt.date(1900,1,1)).days+float(idate.hour)/24.0+float(idate.minute)/1440.0+float(idate.second)/86400.0
del row['dates']
return row
fpath3='/ocean/eolson/MEOPAR/obs/ECBuoy/FRBWQ_20170720to20190617NoOverlap.csv'
header3=('dates','temp','specCond','ph','turb','O2mlg')
trDict3={'dates':'dates','turb':'Turb_NTU','temp':'Tem','O2mlg':'O2'}
adlValDict3={'sourceFile':'FRBWQ_20170720to20190617NoOverlap.csv'}
data2Tbl(fpath3,session,FBuoyTBL,3,',',header3,trDict3,adlValDict3,reqDta,adFunECBPac)
print([row for row in session.query(FBuoyTBL.ID,FBuoyTBL.Time_UTC,FBuoyTBL.DecDay,FBuoyTBL.Turb_NTU).limit(5)])
session.close()
engine.dispose()
--------------------------------------------------------------------------- NoSuchTableError Traceback (most recent call last) <ipython-input-7-1041735e0219> in <module>() 12 13 # reflect existing tables ---> 14 class HBDBTurbTBL(Base): 15 __tablename__= 'HBDBTurbTBL' 16 __table_args__= {'autoload':True} /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/api.py in __init__(cls, classname, bases, dict_) 53 def __init__(cls, classname, bases, dict_): 54 if '_decl_class_registry' not in cls.__dict__: ---> 55 _as_declarative(cls, classname, cls.__dict__) 56 type.__init__(cls, classname, bases, dict_) 57 /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py in _as_declarative(cls, classname, dict_) 86 return 87 ---> 88 _MapperConfig.setup_mapping(cls, classname, dict_) 89 90 /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py in setup_mapping(cls, cls_, classname, dict_) 101 else: 102 cfg_cls = _MapperConfig --> 103 cfg_cls(cls_, classname, dict_) 104 105 def __init__(self, cls_, classname, dict_): /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py in __init__(self, cls_, classname, dict_) 129 self._extract_declared_columns() 130 --> 131 self._setup_table() 132 133 self._setup_inheritance() /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py in _setup_table(self) 393 tablename, cls.metadata, 394 *(tuple(declared_columns) + tuple(args)), --> 395 **table_kw) 396 else: 397 table = cls.__table__ /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in __new__(cls, *args, **kw) 414 except: 415 with util.safe_reraise(): --> 416 metadata._remove_table(name, schema) 417 418 @property /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py in __exit__(self, type_, value, traceback) 58 exc_type, exc_value, exc_tb = self._exc_info 59 self._exc_info = None # remove potential circular references ---> 60 compat.reraise(exc_type, exc_value, exc_tb) 61 else: 62 if not compat.py3k and self._exc_info and self._exc_info[1]: /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py in reraise(tp, value, tb, cause) 184 if value.__traceback__ is not tb: 185 raise value.with_traceback(tb) --> 186 raise value 187 188 else: /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in __new__(cls, *args, **kw) 409 metadata._add_table(name, schema, table) 410 try: --> 411 table._init(name, metadata, *args, **kw) 412 table.dispatch.after_parent_attach(table, metadata) 413 return table /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in _init(self, name, metadata, *args, **kwargs) 482 # circular foreign keys 483 if autoload: --> 484 self._autoload(metadata, autoload_with, include_columns) 485 486 # initialize all the column, etc. objects. done after reflection to /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py in _autoload(self, metadata, autoload_with, include_columns, exclude_columns) 506 bind.run_callable( 507 bind.dialect.reflecttable, --> 508 self, include_columns, exclude_columns 509 ) 510 /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in run_callable(self, callable_, *args, **kwargs) 1970 """ 1971 with self.contextual_connect() as conn: -> 1972 return conn.run_callable(callable_, *args, **kwargs) 1973 1974 def execute(self, statement, *multiparams, **params): /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py in run_callable(self, callable_, *args, **kwargs) 1475 1476 """ -> 1477 return callable_(self, *args, **kwargs) 1478 1479 def _run_visitor(self, visitorcallable, element, **kwargs): /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py in reflecttable(self, connection, table, include_columns, exclude_columns) 362 self, connection, table, include_columns, exclude_columns): 363 insp = reflection.Inspector.from_engine(connection) --> 364 return insp.reflecttable(table, include_columns, exclude_columns) 365 366 def get_pk_constraint(self, conn, table_name, schema=None, **kw): /home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py in reflecttable(self, table, include_columns, exclude_columns) 569 570 if not found_table: --> 571 raise exc.NoSuchTableError(table.name) 572 573 self._reflect_pk( NoSuchTableError: HBDBTurbTBL