Maintain order when using sqlalchemy WHERE-clause and IN operator

Question

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 code:

from sqlalchemy import (
    Column,
    Integer,
    MetaData,
    String,
    Table,
    create_engine,
    insert,
    select,
)

engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)

metadata = MetaData()

# Creating the table
tickers = Table(
    "tickers",
    metadata,
    Column("id", Integer, primary_key=True, autoincrement=True),
    Column("ticker", String, nullable=False),
    Column("description", String(), nullable=False),
)

metadata.create_all(engine)

# Populating the table
with engine.connect() as conn:
    result = conn.execute(
        insert(tickers),
        [
            {"ticker": "GDBR30", "description": "30YR"},
            {"ticker": "GDBR10", "description": "10YR"},
            {"ticker": "GDBR5", "description": "5YR"},
            {"ticker": "GDBR2", "description": "2YR"},
        ],
    )
    conn.commit()

I need to filter tickers for some values:

search_list = ["GDBR10", "GDBR5", "GDBR30"]

records = conn.execute(
    select(tickers.c.description).where((tickers.c.ticker).in_(search_list))
)

print(records.fetchall())

# Result
# [('30YR',), ('10YR',), ('5YR',)]

However, I need the resulting list of tuples ordered in the way search_list has been ordered. That is, I need the following result:

print(records.fetchall())

# Expected result
# [('10YR',), ('5YR',), ('30YR',)]

Using SQLite, you could create a cte with two columns (id and ticker). Applying the following code will lead to the expected result (see Maintain order when using SQLite WHERE-clause and IN operator). Unfortunately, I am not able to transfer the SQLite solution to sqlalchemy.

WITH cte(id, ticker) AS (VALUES (1, 'GDBR10'), (2, 'GDBR5'), (3, 'GDBR30'))
SELECT t.* 
FROM tbl t INNER JOIN cte c
ON c.ticker = t.ticker
ORDER BY c.id

Suppose, I have search_list_tuple as folllows, how am I suppose to code the sqlalchemy query?

search_list_tuple = [(1, 'GDBR10'), (2, 'GDBR5'), (3, 'GDBR30')]

van · Accepted Answer · 2021-05-07T05:28:23.057

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 in enumerate(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())

Maintain order when using sqlalchemy WHERE-clause and IN operator

1 Answers1

Linked