Earlier, textual SQL the usage of textual content() characteristic has been explained from the attitude of middle expression language of SQLAlchemy. Now we shall talk it from ORM factor of view.
Literal strings can be used flexibly with Query item with the aid of specifying their use with the textual content() assemble. Most relevant methods accept it. For instance, clear out() and order_by().
In the instance given underneath, the filter() technique translates the string “identification<three” to the WHERE identity<three
from sqlalchemy import text
for cust in session.query(Customers).filter(text("id<3")):
print(cust.name)
The uncooked SQL expression generated indicates conversion of filter to WHERE clause with the code illustrated under −
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
From our sample statistics in Customers table, rows may be decided on and call column will be printed as follows −
Ravi Kumar
Komal Pande
To specify bind parameters with string-based totally 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()
The effective SQL displayed on Python console will be as given under −
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 = ?
To use an entirely string-based statement, a textual content() construct representing a whole statement can be passed to from_statement().
session.query(Customers).from_statement(text("SELECT * FROM customers")).all()
The end result of above code can be a simple SELECT declaration as given underneath −
SELECT * FROM customers
Obviously, all facts in clients desk could be selected.
The textual content() assemble permits us to link its textual SQL to Core or ORM-mapped column expressions positionally. We can obtain this by way of 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()
The identity and call columns of all rows may be selected even though the SQLite engine executes following expression generated via above code suggests all columns in text() technique −
SELECT name, id, name, address, email FROM customers