#!/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[ ]: