Notebook

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)

Out[5]:

<Mapper at 0x7f12ba703e80; Stations>

In [6]:

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

In [7]:

mystation=Stations(mydict)

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-7-064b12b40a85> in <module>()
----> 1 mystation=Stations(mydict)

TypeError: __init__() takes 1 positional argument but 2 were given

In [8]:

mydict

Out[8]:

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

In [9]:

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

In [ ]:

In [10]:

conn = engine.connect()

In [11]:

conn.execute(stmt)

---------------------------------------------------------------------------
CompileError                              Traceback (most recent call last)
<ipython-input-11-47492503e0bd> in <module>()
----> 1 conn.execute(stmt)

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/engine/base.py in execute(self, object, *multiparams, **params)
    912                 type(object))
    913         else:
--> 914             return meth(self, multiparams, params)
    915 
    916     def _execute_function(self, func, multiparams, params):

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params)
    321 
    322     def _execute_on_connection(self, connection, multiparams, params):
--> 323         return connection._execute_clauseelement(self, multiparams, params)
    324 
    325     def unique_params(self, *optionaldict, **kwargs):

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params)
   1001             compiled_sql = elem.compile(
   1002                 dialect=dialect, column_keys=keys,
-> 1003                 inline=len(distilled_params) > 1)
   1004 
   1005         ret = self._execute_context(

<string> in <lambda>(self, bind, dialect, **kw)

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/elements.py in compile(self, default, bind, dialect, **kw)
    492             else:
    493                 dialect = default.DefaultDialect()
--> 494         return self._compiler(dialect, bind=bind, **kw)
    495 
    496     def _compiler(self, dialect, **kw):

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/elements.py in _compiler(self, dialect, **kw)
    498         Dialect."""
    499 
--> 500         return dialect.statement_compiler(dialect, self, **kw)
    501 
    502     def __str__(self):

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py in __init__(self, dialect, statement, column_keys, inline, **kwargs)
    390         # dialect.label_length or dialect.max_identifier_length
    391         self.truncated_names = {}
--> 392         Compiled.__init__(self, dialect, statement, **kwargs)
    393 
    394         if self.positional and dialect.paramstyle == 'numeric':

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py in __init__(self, dialect, statement, bind, compile_kwargs)
    188             self.statement = statement
    189             self.can_execute = statement.supports_execution
--> 190             self.string = self.process(self.statement, **compile_kwargs)
    191 
    192     @util.deprecated("0.7", ":class:`.Compiled` objects now compile "

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py in process(self, obj, **kwargs)
    211 
    212     def process(self, obj, **kwargs):
--> 213         return obj._compiler_dispatch(self, **kwargs)
    214 
    215     def __str__(self):

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/visitors.py in _compiler_dispatch(self, visitor, **kw)
     79                     raise exc.UnsupportedCompilationError(visitor, cls)
     80                 else:
---> 81                     return meth(self, **kw)
     82         else:
     83             # The optimization opportunity is lost for this case because the

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/compiler.py in visit_insert(self, insert_stmt, **kw)
   1817 
   1818         self.isinsert = True
-> 1819         crud_params = crud._get_crud_params(self, insert_stmt, **kw)
   1820 
   1821         if not crud_params and \

/home/eolson/anaconda3/lib/python3.4/site-packages/sqlalchemy/sql/crud.py in _get_crud_params(compiler, stmt, **kw)
    107             raise exc.CompileError(
    108                 "Unconsumed column names: %s" %
--> 109                 (", ".join("%s" % c for c in check))
    110             )
    111 

CompileError: Unconsumed column names: testwrong

In [ ]: