Forming Queries¶

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

Process¶

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 = 'snow:hackweek@db.snowexdata.org/snowex'

# 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 snowexsql.data 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 
#print(result)

This feels soooo limited :)

Questions

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?

Filtering¶

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()
print(result)

[<snowexsql.data.LayerData object at 0x7fdcf9614430>, <snowexsql.data.LayerData object at 0x7fdcf9614490>, <snowexsql.data.LayerData object at 0x7fdcf9614550>, <snowexsql.data.LayerData object at 0x7fdcf9614610>, <snowexsql.data.LayerData object at 0x7fdcf96146d0>]

Questions

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(LayerData.date).distinct().all()
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

Recap¶

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
session.close()

SnowEx Hackweek