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
How to Add a Custom Loglevel to Python's Logging Facility
Compare Two Files Report Difference in Python
Appending a Dictionary to a List in a Loop
Adding Headers to Requests Module
CSV New-Line Character Seen in Unquoted Field Error
Difference(S) Between Merge() and Concat() in Pandas
How to One Hot Encode Variant Length Features
Segmenting License Plate Characters
Define a Method Outside of Class Definition
How to Assign a Variable in an If Condition, and Then Return It
Return Multiple Columns from Pandas Apply()
Open Cv Error: (-215) Scn == 3 || Scn == 4 in Function Cvtcolor
Python: Slicing a Multi-Dimensional Array
Is There a Difference Between Continue and Pass in a for Loop in Python
Printing a List Separated with Commas, Without a Trailing Comma
What Do All the Distributions Available in Scipy.Stats Look Like