0

I'm doing a Python's function with SQLAlchemy, i'm looking for a easy method of "select". The input variables are the name of the table and kwargs that they will be used to the condition "where".

For example, the query code would be like:

select * from Pruebas where Codigo='123' and Nombre='Leche'

And the Python's function would be like:

my_stmt('Pruebas',Codigo='123',Nombre='leche')

The structure table is:

      **Pruebas**
Codigo---Nombre ---Valor
'123' ---'Leche'---'5000'

The principal code is here:

import sqlalchemy
def my_stmt(TableName,**kwargs):
    table = sqlalchemy.Table(TableName, metadata, autoload=True, schema=esquema)
    stmt = lambda_stmt(lambda: select(table))
    for key, value in kwargs.items():
        col = column(key)        
        stmt = stmt.add_criteria(lambda s: s.where(col == value))
        #stmt += lambda s: s.where(col == value) ##On this way i obtain the same answer
    return stmt

The intake of the funtion would be like:

x=my_stmt('pruebas',codigo='123',nombre='leche')

with engine.connect() as connection:
    results = connection.execute(x).fetchall()

print(results)

The log console show me the next information:

SELECT dbo.pruebas.codigo, dbo.pruebas.nombre, dbo.pruebas.valor
FROM dbo.pruebas
WHERE codigo = ? AND nombre = ?
('leche', 'leche')

That's the problem, 'leche' for both conditions and i obtain a wrong answer. The print show me:

[]

The right answer that i looking for is:

[('123', 'leche', '5000')]

And the log should show me:

('123', 'leche')

I will appreciate any recommendation and if you know one more easy way to make what i need, i will be very thank you. One more thing, sorry for my English~

Victor Nino Vargas
  • 1
  • 1
  • The problem, as explained in the linked duplicates, is that the values bound to the lamdbas are those from the last iteration of the loop. But you could avoid the lambdas with `stmt = select(table).where(*[getattr(table.c, k) == v for k, v in kwargs.items()])` – snakecharmerb Feb 09 '22 at 14:58
  • See also https://docs.python.org/3/faq/programming.html#why-do-lambdas-defined-in-a-loop-with-different-values-all-return-the-same-result – snakecharmerb Feb 09 '22 at 15:00
  • 1
    @snakecharmerb Thank you so much, that works! the documentation is great too. The right way with the lambda funtion is: `stmt = stmt.add_criteria(lambda bound_stmt=stmt, bound_value=value: bound_stmt.where(col == bound_value))` – Victor Nino Vargas Feb 09 '22 at 22:00

0 Answers0