{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine, Column, String, Integer, Boolean, MetaData, Table, case, between, ForeignKey\n",
    "from sqlalchemy.orm import mapper, create_session, relationship\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "import sqlalchemy.types as types\n",
    "from sqlalchemy.sql import select, and_, or_, not_, func\n",
    "from time import strptime\n",
    "import csv\n",
    "import re\n",
    "import os\n",
    "import glob\n",
    "import numpy as np\n",
    "import string\n",
    "import pandas as pd\n",
    "import seawater as sw # for sw.dpth(p,lat) \n",
    "import warnings\n",
    "import datetime as dt\n",
    "from dateutil.parser import parse as dutparse\n",
    "from dataProcess import forceNumeric, forceInt, fmtVarName, data2Tbl, adFunONC, isNum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "basepath='/ocean/eolson/MEOPAR/obs/'\n",
    "basedir=basepath + 'ECBuoy/'\n",
    "dbname='ECBuoy'\n",
    "\n",
    "fout=open(basedir+'createDBfromECBuoy_log.txt','w')\n",
    "\n",
    "if os.path.isfile(basedir + dbname + '.sqlite'):\n",
    "    os.remove(basedir + dbname + '.sqlite')\n",
    "engine = create_engine('sqlite:///' + basedir + dbname + '.sqlite')\n",
    "Base=declarative_base()\n",
    "\n",
    "# define Table Classes\n",
    "\n",
    "class FBuoyTBL(Base):\n",
    "    __table__=Table('FBuoyTBL', Base.metadata,\n",
    "                    Column('ID', Integer, primary_key=True),\n",
    "                    Column('Time_UTC', String),\n",
    "                    Column('DecDay', forceNumeric, index=True),\n",
    "                    Column('Tem', forceNumeric),\n",
    "                    Column('Turb_NTU', forceNumeric),\n",
    "                    Column('SR', forceNumeric),\n",
    "                    Column('O2', forceNumeric),\n",
    "                    Column('sourceFile', String),\n",
    "                    )\n",
    "    \n",
    "Base.metadata.create_all(engine)\n",
    "session = create_session(bind = engine, autocommit = False, autoflush = True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def adFunECB(row):\n",
    "    idate=dutparse(row['dates'])#+dt.timedelta(hours=8) ## column named datatimestamp is in UTC\n",
    "    row['Time_UTC']=idate.strftime(\"%Y-%m-%dT%H:%M:%S.%f\")[:-3]+'Z'\n",
    "    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 \n",
    "    del row['dates']\n",
    "    return row\n",
    "\n",
    "fpath='/ocean/eolson/MEOPAR/obs/ECBuoy/exportbuoy.txt'\n",
    "header=('dates','turb','temp','sal','O2')\n",
    "trDict={'dates':'dates','turb':'Turb_NTU','temp':'Tem','sal':'SR','O2':'O2'}\n",
    "adlValDict={'sourceFile':'exportbuoy.txt'}\n",
    "reqDta=('Turb_NTU',)\n",
    "data2Tbl(fpath,session,FBuoyTBL,1,',',header,trDict,adlValDict,reqDta,adFunECB)\n",
    "\n",
    "fpath2='/ocean/eolson/MEOPAR/obs/ECBuoy/BuoyWQData2016-10-31to20170721NoOverlap.csv'\n",
    "header2=('dates','temp','specCond','ph','turb','O2Per','O2mlg')\n",
    "trDict2={'dates':'dates','turb':'Turb_NTU','temp':'Tem','O2mlg':'O2'}\n",
    "adlValDict2={'sourceFile':'BuoyWQData2016-10-31to20170721NoOverlap.csv'}\n",
    "data2Tbl(fpath2,session,FBuoyTBL,1,',',header2,trDict2,adlValDict2,reqDta,adFunECB)\n",
    "\n",
    "def adFunECBPac(row):\n",
    "    idate=dutparse(row['dates'])+dt.timedelta(hours=8) ## these times are UTC-8\n",
    "    row['Time_UTC']=idate.strftime(\"%Y-%m-%dT%H:%M:%S.%f\")[:-3]+'Z'\n",
    "    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 \n",
    "    del row['dates']\n",
    "    return row\n",
    "\n",
    "fpath3='/ocean/eolson/MEOPAR/obs/ECBuoy/FRBWQ_20170720to20190617NoOverlap.csv'\n",
    "header3=('dates','temp','specCond','ph','turb','O2mlg')\n",
    "trDict3={'dates':'dates','turb':'Turb_NTU','temp':'Tem','O2mlg':'O2'}\n",
    "adlValDict3={'sourceFile':'FRBWQ_20170720to20190617NoOverlap.csv'}\n",
    "data2Tbl(fpath3,session,FBuoyTBL,3,',',header3,trDict3,adlValDict3,reqDta,adFunECBPac)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[(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)]\n"
     ]
    }
   ],
   "source": [
    "print([row for row in session.query(FBuoyTBL.ID,FBuoyTBL.Time_UTC,FBuoyTBL.DecDay,FBuoyTBL.Turb_NTU).limit(5)])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "session.close()\n",
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# add to ONC db"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "ename": "NoSuchTableError",
     "evalue": "HBDBTurbTBL",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNoSuchTableError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-7-1041735e0219>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     12\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     13\u001b[0m \u001b[0;31m# reflect existing tables\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 14\u001b[0;31m \u001b[0;32mclass\u001b[0m \u001b[0mHBDBTurbTBL\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mBase\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     15\u001b[0m     \u001b[0m__tablename__\u001b[0m\u001b[0;34m=\u001b[0m \u001b[0;34m'HBDBTurbTBL'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     16\u001b[0m     \u001b[0m__table_args__\u001b[0m\u001b[0;34m=\u001b[0m \u001b[0;34m{\u001b[0m\u001b[0;34m'autoload'\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;32mTrue\u001b[0m\u001b[0;34m}\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/api.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(cls, classname, bases, dict_)\u001b[0m\n\u001b[1;32m     53\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__init__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbases\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdict_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     54\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;34m'_decl_class_registry'\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__dict__\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 55\u001b[0;31m             \u001b[0m_as_declarative\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__dict__\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     56\u001b[0m         \u001b[0mtype\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__init__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mbases\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdict_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     57\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py\u001b[0m in \u001b[0;36m_as_declarative\u001b[0;34m(cls, classname, dict_)\u001b[0m\n\u001b[1;32m     86\u001b[0m         \u001b[0;32mreturn\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     87\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 88\u001b[0;31m     \u001b[0m_MapperConfig\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msetup_mapping\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdict_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     89\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     90\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py\u001b[0m in \u001b[0;36msetup_mapping\u001b[0;34m(cls, cls_, classname, dict_)\u001b[0m\n\u001b[1;32m    101\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    102\u001b[0m             \u001b[0mcfg_cls\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_MapperConfig\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 103\u001b[0;31m         \u001b[0mcfg_cls\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcls_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdict_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    104\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    105\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__init__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcls_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mclassname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdict_\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py\u001b[0m in \u001b[0;36m__init__\u001b[0;34m(self, cls_, classname, dict_)\u001b[0m\n\u001b[1;32m    129\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_extract_declared_columns\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    130\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 131\u001b[0;31m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_setup_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    132\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    133\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_setup_inheritance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/ext/declarative/base.py\u001b[0m in \u001b[0;36m_setup_table\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    393\u001b[0m                     \u001b[0mtablename\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mmetadata\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    394\u001b[0m                     \u001b[0;34m*\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtuple\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdeclared_columns\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m+\u001b[0m \u001b[0mtuple\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 395\u001b[0;31m                     **table_kw)\n\u001b[0m\u001b[1;32m    396\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    397\u001b[0m             \u001b[0mtable\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mcls\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__table__\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py\u001b[0m in \u001b[0;36m__new__\u001b[0;34m(cls, *args, **kw)\u001b[0m\n\u001b[1;32m    414\u001b[0m             \u001b[0;32mexcept\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    415\u001b[0m                 \u001b[0;32mwith\u001b[0m \u001b[0mutil\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0msafe_reraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 416\u001b[0;31m                     \u001b[0mmetadata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_remove_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    417\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    418\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mproperty\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py\u001b[0m in \u001b[0;36m__exit__\u001b[0;34m(self, type_, value, traceback)\u001b[0m\n\u001b[1;32m     58\u001b[0m             \u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     59\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m   \u001b[0;31m# remove potential circular references\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 60\u001b[0;31m             \u001b[0mcompat\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreraise\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mexc_type\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_value\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc_tb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     61\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     62\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mcompat\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpy3k\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m \u001b[0;32mand\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_exc_info\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m1\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/util/compat.py\u001b[0m in \u001b[0;36mreraise\u001b[0;34m(tp, value, tb, cause)\u001b[0m\n\u001b[1;32m    184\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__traceback__\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mtb\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    185\u001b[0m             \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtb\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 186\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    187\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    188\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py\u001b[0m in \u001b[0;36m__new__\u001b[0;34m(cls, *args, **kw)\u001b[0m\n\u001b[1;32m    409\u001b[0m             \u001b[0mmetadata\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_add_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    410\u001b[0m             \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 411\u001b[0;31m                 \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_init\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmetadata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    412\u001b[0m                 \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdispatch\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mafter_parent_attach\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmetadata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    413\u001b[0m                 \u001b[0;32mreturn\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py\u001b[0m in \u001b[0;36m_init\u001b[0;34m(self, name, metadata, *args, **kwargs)\u001b[0m\n\u001b[1;32m    482\u001b[0m         \u001b[0;31m# circular foreign keys\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    483\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mautoload\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 484\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_autoload\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmetadata\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mautoload_with\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0minclude_columns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    485\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    486\u001b[0m         \u001b[0;31m# initialize all the column, etc. objects.  done after reflection to\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/sql/schema.py\u001b[0m in \u001b[0;36m_autoload\u001b[0;34m(self, metadata, autoload_with, include_columns, exclude_columns)\u001b[0m\n\u001b[1;32m    506\u001b[0m             bind.run_callable(\n\u001b[1;32m    507\u001b[0m                 \u001b[0mbind\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdialect\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreflecttable\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 508\u001b[0;31m                 \u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0minclude_columns\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexclude_columns\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    509\u001b[0m             )\n\u001b[1;32m    510\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mrun_callable\u001b[0;34m(self, callable_, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1970\u001b[0m         \"\"\"\n\u001b[1;32m   1971\u001b[0m         \u001b[0;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcontextual_connect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1972\u001b[0;31m             \u001b[0;32mreturn\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_callable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcallable_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1973\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1974\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mstatement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0mmultiparams\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/base.py\u001b[0m in \u001b[0;36mrun_callable\u001b[0;34m(self, callable_, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1475\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1476\u001b[0m         \"\"\"\n\u001b[0;32m-> 1477\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mcallable_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1478\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1479\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_run_visitor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvisitorcallable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0melement\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/default.py\u001b[0m in \u001b[0;36mreflecttable\u001b[0;34m(self, connection, table, include_columns, exclude_columns)\u001b[0m\n\u001b[1;32m    362\u001b[0m             self, connection, table, include_columns, exclude_columns):\n\u001b[1;32m    363\u001b[0m         \u001b[0minsp\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mreflection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mInspector\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfrom_engine\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 364\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0minsp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mreflecttable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0minclude_columns\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexclude_columns\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    365\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    366\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mget_pk_constraint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mconn\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable_name\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkw\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/home/eolson/anaconda3/lib/python3.5/site-packages/sqlalchemy/engine/reflection.py\u001b[0m in \u001b[0;36mreflecttable\u001b[0;34m(self, table, include_columns, exclude_columns)\u001b[0m\n\u001b[1;32m    569\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    570\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mfound_table\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 571\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mNoSuchTableError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    572\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    573\u001b[0m         self._reflect_pk(\n",
      "\u001b[0;31mNoSuchTableError\u001b[0m: HBDBTurbTBL"
     ]
    }
   ],
   "source": [
    "basedir='/ocean/eolson/MEOPAR/obs/ONC/'\n",
    "dbname='ONC'\n",
    "\n",
    "engine = create_engine('sqlite:///' + basedir + dbname + '.sqlite', echo = False)\n",
    "Base=declarative_base(engine)\n",
    "\n",
    "connection=engine.connect()\n",
    "if engine.dialect.has_table(connection,'FBuoyTBL'):\n",
    "    # delete existing ModelGridTBL\n",
    "    connection.execute('DROP TABLE FBuoyTBL')\n",
    "connection.close()\n",
    "\n",
    "# reflect existing tables\n",
    "class HBDBTurbTBL(Base):\n",
    "    __tablename__= 'HBDBTurbTBL'\n",
    "    __table_args__= {'autoload':True}\n",
    "class HBDBSalTBL(Base):\n",
    "    __tablename__= 'HBDBSalTBL'\n",
    "    __table_args__= {'autoload':True}\n",
    "class TDPTurbTBL(Base):\n",
    "    __tablename__= 'TDPTurbTBL'\n",
    "    __table_args__= {'autoload':True}\n",
    "class TDPSalTBL(Base):\n",
    "    __tablename__= 'TDPSalTBL'\n",
    "    __table_args__= {'autoload':True}\n",
    "class TSBTurbTBL(Base):\n",
    "    __tablename__= 'TSBTurbTBL'\n",
    "    __table_args__= {'autoload':True}\n",
    "class TSBSalTBL(Base):\n",
    "    __tablename__= 'TSBSalTBL'\n",
    "    __table_args__= {'autoload':True}\n",
    "\n",
    "# create new Table\n",
    "class FBuoyTBL(Base):\n",
    "    __table__=Table('FBuoyTBL', Base.metadata,\n",
    "                    Column('ID', Integer, primary_key=True),\n",
    "                    Column('Time_UTC', String),\n",
    "                    Column('DecDay', forceNumeric, index=True),\n",
    "                    Column('Tem', forceNumeric),\n",
    "                    Column('Turb_NTU', forceNumeric),\n",
    "                    Column('SR', forceNumeric),\n",
    "                    Column('O2', forceNumeric),\n",
    "                    Column('sourceFile', String))\n",
    "        \n",
    "Base.metadata.create_all(engine)\n",
    "session = create_session(bind = engine, autocommit = False, autoflush = True)\n",
    "\n",
    "fpath='/ocean/eolson/MEOPAR/obs/ECBuoy/exportbuoy.txt'\n",
    "header=('dates','turb','temp','sal','O2')\n",
    "trDict={'dates':'dates','turb':'Turb_NTU','temp':'Tem','sal':'SR','O2':'O2'}\n",
    "adlValDict={'sourceFile':'exportbuoy.txt'}\n",
    "reqDta=('Turb_NTU',)\n",
    "\n",
    "def adFunECB(row):\n",
    "    idate=dutparse(row['dates'])#+dt.timedelta(hours=8)\n",
    "    row['Time_UTC']=idate.strftime(\"%Y-%m-%dT%H:%M:%S.%f\")[:-3]+'Z'\n",
    "    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 \n",
    "    del row['dates']\n",
    "    return row\n",
    "\n",
    "data2Tbl(fpath,session,FBuoyTBL,1,',',header,trDict,adlValDict,reqDta,adFunECB)\n",
    "\n",
    "fpath2='/ocean/eolson/MEOPAR/obs/ECBuoy/BuoyWQData2016-10-31toPresentToCSV.csv'\n",
    "header2=('dates','temp','specCond','ph','turb','O2Per','O2mlg')\n",
    "trDict2={'dates':'dates','turb':'Turb_NTU','temp':'Tem','O2mlg':'O2'}\n",
    "adlValDict2={'sourceFile':'BuoyWQData2016-10-31toPresentToCSV.csv'}\n",
    "\n",
    "data2Tbl(fpath2,session,FBuoyTBL,1,',',header2,trDict2,adlValDict2,reqDta,adFunECB)\n",
    "\n",
    "def adFunECBPac(row):\n",
    "    idate=dutparse(row['dates'])+dt.timedelta(hours=8) ## these times are UTC-8\n",
    "    row['Time_UTC']=idate.strftime(\"%Y-%m-%dT%H:%M:%S.%f\")[:-3]+'Z'\n",
    "    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 \n",
    "    del row['dates']\n",
    "    return row\n",
    "\n",
    "fpath3='/ocean/eolson/MEOPAR/obs/ECBuoy/FRBWQ_20170720to20190617NoOverlap.csv'\n",
    "header3=('dates','temp','specCond','ph','turb','O2mlg')\n",
    "trDict3={'dates':'dates','turb':'Turb_NTU','temp':'Tem','O2mlg':'O2'}\n",
    "adlValDict3={'sourceFile':'FRBWQ_20170720to20190617NoOverlap.csv'}\n",
    "data2Tbl(fpath3,session,FBuoyTBL,3,',',header3,trDict3,adlValDict3,reqDta,adFunECBPac)\n",
    "\n",
    "\n",
    "\n",
    "print([row for row in session.query(FBuoyTBL.ID,FBuoyTBL.Time_UTC,FBuoyTBL.DecDay,FBuoyTBL.Turb_NTU).limit(5)])\n",
    "session.close()\n",
    "engine.dispose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}