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

# In[1]:


# imports
from sqlalchemy import create_engine, Column, String, Integer, Numeric, MetaData, Table, type_coerce, case
from sqlalchemy.orm import mapper, create_session
from sqlalchemy.sql import and_, or_, not_, select
import sqlalchemy.types as types
from sqlalchemy.ext.automap import automap_base
import csv
import numpy as np
import matplotlib.pyplot as plt
import numbers
import re


# In[2]:


# create database file
engine = create_engine('sqlite:///test.sqlite')
metadata = MetaData(bind=engine)


# In[3]:


# custom types:
class forceNumeric(types.TypeDecorator):

    impl = types.Numeric

    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


# In[4]:


# create station table with basic variable names
# primary key is CAST #
tableStation = Table('stationTBL', metadata, Column('CastID', Integer, primary_key=True),
                     Column('NODC_Cruise_ID', String), Column('Originators_Station_ID', String),
                     Column('Originators_Cruise_ID', String), Column('Latitude', forceNumeric), 
                     Column('Longitude', forceNumeric), Column('Year', forceInt), 
                     Column('Month', forceNumeric), Column('Day', forceNumeric), 
                     Column('Time_hr', forceNumeric), Column('Country', String), 
                     Column('Accession_Number', forceInt), Column('Platform', String), 
                     Column('Institute', String), Column('Secchi_disk_depth_m', forceNumeric))
tableStation.create()


# In[5]:


class Stations(object): pass
mapper(Stations, tableStation)


# In[6]:


mydict={'NODC_Cruise_ID':'test1','Originators_Cruise_ID':'teststring', 'Latitude': 23.5, 'Longitude': -145.143, 'testwrong':55}


# In[7]:


mystation=Stations(mydict)


# In[8]:


mydict


# In[9]:


stmt = tableStation.insert().values(mydict)


# In[ ]:





# In[10]:


conn = engine.connect()


# In[11]:


conn.execute(stmt)


# In[ ]: