<jupyter_start><jupyter_text>Forming QueriesGet Familiar with querying the database. BUT don't forget your [cheat sheets](https://snowexsql.readthedocs.io/en/latest/cheat_sheet.html)!  Process Getting ConnectedGetting connected to the database is easiest done using the snowexsql library function [`get_db`](https://snowexsql.readthedocs.io/en/latest/snowexsql.htmlsnowexsql.db.get_db)<jupyter_code># 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@192.168.3.1144/snowex'

# Using the function get_db, we receive 2 ways to interact with the database
engine, session = get_db(db_name)<jupyter_output><empty_output><jupyter_text>Importing the tables classesThese are critical for build queries. You will need at least one of these every query since they reflect the data were interested in.<jupyter_code>from snowexsql.data import SiteData, PointData, LayerData, ImageData<jupyter_output><empty_output><jupyter_text>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!<jupyter_code># Lets grab a single row from the points table
qry = session.query(PointData).limit(1)

# Execute that query!
result = qry.all()<jupyter_output><empty_output><jupyter_text>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<jupyter_code># uncomment the line below and print out the results 
#print(result)<jupyter_output><empty_output><jupyter_text>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?  FilteringThe 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.<jupyter_code># 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)<jupyter_output><empty_output><jupyter_text>**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()`!<jupyter_code># 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])))<jupyter_output><empty_output><jupyter_text>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 onIf you don't feel comfortable with these, you are probably not alone, let's discuss it!<jupyter_code># Close out the session to avoid hanging transactions
session.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.09820051413881747, 'fraction_numerical': 0.004627249357326478, 'mean_word_length': 5.517587939698492, 'pattern_counts': {'":': 0, '<': 29, '<?xml version=': 0, '>': 29, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '241310', 'license': 'MIT', 'n_tokens_mistral': 1172, 'n_tokens_neox': 1137, 'n_words': 464, 'path': 'book/tutorials/database/3_forming_queries.ipynb', 'repo_name': 'hpmarshall/website', 'size': '3889'}