CODEWITHSUNDEEP
pythonsqlalchemy
Wayne Werner
Wayne Werner

Reputation: 51807

How do I create a literal where column in SQLAlchemy (core)?

I have a stored procedure in PostgreSQL that I can query using SQLAlchemy like this:

import sqlalchemy as sa

engine = sa.create_engine(...)

some_argument = 42
some_value = 99
for result in engine.execute(sa.select(['col_name'])
                              .select_from(sa.func.this_is_my_func_name(some_agument))
                              .where('this part does not work')):
     print(result)

I want to know how I can use something like col_name == some_value in my code.

Here are some things that don't work:

.where('col_name' == some_value)
.where(sa.column('col_name') == some_value)

I haven't found anything else that does precisely what I'm looking for. I can do:

.where(sa.text('col_name = :col_value'))

and then engine.execute(query, col_value=some_value), but that seems like it's not quite the right thing to do. At that point it seems like I should just do:

sql = sa.text('''
    SELECT
        col_name
    FROM
        this_is_my_func_name(:argument)
    WHERE
        col_name = :value
''')
engine.execute(sql, argument=some_argument, value=some_value)

Is there a better way to accomplish what I have in mind, or should I just go full-bore sqlalchemy.text?

Upvotes: 1

Views: 443

Answers (1)

Wayne Werner
Wayne Werner

Reputation: 51807

Turns out I was totally wrong.

The problem was actually that

sa.select(['col_name'])

Isn't exactly what we want. Sqlalchemy guessed that it was a column name, but it wasn't sure, so it was giving me a bit of a warning. It needed to be:

sa.select([sa.column('col_name')])

Then it will stop giving warnings. But in either case,

.where(sa.column('col_name') == value)

is the correct way to do it.

Upvotes: 1

Related Questions