CODEWITHSUNDEEP
pythonsqlalchemymixins
Thornhale
Thornhale

Reputation: 2376

How do I use Mixins with SQLAlchemy to simplify querying and filtering operation?

Assume the following setup:

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class MyClass(Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)

The normal paradigm to query the DB with SQLAlchemy is to do the following:

Session = sessionmaker()
engine = 'some_db_location_string'
session = Session(bind=engine)
session.query(MyClass).filter(MyClass.id == 1).first()

Suppose, I want to simplify the query to the following:

MyClass(s).filter(MyClass.id == 1).first()

OR MyClass(s).filter(id == 1).first()

How would I do that? My first attempt at that to use a model Mixin class failed. This is what I tried:

class ModelMixins(object)
    def __init__(self, session):
        self.session = session

    def filter(self, *args):
        self.session.query(self).filter(*args)

# Redefine MyClass to use the above class
class MyClass(ModelMixins, Base):
    id = Column(Integer, primary_key=True)
    name = Column(String)

The main failure seems to be that I can't quite transfer the expression 'MyClass.id == 1' to the actual filter function that is part of the session object.

Folks may ask why would I want to do:

MyClass(s).filter(id == 1).first()

I have seen something similar like this used before and thought that the syntax becomes so much cleaner I can achieve this. I wanted to replicate this but have not been able to. Being able to do something like this:

def get_stuff(some_id):
    with session_scope() as s:
        rec = MyClass(s).filter(MyClass.id== some_id').first()
    if rec:
        return rec.name
    else:
        return None

...seems to be the cleanest way of doing things. For one, session management is kept separate. Secondly, the query itself is simplified. Having a Mixin class like this would allow me to add the filter functionality to any number of classes...So can someone help in this regard?

Upvotes: 1

Views: 3874

Answers (2)

Luke Gary
Luke Gary

Reputation: 75

Ive been using this mixin to good success. Most likely not the most efficient thing in the world and I am no expert. I define a date_created column for every table

class QueryBuilder:
"""
This class describes a query builer.
"""
q_debug = False
def query_from_dict(self, db_session: Session, **q_params: dict):
    """
    Creates a query.

    :param      db_session:  The database session
    :type       db_session:  Session
    :param      q_params:    The quarter parameters
    :type       q_params:    dictionary
    """
    q_base = db_session.query(type(self))
    for param, value in q_params.items():
        if param == 'start_date':
            q_base = q_base.filter(
                type(self).__dict__.get('date_created') >= value
            )
        elif param == 'end_date':
            q_base = q_base.filter(
                type(self).__dict__.get('date_created') <= value
            )
        elif 'like' in param:
            param = param.replace('_like', '')
            member = type(self).__dict__.get(param)
            if member:
                q_base = q_base.filter(member.ilike(f'%{value}%'))
        else:
            q_base = q_base.filter(
                type(self).__dict__.get(param) == value
            )
    if self.q_debug:
        print(q_base)
    return q_base

Upvotes: 0

Nathan Vērzemnieks
Nathan Vērzemnieks

Reputation: 5603

session.query takes a class; you're giving it self, which is an instance. Replace your filter method with:

def filter(self, *args):
    return session.query(self.__class__).filter(*args)

and at least this much works:

In [45]: MyClass(session).filter(MyClass.id==1)
Out[45]: <sqlalchemy.orm.query.Query at 0x10e0bbe80>

The generated SQL looks right, too (newlines added for clarity):

In [57]: str(MyClass(session).filter(MyClass.id==1))
Out[57]: 'SELECT "MyClass".id AS "MyClass_id", "MyClass".name AS "MyClass_name" 
FROM "MyClass" 
WHERE "MyClass".id = ?'

No guarantees there won't be oddities; I've never tried anything like this before.

Upvotes: 1

Related Questions