Antony Petrocelli Antony Petrocelli - 4 years ago 504
Python Question

Sqlalchemy dynamically build or_ conjunction and_

I'm trying to generate a dynamically conjunction with or_ and and_ but not the SQL query generates or.

The number of operators or_ and and_ varies for each query.

query:

q = se.query(Publicacion.update_time).join(Categoria).order_by(Publicacion.update_time.desc())
q = q.filter(and_(Publicacion.id_group_id == '103738479786979'))
q = q.filter(and_(Publicacion.id_categoria_id == 1),)
q = q.filter(and_(Categoria.estatus == True),)
q = q.filter(or_(Publicacion.message.ilike('%Obsequios%')),)
q = q.filter(or_(Publicacion.message.ilike('%jose%')))
q = q.filter(or_(Publicacion.message.ilike('%compu%')))


out sql:

SELECT showgroups_publicacion.update_time AS showgroups_publicacion_update_time
FROM showgroups_publicacion JOIN showgroups_categoria ON showgroups_categoria.id = showgroups_publicacion.id_categoria_id
WHERE showgroups_publicacion.id_group_id = %(id_group_id_1)s AND showgroups_publicacion.id_categoria_id = %(id_categoria_id_1)s AND showgroups_categoria.estatus = true AND showgroups_publicacion.message ILIKE %(message_1)s AND showgroups_publicacion.message ILIKE %(message_2)s AND showgroups_publicacion.message ILIKE %(message_3)s ORDER BY showgroups_publicacion.update_time DESC


At the end I solved this way, if there is a more elegant, share it:

Y = and_ (column1 == 1)
Y = and_ (column2 == 'SQL',Y)

O = and_ (column1 == 2)
O = and_ (column2 == 'Python',O)

session.query(Model).Filter(or_(O,Y))

Answer Source

You are misunderstanding the use of or_, and_ and filter. Look at the tutorial for a correct example.

You should specify more than a clause inside and_ (and or_), like you do in every programming language. Instead of writing this:

q = q.filter(and_(Publicacion.id_group_id == '103738479786979'))
q = q.filter(and_(Publicacion.id_categoria_id == 1),)
q = q.filter(and_(Categoria.estatus == True),)

You should have written this:

q = q.filter(and_(
    Publicacion.id_group_id == '103738479786979',
    Publicacion.id_categoria_id == 1,
    Categoria.estatus == True,
))

The same holds for or_.

Now I don't know what you had in mind, so I can't tell you how the two and_ and or_ clauses should be combined in your case. Probably you wanted to do this:

q = q.filter(
    Publicacion.id_group_id == '103738479786979',
    Publicacion.id_categoria_id == 1,
    Categoria.estatus == True,
    or_(
        Publicacion.message.ilike('%Obsequios%'),
        Publicacion.message.ilike('%jose%'),
        Publicacion.message.ilike('%compu%'),
    ),
)

What you were doing by using multiple filter with single-condition clauses was essentially this:

  • An and_/or_ with a single condition is superfluous, so that filter(and_(something)) is equivalent to filter(or_(something)), which is equivalent to filter(something).
  • You were issuing multiple filter, that can be combined into only one. Your code is 100% equivalent to:

    q = q.filter(
        Publicacion.id_group_id == '103738479786979',
        Publicacion.id_categoria_id == 1,
        Categoria.estatus == True,
        Publicacion.message.ilike('%Obsequios%'),
        Publicacion.message.ilike('%jose%'),
        Publicacion.message.ilike('%compu%'),
    )
    

    The behavior of filter() is to AND every condition. In fact, your query contained only AND.

    By the way, note that filter(and_(a, b, c, ...)) is equivalent to filter(a, b, c, ...).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download