Getting Started With Python and Postgresql

This python package (snowexsql) is python package that provides easy access to the entire postgresql database. Here is how you establish a connection:

# Import the functions we need from the python snowexsql library
from snowexsql.db import get_db

# If you followed the install instructions the database name should be snowex
db_name = 'db.snowexdata.org/snowex'

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

Using the Engine Object

The engine object returned from the get_db function is not used much in the snowexsql library. It does allow you to use typical SQL strings to interact with the database.

Note: Users who have used python + SQL before will likely be more familiar with this approach. Additionally those who don’t know python but know SQL will also be more comfortable here.

# First connect to the db
conn = engine.connect()

# Form a typical SQL query and use python to populate the table name
qry = "SELECT DISTINCT site_id FROM sites"

# Then we execute the sql command and collect the results
results = conn.execute(qry)

# Create a nice readable string to print the site names using python
out = ', '.join((row['site_id'] for row in results))

# Print it with a line return for readability
print(out + '\n')

# Close your connections to avoid hanging transactions
conn.close()

8C18, 1C1, Open 6, 2S4, 2S7, 6N36, Skyway Open, 6S22, 2S6, 9S40, 9N30, 1N1, 9N47, 1N3, 9N29, 8N45, 9N39, 8C35, 1C5, 6N31, 3S33, 5N24, 3N53, 1C7, 7N40, SNB 2, 8N55, County Line Open, 6N18, 5C20, 5N10, 8N52, 2N21, 1N5, 6S32, GML, 1S1, 2C3, 8S41, JPL 1, Forest 14, 6S53, C1, 8C11, Open, 2C13, Open Flat, Joe Wright, 8N54, 7S23, 6N16, 8C31, Open 2, 5S42, Mesa West Open, 8N34, Upper, 6C37, 5S49, 9S39, 1S17, 2S35, 1C8, BA Flux Tower, 8N9, FL2A, 5S31, 8N38, 6S26, Caples Lake, Open 4, 2S46, 8S28, 8C36, 5N15, 2C33, 6N46, 3S14, 6S15, 8N35, Skyway Tree, TLSFL2A, 2N13, 3N26, 1S8, Saddle, Banner Open, 3S47, 3S52, 4N2, 2S9, 9S51, Trench 13, 6C24, Panorama Dome, 5C27, Senator Beck, 2S25, Swamp Angel, FL1B, 9N42, 1N6, JPL 2, 2S11, 2N8, 9N59, 1N7, 8C25, 3S5, 8N58, 9C28, 2S10, 2S45, 5C21, 5S24, 7S50, 2N49, 8C22, Forest 13, 2N14, 9C17, 5N19, 2C9, 5N50, 2N4, Mores Creek Summit, LDP Tree, 1C14, 2C2, CUES, SNB 1, 8S18, Michigan River, 7C15, Irwin Barn, 2S20, 1S12, 6S44, 2S48, 9C19, 9N43, 9N56, 9N44, 8S30, 8C26, 7N57, 3S38, 9C16, 5N11, 6S34, 4N27, Forest 12, 5S21, 2C12, 2N12, 9C23, 1S2, 3N22, 2N48, 2S3, 5S29, 8N25, 2C4, Forest North, 2C6, Forest, 2S37, 2S16, HQ Met Station, 1S13, Alta Collins, 4C30, County Line Tree, Bogus Upper, 2S36, 6S19, 8N37, Forest Flat, Atwater, LDP Open, 9N28, 1N23, Gothic, 6N17, 8N51, 1N20, Forest South, 5N41, 8C32, 5S43, 8N33, 5N32, 6C10, Tower 4, Banner Snotel, 2S27, Mesa West Trees, 6C34, Aspen, 8C29

Using the Session Object

The session object allows a user to interact with the database in a pure python form. This approach is called Object Relational Mapping (ORM). This is important because its super handy when your are googling for help.

ORM maps the database tables and their columns to a python class and attributes. Here is how it works:

# Import the table classes from our data module which is where our ORM classes are defined
from  snowexsql.data import SiteData, PointData, LayerData, ImageData

# Form the query to receive all the site_id from the sites table
qry = session.query(SiteData.site_id).distinct()

# Execute the query and collect the result
results = qry.all()

# Form a nice string for readability
out = ', '.join([row[0] for row in list(results)])

# Print it with a line return for readability
print(out + '\n')

8C18, 1C1, Open 6, 2S4, 2S7, 6N36, Skyway Open, 6S22, 2S6, 9S40, 9N30, 1N1, 9N47, 1N3, 9N29, 8N45, 9N39, 8C35, 1C5, 6N31, 3S33, 5N24, 3N53, 1C7, 7N40, SNB 2, 8N55, County Line Open, 6N18, 5C20, 5N10, 8N52, 2N21, 1N5, 6S32, GML, 1S1, 2C3, 8S41, JPL 1, Forest 14, 6S53, C1, 8C11, Open, 2C13, Open Flat, Joe Wright, 8N54, 7S23, 6N16, 8C31, Open 2, 5S42, Mesa West Open, 8N34, Upper, 6C37, 5S49, 9S39, 1S17, 2S35, 1C8, BA Flux Tower, 8N9, FL2A, 5S31, 8N38, 6S26, Caples Lake, Open 4, 2S46, 8S28, 8C36, 5N15, 2C33, 6N46, 3S14, 6S15, 8N35, Skyway Tree, TLSFL2A, 2N13, 3N26, 1S8, Saddle, Banner Open, 3S47, 3S52, 4N2, 2S9, 9S51, Trench 13, 6C24, Panorama Dome, 5C27, Senator Beck, 2S25, Swamp Angel, FL1B, 9N42, 1N6, JPL 2, 2S11, 2N8, 9N59, 1N7, 8C25, 3S5, 8N58, 9C28, 2S10, 2S45, 5C21, 5S24, 7S50, 2N49, 8C22, Forest 13, 2N14, 9C17, 5N19, 2C9, 5N50, 2N4, Mores Creek Summit, LDP Tree, 1C14, 2C2, CUES, SNB 1, 8S18, Michigan River, 7C15, Irwin Barn, 2S20, 1S12, 6S44, 2S48, 9C19, 9N43, 9N56, 9N44, 8S30, 8C26, 7N57, 3S38, 9C16, 5N11, 6S34, 4N27, Forest 12, 5S21, 2C12, 2N12, 9C23, 1S2, 3N22, 2N48, 2S3, 5S29, 8N25, 2C4, Forest North, 2C6, Forest, 2S37, 2S16, HQ Met Station, 1S13, Alta Collins, 4C30, County Line Tree, Bogus Upper, 2S36, 6S19, 8N37, Forest Flat, Atwater, LDP Open, 9N28, 1N23, Gothic, 6N17, 8N51, 1N20, Forest South, 5N41, 8C32, 5S43, 8N33, 5N32, 6C10, Tower 4, Banner Snotel, 2S27, Mesa West Trees, 6C34, Aspen, 8C29

# Close your session to avoid hanging transactions
session.close()