How to Select Literal Values in an SQLalchemy Query

How do I select literal values in an sqlalchemy query?

You'll need to use a literal_column, which looks a bit like this:

sqlalchemy.orm.Query(Item, sqlalchemy.sql.expression.literal_column("0"))

Beware that the text argument is inserted into the query without any transformation; this may expose you to a SQL Injection vulnerability if you accept values for the text parameter from outside your application. If that's something you need, you'll want to use bindparam, which is about as easy to use; but you will have to invent a name:

sqlalchemy.orm.Query(Item, sqlalchemy.sql.expression.bindparam("zero", 0))

What is the way to select a hard-coded value in a query?

Use the literal construct.

from sqlalchemy import literal

result = session.query(
table_a.c.col1, table_a.c.col2, literal('hardcoded value').label('col3')
)

how to create literal based query in sqlalchemy?

any literal value can be converted to an expression construct:

from sqlalchemy import literal_column, bindparam

# ? = ?, 1 will be bound
bindparam(1) == bindparam(1)

# " 1 = 1", literals rendered inline (no quoting is applied !!)
literal_column(str(1)) == literal_column(str(1))

Add own literal string as additional field in result of query - SQLAlchemy

The SQLAlchemy string types have operator overloads that allow you to treat them like you'd treat Python strings in this case (string concatenation), but produce SQL expressions:

session.query(
Table,
("http://example.com/page/" + Table.pagename).label("pageUrl"))

You can read more about SQLAlchemy's operator paradigm here: http://docs.sqlalchemy.org/en/latest/core/tutorial.html#operators

SQLAlchemy force in_() to use literals

You could use a text construct for your IN clause (mindful of sql injection risk):

from sqlalchemy_app import Base, Session, engine
import sqlalchemy as sa
from sqlalchemy.sql import text

class Something(Base):

id = sa.Column(sa.Integer, primary_key=True)

class SomethingElse(Base):

id = sa.Column(sa.Integer, primary_key=True)
something_id = sa.Column(sa.Integer, sa.ForeignKey("something.id"))

if __name__ == "__main__":
Base.metadata.drop_all(engine)
Base.metadata.create_all(engine)
s = Session()
in_clause = text("something.id IN (1, 2, 3)")
s.query(Something).join(SomethingElse).filter(in_clause).all()

Issues this query:

2019-08-10 14:23:32,329 INFO sqlalchemy.engine.base.Engine SELECT something.id AS something_id
FROM something INNER JOIN somethingelse ON something.id = somethingelse.something_id
WHERE something.id IN (1, 2, 3)

How can I add a constant-valued column to a SQLAlchemy query response?

Use literal:

.with_entities(VeryWideTable.a, VeryWideTable.b, literal("hello"))


Related Topics



Leave a reply



Submit