SQLAlchemy: Querying

July 17, 2017

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

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.