SQLAlchemy: Querying

July 17, 2017

Context
I need to query my database through SQLAlchemy to generate reports and add results to the website.

Postgres and SQLAlchemy

Querying in sqlalchemy is taking some time to grok.

Most of the examples showed something like this:

'''
Tables already created using sqlalchemy models and filled with data.
'''

from sqlalchemy import *

r = session.query(Table).filter(Table.name=="test").first()

This doesn’t really work for me.

I wanted to write a reusable method to specifically query inventory tables.
So perhaps specifying some of the inputs and leaving the rest to the use case.

I’m not sure if that’s the best way to do it, or just use the sqlalchemy query method.

# create a query object using session (or session_scope)

def querystuff(self, *args, **kwargs):
    

    with self.session_scope() as s:
        query = s.query(Table)
        res = query.filter(Table.id == 1)
        
        for r in res:
            print r.name, r.id, r.location
        
    # ALLOW QUERYING WITH ARGS AND KWARGS SOMEHOW

I think I’ll figure out my most immediate use case for the query function and then write a method that fits.
Also, I think instead of using the to_sql pandas method, I need to insert rows one by one and build a master list of colors.

The to_sql method seems sloppy for my use case.