Preface
Earlier, textual SQL using text() function has been explained from the perspective of core expression language of SQLAlchemy. Now we shall discuss it from ORM point of view.
Textual SQL
Literal strings can be used flexibly with Query object by specifying their use with the text() construct. Most applicable methods accept it. For example, filter() and order_by(). In the example given below, the filter() method translates the string “id<3” to the WHERE id<3
- from sqlalchemy import text
- for cust in session.query(Customers).filter(text("id<3")):
- print(cust.name)
- SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email
- FROM customers
- WHERE id < 3
To specify bind parameters with string-based SQL, use a colon,and to specify the values, use the params() method:
- cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()
To use an entirely string-based statement, a text() construct representing a complete statement can be passed to from_statement():
- session.query(Customers).from_statement(text("SELECT * FROM customers")).all()
- SELECT * FROM customers
The text() construct allows us to link its textual SQL to Core or ORM-mapped column expressions positionally. We can achieve this by passing column expressions as positional arguments to the TextClause.columns() method:
- stmt = text("SELECT name, id, name, address, email FROM customers")
- stmt = stmt.columns(Customers.id, Customers.name)
- session.query(Customers.id, Customers.name).from_statement(stmt).all()
- SELECT name, id, name, address, email FROM customers
Supplement
* SQLAlchemy Doc - Query API
沒有留言:
張貼留言