In [1]:
import datetime
from pycds import *
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy import and_, or_
We need to do a few things to set up our database session, and then the session object is used to make all our queries
In [2]:
connection_string = 'postgresql+psycopg2://hiebert@atlas.pcic/crmp'
engine = create_engine(connection_string)
Session = sessionmaker(bind=engine)
session = Session()
Query and print the names of all networks in the database
In [3]:
for net, in session.query(Network.name).order_by(Network.name):
print(net)
Print the first 10 stations in the EC network when ordered ascencding
In [4]:
for station in session.query(Station).filter(Network.name=='EC').order_by(Station.native_id.asc())[:10]:
print(station.native_id)
Count the observations for station 1010066 in the EC network We must use explicit query joins here or sqlalchemy creates ridculous crossjoins and subqueries
In [5]:
query = session.query(Obs).join(History).join(Station).join(Network).filter(Network.name=='EC').filter(Station.native_id=='1010066')
print(query.count())
Find all history_ids for station 1010066 in the EC network using filter criteria
In [6]:
for hist, in session.query(History.id).filter(History.station_id==Station.id).filter(and_(Station.native_id=='1010066', Network.name=='EC')):
print(hist)
Find all history ids representing EC native_id 1010066 using explicit joins
In [7]:
for hist, in session.query(History.id).join(Station).join(Network).filter(Network.name=='EC').filter(Station.native_id=='1010066'):
print(hist)
Count the number of observations from this year
In [8]:
this_year = datetime.datetime(2015, 1, 1)
q = session.query(Obs).filter(Obs.time >= this_year)
print(q.count())
You can access related objects through object attributes. For example, each Network object has a list of its stations in the stations attribute and a list of each available network variable in the variables object. These are Python native objects, all with the proper typing.
In [9]:
q = session.query(Network)
net = q.first()
net.name
Out[9]:
In [10]:
net.stations
Out[10]:
In [11]:
for s in net.stations:
print(s.id, s.native_id)
Note that the station id, which is an integer in the database, is automatically an integer when we access it as well. Likewise for the native_id which are free form strings in the database and here in Python.
In [12]:
net.variables
Out[12]:
In [13]:
for v in net.variables:
print(v.standard_name, v.unit)
In [ ]: