[ Python 文章收集 ] SQLAlchemy Core - Using Joins & Conjunctions (6)

Source From Here 
Using Joins 
Effect of joining is achieved by just placing two tables in either the columns clause or the Where clause of the select() construct. Now we use the join() and outerjoin() methods. The join() method returns a join object from one table object to another: 

view plaincopy to clipboardprint?

1. join(right, onclause = None, isouter = False, full = False)  

The functions of the parameters mentioned in the above code are as follows: 

* right − the right side of the join; this is any Table object
* onclause − a SQL expression representing the ON clause of the join. If left at None, it attempts to join the two tables based on a foreign key relationship
* isouter − if True, renders a LEFT OUTER JOIN, instead of JOIN
* full − if True, renders a FULL OUTER JOIN, instead of LEFT OUTER JOIN

For example, following use of join() method will automatically result in join based on the foreign key: 

>>> str(students.join(addresses))
'students JOIN addresses ON students.id = addresses.st_id'

You can explicitly mention joining criteria as follows: 

>>> j = students.join(addresses, students.c.id == addresses.c.st_id)
>>> str(j)
'students JOIN addresses ON students.id = addresses.st_id'

If we now build the below select construct using this join as: 

>>> from sqlalchemy.sql import select
>>> stmt = select([students]).select_from(j)
>>> str(stmt)
'SELECT students.id, students.name, students.lastname \nFROM students JOIN addresses ON students.id = addresses.st_id'

If this statement is executed using the connection representing engine, data belonging to selected columns will be displayed. The complete code is as follows: 
- demo10.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python36  
2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
3. from sqlalchemy import join  
4. from sqlalchemy.sql import select  
5.   
6. db_string = "postgresql://postgres:<password≷@localhost/testdb"  
7. engine = create_engine(db_string, echo = True)  
8. meta = MetaData(bind=engine)  
9. meta.reflect()  
10.   
11. conn = engine.connect()  
12. students = meta.tables['students']  
13. addresses = meta.tables['addresses']  
14.   
15. j = students.join(addresses, students.c.id == addresses.c.st_id)  
16. stmt = select([students, addresses.c.email_add]).select_from(j)  
17. result = conn.execute(stmt)  
18. for row in result.fetchall():  
19.     print(row)  

Output: 

...
(1, 'Ravi', 'Kapoor', 'ravi@gmail.com')
(1, 'Ravi', 'Kapoor', 'kapoor@gmail.com')
(3, 'Komal', 'Bhandari', 'komal@gmail.com')
(5, 'Priya', 'Rajhans', 'as@yahoo.com')
(2, 'Rajiv', 'Khanna', 'admin@khanna.com')

Using Conjunctions 
Conjunctions are functions in SQLAlchemy module that implement relational operators used in WHERE clause of SQL expressions. The operators AND, OR, NOT, etc., are used to form a compound expression combining two individual logical expressions. A simple example of using AND in SELECT statement is as follows: 

view plaincopy to clipboardprint?

1. SELECT * from EMPLOYEE WHERE salary>10000 AND age>30  

SQLAlchemy functions and_(), or_() and not_() respectively implement AND, OR and NOT operators. 

and_() function 
It produces a conjunction of expressions joined by AND. An example is given below for better understanding: 

view plaincopy to clipboardprint?

1. from sqlalchemy import and_  
2.   
3. print(  
4.    and_(  
5.       students.c.name == 'Ravi',  
6.       students.c.id <3  
7.    )  
8. )  

This translates to: 

students.name = :name_1 AND students.id < :id_1

To use and_() in a select() construct on a students table, use the following line of code: 

>>> from sqlalchemy import and_, select
>>> stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3 font="">
>>> str(stmt)
'SELECT students.id, students.name, students.lastname \nFROM students \nWHERE students.name = %(name_1)s AND students.id < %(id_1)s'

