Maintain Order When Using Sqlalchemy Where-clause And In Operator

Consider the following database table: ID ticker description 1 GDBR30 30YR 2 GDBR10 10YR 3 GDBR5 5YR 4 GDBR2 2YR It can be replicated with this piece of

Solution 1:

Below works and is actually equivalent to the VALUES (...) on sqlite albeit somewhat more verbose:

# construct the CTE
sub_queries = [
    select(literal(i).label("id"), literal(v).label("ticker"))
    for i, v inenumerate(search_list)
]
cte = union_all(*sub_queries).cte("cte")

# desired query
records = conn.execute(
    select(tickers.c.description)
    .join(cte, cte.c.ticker == tickers.c.ticker)
    .order_by(cte.c.id)
)
print(records.fetchall())
# [('10YR',), ('5YR',), ('30YR',)]

---

Below is using the values() contruct, but unfortunately the resulting query fails on SQLite, but it works perfectly on postgresql:

cte =select(
    values(
        column("id", Integer), column("ticker", String), name="subq"
    ).data(list(zip(range(len(search_list)), search_list)))
).cte("cte")

qq = (
    select(tickers.c.description)
    .join(cte, cte.c.ticker == tickers.c.ticker)
    .order_by(cte.c.id)
)
records = conn.execute(qq)
print(records.fetchall())