import sqlalchemy
from sqlalchemy import create_engine, Column, String, Integer, Numeric, MetaData, Table, ForeignKey
from sqlalchemy.orm import create_session, relationship
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import and_, or_, not_, func
import csv
import matplotlib.pyplot as plt
basepath = '/Users/eolson/WorkUBC/SWC/SQLAlchemy/'
dbname = 'fakeData'
engine = create_engine('sqlite:///' + basepath + dbname + '.sqlite')
tables are associated with Classes
Base=declarative_base() # useful for creating your tables, defining their structure
# We will use this first table as an intermediate step to import the flat spreadsheet
class LoadDataTBL(Base):
__table__ = Table('LoadDataTBL', Base.metadata,
Column('ID', Integer, primary_key=True),
Column('Station', Integer),
Column('Lat', Numeric),
Column('Lon', Numeric),
Column('Date', String),
Column('Depth', Numeric),
Column('Temperature', Numeric),
Column('Salinity', Numeric))
# Station Table
class StationTBL(Base):
__table__=Table('StationTBL', Base.metadata,
Column('ID', Integer, primary_key=True),
Column('Lat', Numeric),
Column('Lon', Numeric),
Column('Date', String))
# Profile Table
class ProfileTBL(Base):
__table__=Table('ProfileTBL', Base.metadata,
Column('ID', Integer, primary_key=True),
Column('StationTBLID', Integer, ForeignKey('StationTBL.ID')),
Column('Depth', Numeric),
Column('Temperature', Numeric),
Column('Salinity', Numeric))
Base.metadata.create_all(engine)
session = create_session(bind = engine, autocommit = False, autoflush = True)
f=open(basepath+'MadeUpData.csv','r')
cf = csv.DictReader(f, delimiter=',')
i=0
for row in cf:
i+=1
if i<3: print(row) # print first two rows
session.execute(LoadDataTBL.__table__.insert().values(**row))
f.close()
session.commit()
# query LoadDataTBL to return columns that belong in ProfileTBL
# ID, Station, Depth, Temperature Salinity
q0=session.query(LoadDataTBL.ID,LoadDataTBL.Station,LoadDataTBL.Depth,LoadDataTBL.Temperature,
LoadDataTBL.Salinity)
for row in q0.all():
print(*row)
for row in q0.all():
idict={}
idict['ID']=row[0]
idict['StationTBLID']=row[1]
idict['Depth']=row[2]
idict['Temperature']=row[3]
idict['Salinity']=row[4]
# enter in new record in Profile table:
session.execute(ProfileTBL.__table__.insert().values(**idict))
session.commit()
q1=session.query(LoadDataTBL.Station,LoadDataTBL.Lat,LoadDataTBL.Lon,
LoadDataTBL.Date).group_by(LoadDataTBL.Station)
for row in q1.all():
print(*row)
for Station, Lat, Lon, Date in q1.all():
idict={}
idict['ID']=Station
idict['Lat']=Lat
idict['Lon']=Lon
idict['Date']=Date
# enter in new record in Profile table:
session.execute(StationTBL.__table__.insert().values(**idict))
session.commit()
session.close()
LoadDataTBL.__table__.drop(engine)
engine.dispose()
# automatically reflect database structure:
Base = automap_base()
engine = create_engine('sqlite:///' + basepath + dbname + '.sqlite', echo = False)
# reflect the tables:
Base.prepare(engine, reflect=True)
# mapped classes have been created
# assign table classes to short variables for convenience:
StationTBL=Base.classes.StationTBL
ProfileTBL=Base.classes.ProfileTBL
session = create_session(bind = engine, autocommit = False, autoflush = True)
q2=session.query(StationTBL.ID,StationTBL.Lat).filter(StationTBL.Lat>48.4)
for row in q2.all():
print(row)
q3=session.query(ProfileTBL.Temperature,ProfileTBL.Salinity).filter(ProfileTBL.Salinity<33)
for row in q3.all():
print(row)
q4=session.query(StationTBL.Lat,StationTBL.Lon,ProfileTBL.Temperature).\
filter(ProfileTBL.Salinity<33)
for row in q4:
print(row)
q4=session.query(StationTBL.Lat,StationTBL.Lon,ProfileTBL.Salinity).\
select_from(ProfileTBL).join(StationTBL,StationTBL.ID==ProfileTBL.StationTBLID).\
filter(ProfileTBL.Salinity<33)
for row in q4:
print(row)
q5=session.query(func.max(ProfileTBL.Salinity))
print(q5.one())
q6=session.query(StationTBL.ID,func.avg(ProfileTBL.Temperature),func.count()).select_from(
ProfileTBL).join(StationTBL,StationTBL.ID==ProfileTBL.StationTBLID).\
group_by(StationTBL.ID)
for row in q6:
print(row)
qP1=session.query(ProfileTBL).subquery()
qP2=session.query(ProfileTBL).subquery()
qMatch=session.query(qP1.c.ID,qP2.c.ID,qP1.c.Temperature,qP2.c.Temperature,
qP1.c.Salinity,qP2.c.Salinity).filter(
qP1.c.ID!=qP2.c.ID,qP1.c.Temperature==qP2.c.Temperature,qP1.c.Depth==qP2.c.Depth)
for row in qMatch.all():
print(*row)
q0=session.query(ProfileTBL.StationTBLID,func.count().label('N')).filter(ProfileTBL.Salinity<29).\
group_by(ProfileTBL.StationTBLID)
for row in q0.all():
print(row)
qsub=q0.subquery()
q=session.query(StationTBL.ID,StationTBL.Lat,StationTBL.Lon).\
select_from(StationTBL).join(qsub,qsub.c.StationTBLID==StationTBL.ID).\
filter(qsub.c.N>1)
for row in q.all():
print(row)
session.close()
engine.dispose()