The complete code that displays output of the above SELECT query is as follows: 
- demo11.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python36  
2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
3. from sqlalchemy import join, and_, or_  
4. from sqlalchemy.sql import select  
5.   
6. db_string = "postgresql://postgres:<password≷@localhost/testdb"  
7. engine = create_engine(db_string, echo = True)  
8. meta = MetaData(bind=engine)  
9. meta.reflect()  
10.   
11. conn = engine.connect()  
12. students = meta.tables['students']  
13. addresses = meta.tables['addresses']  
14.   
15. stmt = select([students]).where(and_(students.c.name == 'Ravi', students.c.id <3))  
16. result = conn.execute(stmt)  
17. for row in result:  
18.     print(row)  

or_() function 
It produces conjunction of expressions joined by OR. We shall replace the stmt object in the above example with the following one using or_(): 

>>> stmt = select([students]).where(or_(students.c.name == 'Ravi', students.c.id <3 font="">
>>> print(stmt)
SELECT students.id, students.name, students.lastname
FROM students
WHERE students.name = %(name_1)s OR students.id < %(id_1)s

asc() function 
It produces an ascending ORDER BY clause. The function takes the column to apply the function as a parameter: 

view plaincopy to clipboardprint?

1. from sqlalchemy import asc  
2.   
3. stmt = select([students]).order_by(asc(students.c.name))  

The statement implements following SQL expression: 

view plaincopy to clipboardprint?

1. SELECT students.id,   
2.    students.name,   
3.    students.lastname  
4. FROM students   
5. ORDER BY students.name ASC  

Following code lists out all records in students table in ascending order of name column: 
- demo12.py 

view plaincopy to clipboardprint?

1. #!/usr/bin/env python36  
2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String  
3. from sqlalchemy import join, and_, or_, asc  
4. from sqlalchemy.sql import select  
5.   
6. db_string = "postgresql://postgres:@localhost/testdb"  
7. engine = create_engine(db_string, echo = True)  
8. meta = MetaData(bind=engine)  
9. meta.reflect()  
10.   
11. conn = engine.connect()  
12. students = meta.tables['students']  
13. addresses = meta.tables['addresses']  
14.   
15. from sqlalchemy import asc  
16. stmt = select([students]).order_by(asc(students.c.name))  
17. result = conn.execute(stmt)  
18.   
19. for row in result:  
20.        print (row)  

Output: 

(4, 'Abdul', 'Sattar')
(3, 'Komal', 'Bhandari')
(5, 'Priya', 'Rajhans')
(2, 'Rajiv', 'Khanna')
(1, 'Ravi', 'Kapoor')

desc() function 
Similarly desc() function produces descending ORDER BY clause as follows: 

view plaincopy to clipboardprint?

1. from sqlalchemy import desc  
2. stmt = select([students]).order_by(desc(students.c.lastname))  

The equivalent SQL expression is − 

view plaincopy to clipboardprint?

1. SELECT students.id,   
2.    students.name,   
3.    students.lastname  
4. FROM students   
5. ORDER BY students.lastname DESC  

between() function 
It produces a BETWEEN predicate clause. This is generally used to validate if value of a certain column falls between a range. For example, following code selects rows for which id column is between 2 and 4: 

view plaincopy to clipboardprint?

1. from sqlalchemy import between  
2. stmt = select([students]).where(between(students.c.id,2,4))  
3. print (stmt)  

The resulting SQL expression resembles − 

view plaincopy to clipboardprint?

1. SELECT students.id,   
2.    students.name,   
3.    students.lastname  
4. FROM students  
5. WHERE students.id   
6. BETWEEN :id_1 AND :id_2  

and the result is as follows: 

(2, 'Rajiv', 'Khanna')
(3, 'Komal', 'Bhandari')
(4, 'Abdul', 'Sattar')

Supplement 
* SQLAlchemy Cheetsheet 
* Tutorialspoint - PostgreSQL - JOINS