#!/usr/bin/env python
# coding: utf-8

# In[1]:


# imports
from sqlalchemy import create_engine, Column, String, Integer, Float, Date, MetaData, Table, type_coerce
from sqlalchemy.orm import mapper, create_session
import csv
from sqlalchemy import case
import numpy as np
from sqlalchemy.ext.automap import automap_base
import matplotlib.pyplot as plt
import sqlalchemy.types as types
import numbers
from sqlalchemy.sql import and_, or_, not_
from sqlalchemy.sql import select
import datetime as dt
get_ipython().run_line_magic('matplotlib', 'inline')


# In[2]:


# definitions
file1='/ocean/eolson/MEOPAR/obs/BioChem/BioChem_Query_1610/BioChem_Query_1610_Data.csv'


# In[3]:


engine = create_engine('sqlite:////ocean/eolson/MEOPAR/obs/BioChem/BioChemQueryDB.sqlite')


# In[4]:


table = None
metadata = MetaData(bind=engine)


# In[5]:


class forceNumeric(types.TypeDecorator):

    impl = types.Float

    def process_bind_param(self, value, dialect):
        try:
            int(float(value))
        except:
            value = None
        return value

class forceInt(types.TypeDecorator):
    
    impl = types.Integer
    def process_bind_param(self, value, dialect):
        try:
            int(value)
        except:
            value = None
        return value
    
class toDate(types.TypeDecorator):
    
    impl = types.Date
    def process_bind_param(self, value, dialect):
        try:
            #monDict={'Jan':1,'Feb':2,'Mar':3,'Apr':4, 'May':5, 'Jun':6,
            #         'Jul':7,'Aug':8,'Sep':9,'Oct':10,'Nov':11,'Dec':12}
            monDict={'Jan':'01','Feb':'02','Mar':'03','Apr':'04', 'May':'05', 'Jun':'06',
                     'Jul':'07','Aug':'08','Sep':'09','Oct':'10','Nov':'11','Dec':'12'}
            year=value[-4:]
            month=monDict[value[-8:-5]]
            day=value[0:2]
            value=dt.date(int(year),int(month), int(day))
        except:
            value = None
        return value

class toHour(types.TypeDecorator):
    
    impl = types.Float
    def process_bind_param(self, value, dialect):
        try:
            mm=float(value[-2:])
            hh=float(value[0:-2])
            value=hh+mm/60.0
        except:
            value = None
        return value


# In[6]:


def coltype(ikey):
    typedict = {
        'DATA_CENTER': String(),
        'DESCRIPTOR-NAME': String(),
        'AREA_NAME': String(),
        'COLLECTOR_STATION_NAME': String(),
        'START_DATE': toDate(),
        'START_TIME': toHour(),
        'COLLECTOR_DEPLOYMENT_ID': String(),
        'GEAR_MODEL (GEAR_SIZE)': String(),
        'Silicate_datatype': String(),
        'Phosphate_datatype': String(),
        'Nitrate_datatype': String(),
    }
    return typedict.get(ikey, forceNumeric())
    


# In[7]:


with open(file1) as f:
    cf = csv.DictReader(f, delimiter=',')
    for row in cf:
        if table is None:
            table = Table('profiles', metadata, Column('id', Integer, primary_key=True),
                *(Column(rowname, coltype(rowname)) for rowname in row.keys()))
            table.create()
        table.insert().values(**row).execute()
class Profs(object): pass
mapper(Profs, table)


# In[8]:


Base = automap_base()
# reflect the tables in salish.sqlite:
Base.prepare(engine, reflect=True)
# mapped classes have been created
Profs=Base.classes.profiles
session = create_session(bind = engine, autocommit = False, autoflush = True)


# In[9]:


print(row.keys())


# In[11]:


data=session.query(Profs.START_DATE,Profs.START_TIME, Profs.START_DEPTH,Profs.START_LAT,Profs.START_LON, 
                  Profs.Nitrate, Profs.Silicate, Profs.Phosphate
                  ).filter(or_(
                           Profs.Nitrate != None,
                           Profs.Phosphate != None,
                           Profs.Silicate != None,
                          )).all()
for row in data:
    print(row)


# In[12]:


session.close()
engine.dispose()


# In[ ]: