Forming Queries

Get Familiar with querying the database. BUT don’t forget your cheat sheets!


Getting Connected

Getting connected to the database is easiest done using the snowexsql library function get_db

# Import the function to get connect to the db
from snowexsql.db import get_db

# This is what you will use for all of hackweek to access the db
db_name = ''

# Using the function get_db, we receive 2 ways to interact with the database
engine, session = get_db(db_name)

Importing the tables classes

These are critical for build queries. You will need at least one of these every query since they reflect the data were interested in.

from import SiteData, PointData, LayerData, ImageData

Query Time!

We build queries in python using session.query(). Whatever we put inside of the query parentheses is what we will get back in the results!

# Lets grab a single row from the points table
qry = session.query(PointData).limit(1)

# Execute that query!
result = qry.all()

Pause for moment and consider what is in result….

Is it:

A. a single value
B. a bunch of values
C. an object
D. a row of values
# uncomment the line below and print out the results 

This feels soooo limited :)


  • What happens if we changed the number in the limit? What will we get back?

  • Where are our column names?

  • What if I only wanted a single column and not a whole row?


The database had a silly number of records, and asking for all of them will crash your computer.

So let talk about using .filter()

All queries can be reduced by applying session.query(__).filter(__) and a lot can go into the parentheses. This is where your cheat sheet will come in handy.

# Its convenient to store a query like the following 
qry = session.query(LayerData)

# Then filter on it to just density profiles
qry = qry.filter(LayerData.type == 'density')

# protect ourselves from a lot of data
qry = qry.limit(5)

result = qry.all()
[< object at 0x7fdcf9614430>, < object at 0x7fdcf9614490>, < object at 0x7fdcf9614550>, < object at 0x7fdcf9614610>, < object at 0x7fdcf96146d0>]


  • What happens if I filter on a qry that’s been filtered?

  • What happens if I just want a single column/attribute back? How do I do that?

How do I know what to filter on?

Queries and .distinct()!

# Get the unique datanames in the table
results = session.query(LayerData.type).distinct().all()
print('Available types = {}'.format(', '.join([r[0] for r in results])))

# Get the unique instrument in the table
results = session.query(LayerData.instrument).distinct().all()
print('\nAvailable Instruments = {}'.format(', '.join([str(r[0]) for r in results])))

# Get the unique dates in the table
results = session.query(
print('\nAvailable Dates = {}'.format(', '.join([str(r[0]) for r in results])))

# Get the unique surveyors in the table
results = session.query(LayerData.surveyors).distinct().all()
print('\nAvailable surveyors = {}'.format(', '.join([str(r[0]) for r in results])))
Available types = sample_signal, force, grain_size, density, reflectance, permittivity, lwc_vol, manual_wetness, equivalent_diameter, specific_surface_area, grain_type, temperature, hand_hardness
Available Instruments = IS3-SP-15-01US, IRIS, snowmicropen, None, IS3-SP-11-01F
Available Dates = 2020-02-04, 2020-02-11, 2020-02-12, 2020-01-30, 2020-01-27, 2020-02-10, 2020-02-01, 2020-02-08, 2020-02-09, 2020-02-06, 2020-01-31, 2020-02-02, 2020-02-03, 2020-01-29, 2020-01-28, 2020-02-05
Available surveyors = None, Juha Lemmetyinen, Kate Hale, Carrie Vuyovich, Céline Vargel, Juha Lemmetyinen & Ioanna Merkouriadi, Carrie Vuyovich & Juha Lemmetyinen, Kehan Yang


You just explored using the session object to form queries and compounding filters results with it

You should know:

  • How to build queries using filtering

  • How to isolate column data

  • Determine what values to filter on

If you don’t feel comfortable with these, you are probably not alone, let’s discuss it!

# Close out the session to avoid hanging transactions