[ Python 文章收集 ] SQLAlchemy Core - Using Textual SQL & Alias (3)

Source From Here 
Using Textual SQL 
SQLAlchemy lets you just use strings, for those cases when the SQL is already known and there isn’t a strong need for the statement to support dynamic features. The text() construct is used to compose a textual statement that is passed to the database mostly unchanged. 

It constructs a new TextClause, representing a textual SQL string directly as shown in the below code: 
- demo4.py 

view plaincopy to clipboardprint?

1. from sqlalchemy import text  
2. from sqlalchemy import create_engine, MetaData  
3.   
4. db_string = "postgresql://postgres:john7810@localhost/testdb"  
5. engine = create_engine(db_string, echo = True)  
6. meta = MetaData(bind=engine)  
7. conn = engine.connect()  
8.   
9. t = text("SELECT * FROM students")  
10. result = conn.execute(t)  

The text() function requires Bound parameters in the named colon format. They are consistent regardless of database backend. To send values in for the parameters, we pass them into the execute() method as additional arguments. The following example uses bound parameters in textual SQL: 

>>> from sqlalchemy.sql import text
>>> s = text('SELECT name, lastname FROM students WHERE name BETWEEN :x and :y')
>>> str(s)
'SELECT name, lastname FROM students WHERE name BETWEEN :x and :y'
>>> result = conn.execute(s, x = 'A', y = 'L').fetchall()
2019-03-06 20:51:35,147 INFO sqlalchemy.engine.base.Engine SELECT name, lastname FROM students WHERE name BETWEEN %(x)s and %(y)s
2019-03-06 20:51:35,147 INFO sqlalchemy.engine.base.Engine {'x': 'A', 'y': 'L'}
>>> result
[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]

The text() construct supports pre-established bound values using the TextClause.bindparams() method. The parameters can also be explicitly typed as follows: 

>>> stmt = text('SELECT * FROM students WHERE name BETWEEN :x AND :y')
>>> stmt = stmt.bindparams(x='A', y='l')
>>> str(stmt)
'SELECT * FROM students WHERE name BETWEEN :x AND :y'
>>> result = conn.execute(stmt).fetchall()
2019-03-06 20:59:58,207 INFO sqlalchemy.engine.base.Engine SELECT * FROM students WHERE name BETWEEN %(x)s AND %(y)s
2019-03-06 20:59:58,207 INFO sqlalchemy.engine.base.Engine {'x': 'A', 'y': 'l'}
>>> result
[(5, 'Komal', 'Bhandari'), (6, 'Abdul', 'Sattar')]

The text() function also be produces fragments of SQL within a select() object that accepts text() objects as an arguments. The “geometry” of the statement is provided by select() construct , and the textual content by text()construct. We can build a statement without the need to refer to any pre-established Table metadata: 

>>> from sqlalchemy.sql import select
>>> s = select([text("students.name, students.lastname from students")]).where(text("students.name between :x and :y"))
>>> str(s)
'SELECT students.name, students.lastname from students \nWHERE students.name between :x and :y'
>>> result = conn.execute(s, x = 'A', y = 'L').fetchall()
2019-03-06 21:09:58,695 INFO sqlalchemy.engine.base.Engine SELECT students.name, students.lastname from students
WHERE students.name between %(x)s and %(y)s
2019-03-06 21:09:58,695 INFO sqlalchemy.engine.base.Engine {'x': 'A', 'y': 'L'}
>>> result
[('Komal', 'Bhandari'), ('Abdul', 'Sattar')]

You can also use and_() function to combine multiple conditions in WHERE clause created with the help of text() function: 

view plaincopy to clipboardprint?

1. from sqlalchemy import and_  
2. from sqlalchemy.sql import select  
3. s = select([text("* from students")]) \  
4. .where(  
5.    and_(  
6.       text("students.name between :x and :y"),  
7.       text("students.id>2")  
8.    )  
9. )  
10. conn.execute(s, x = 'A', y = 'L').fetchall()  

Using Aliases 
The alias in SQL corresponds to a “renamed” version of a table or SELECT statement, which occurs anytime you say “SELECT * FROM table1 AS a”. The AS creates a new name for the table. Aliases allow any table or subquery to be referenced by a unique name. In case of a table, this allows the same table to be named in the FROM clause multiple times. It provides a parent name for the columns represented by the statement, allowing them to be referenced relative to this name. 

In SQLAlchemy, any Table, select() construct, or other selectable object can be turned into an alias using the From Clause.alias() method, which produces an Alias construct. The alias() function in sqlalchemy.sql module represents an alias, as typically applied to any table or sub-select within a SQL statement using the AS keyword: 
- demo5.py 

view plaincopy to clipboardprint?

1. from sqlalchemy.sql import alias, select  
2. from sqlalchemy import text  
3. from sqlalchemy import create_engine, MetaData  
4.   
5. db_string = "postgresql://postgres:john7810@localhost/testdb"  
6. engine = create_engine(db_string, echo = True)  
7. meta = MetaData(bind=engine)  
8. meta.reflect()  
9. conn = engine.connect()  
10. students = meta.tables['students']  
11.   
12. st = students.alias("a")  
13.   
14. s = select([st]).where(st.c.id > 2)  
15. result = conn.execute(s).fetchall()  
16. print("SELECT result={}".format(result))  

Output: 

SELECT result=[(3, 'Ravi', 'Kapoor'), (4, 'Rajiv', 'Khanna'), (5, 'Komal', 'Bhandari'), (6, 'Abdul', 'Sattar'), (7, 'Priya', 'Rajhans'), (8 , 'Lee', 'John')]

Supplement 
* SQLAlchemy document - SQL Expression Language Tutorial 
* SQLAlchemy document - Reflecting All Tables at Once