2019年6月13日 星期四

[ Python 文章收集 ] SQLAlchemy ORM - Textual SQL

Source From Here 
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<
  1. from sqlalchemy import text  
  2. for cust in session.query(Customers).filter(text("id<3")):  
  3.    print(cust.name)  
The raw SQL expression generated shows conversion of filter to WHERE clause with the code illustrated below: 
  1. SELECT customers.id AS customers_id, customers.name AS customers_name, customers.address AS customers_address, customers.email AS customers_email  
  2. FROM customers  
  3. WHERE id < 3  
From our sample data in Customers table, two rows will be selected and name column will be printed as follows: 
Ravi Kumar
Komal Pande

To specify bind parameters with string-based SQL, use a colon,and to specify the values, use the params() method: 
  1. cust = session.query(Customers).filter(text("id = :value")).params(value = 1).one()  
The effective SQL displayed on Python console will be as given below: 
2019-06-13 20:21:28,937 INFO sqlalchemy.engine.base.Engine 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 = %(value)s

2019-06-13 20:21:28,937 INFO sqlalchemy.engine.base.Engine {'value': 1}

To use an entirely string-based statement, a text() construct representing a complete statement can be passed to from_statement()
  1. session.query(Customers).from_statement(text("SELECT * FROM customers")).all()  
The result of above code will be a basic SELECT statement as given below: 
  1. SELECT * FROM customers  
Obviously, all records in customers table will be selected. 


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: 
  1. stmt = text("SELECT name, id, name, address, email FROM customers")  
  2. stmt = stmt.columns(Customers.id, Customers.name)  
  3. session.query(Customers.id, Customers.name).from_statement(stmt).all()  
The id and name columns of all rows will be selected even though the SQLite engine executes following expression generated by above code shows all columns in text() method: 
  1. SELECT name, id, name, address, email FROM customers  

Supplement 
SQLAlchemy Doc - Query API

沒有留言:

張貼留言

[Git 常見問題] error: The following untracked working tree files would be overwritten by merge

  Source From  Here 方案1: // x -----删除忽略文件已经对 git 来说不识别的文件 // d -----删除未被添加到 git 的路径中的文件 // f -----强制运行 #   git clean -d -fx 方案2: 今天在服务器上  gi...