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 have a pandas dataframe that needs to be inserted.
• Each day this dataframe is updated.
• Using the to_sql pandas method, sqlalchemy models are useless (I’m using automap_base instead of declarative_base)
• I’m unsure of how to get a usable list/table of results from a query.

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.