How to properly bind pairs (array of tuples, multidimensional array) in SQLAlchemy?

Question

How to construct a mysql query:

SELECT *
FROM table
WHERE (key->>"$.k1", key->>"$.k2") IN ((1, "string1"), (2, "string2"))

with SQLAlchemy?

I tried to use text but with no luck:

select([table.c.value]).where(
    text('(key->>"$.k1", key->>"$.k2") IN :pairs)').bindparams(
        pairs=[(1, "string1"), (2, "string2")]
    )
)

This code produced:

SELECT *
FROM table
WHERE (key->>"$.k1", key->>"$.k2") IN NULL

Ignoring the `->>` operator, you'll find the answer from https://stackoverflow.com/questions/9140015/how-to-get-rows-which-match-a-list-of-3-tuples-conditions-with-sqlalchemy and https://stackoverflow.com/questions/948212/sqlalchemy-complex-in-clause-with-tuple-in-list-of-tuples — Ilja Everilä, Oct 08 '19 at 14:50

score 1 · Answer 1 · answered Oct 08 '19 at 15:12

1

The following code works:

select([table.c.value]).where(
    tuple_(
        text('key->>"$.k1"'),
        text('key->>"$.k2"'),
    ).in_([[1, 'string1'], [2, 'string']])
)

answered Oct 08 '19 at 15:12

1 Answers1