Difficulty passing column in filter_by as parameter in Python3 function with Sqlalchemt

Question

I’m using Sqlalchemy to work with database tables. I am creating classes for the proper tables, where I have as methods of these classes, Insert, filter_all(), etc.

I’m using the following class with your methods:

class Veiculos(base):
    __tablename__ = "veiculos"

    id = Column(Integer, primary_key=True, autoincrement=True)
    marca =  Column(String(20))
    modelo = Column(String(20))
    ano = Column(Integer)

    def __init__(self, str_marca="None", str_modelo="None", int_ano="None"):
        """
        :param str_marca: (str).
        :param str_modelo: (str).
        :param  int_ano: (int).
        """
        self.marca =  str_marca
        self.modelo = str_modelo
        self.ano = int_ano

    def __repr__(self):
        return "< Veículo {}, {}, {} >".format(self.marca, self.modelo, self.ano)

    def insert(self):
        session.add(self)
        session.commit()

    @classmethod
    def find_by_column(self, session, str_column, var_search):
        search = var_search
        coluna = str_column
        return  session.query(self).filter_by(self.coluna=search).all()

    @classmethod
    def select_all(self, session):        
        return session.query(self)

My problem is in this method:

def find_by_column(self, session, str_column, var_search):
            search = var_search
            coluna = str_column
            return  session.query(self).filter_by(self.coluna=search).all()

Where I would like to pass the column name and the value to search everything in the function parameters.

What am I doing wrong?

Answer 1

I imagine this .filter_by(self.coluna=search) is causing the following error:

SyntaxError: keyword can't be an expression

In case it seems to me that you want to pass the value of the key, which is a "dynamic parameter", in this case you can use the unpack:

• https://docs.python.org/3/tutorial/controlflow.html#unpacking-argument-lists

simplest example to understand:

def filter_by(**kwargs):
    print('keys', kwargs.keys())
    print('values', kwargs.values())

filter_by(teste=1)

minha_coluna = 'teste2'

filter_by(**{ minha_coluna: 'Hello' })

The name of the argument passed thus can be dynamic, see an example similar to your class:

class Teste:
    coluna = 'teste'

    def filter_by(self, **kwargs):
        print('keys', kwargs.keys())
        print('values', kwargs.values())
        print('-----')

    def exec(self):
        #manualmente
        self.filter_by(teste=1)

        #dinamicamente
        self.filter_by(**{self.coluna: 1})

foo = Teste()
foo.exec()

So to solve it probably should stay that way:

session.query(self).filter_by(**{self.coluna: search}).all()

I couldn’t test it, but from the documentation https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.filter_by the function even uses **kwargs (probably wouldn’t have been able to do otherwise), so if you miss something let me know.

Online example to test on repl it.