Sqlalchemy in Clause

SQLAlchemy IN clause

How about

session.query(MyUserClass).filter(MyUserClass.id.in_((123,456))).all()

edit: Without the ORM, it would be

session.execute(
    select(
        [MyUserTable.c.id, MyUserTable.c.name], 
        MyUserTable.c.id.in_((123, 456))
    )
).fetchall()

select() takes two parameters, the first one is a list of fields to retrieve, the second one is the where condition. You can access all fields on a table object via the c (or columns) property.

Python, sqlalchemy, Select with WHERE IN clause

Try reading this part of the documentation -

http://docs.sqlalchemy.org/en/latest/core/sqlelement.html#sqlalchemy.sql.expression.ColumnElement.in_

Python SQLAlchemy ORM where clause with two column value

Use the and_ element to combine expressions in a clause.

import sqlalchemy as sa
row = session.execute(select(User).where(
    sa.and_(
        User.name == "squidward", 
        User.fullname == 'Squidward Tentacles'
    )
)

Maintain order when using sqlalchemy WHERE-clause and IN operator

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

SqlAlchemy puts SELECT Subquery in FROM clause instead of SELECT

If the sub-Select should be part of the SELECT, we can't use .subquery(), we need to use .label() instead.

Example here: https://stackoverflow.com/a/43655840/1005607

Thanks for the tip @Ilja Everilä

Sqlalchemy in_ subquery

Using a subquery:

sub_stmt = config.Session.query(ChildTable.person_id)
stmt = config.Session.query(Person).filter(~Person.id.in_(sub_stmt))
empty_persons = stmt.all()

emits the following sql:

SELECT person.id AS person_id, person.name AS person_name
FROM person
WHERE person.id NOT IN (SELECT foo.person_id AS foo_person_id
FROM foo)

Using a join:

stmt = config.Session.query(Person).outerjoin(ChildTable).filter(ChildTable.person_id.is_(None))
empty_persons = stmt.all()

emits the following sql:

SELECT person.id AS person_id, person.name AS person_name
FROM person LEFT OUTER JOIN foo ON person.id = foo.person_id
WHERE foo.person_id IS NULL

I think both achieve your desired result set.

what is flask-sqlalchemy where in clause query syntax?

Try .in_ clause in sqlalchemy

result = db_session.query(table_1).filter(table_1.id.in_((1,2,3,5))).all()

Note: here am assuming table_1 is your model

---

Related Topics