2019年3月21日 星期四

[ 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: 
  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 
  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: 
  1. SELECT * from EMPLOYEE WHERE salary>10000 AND age>30  
SQLAlchemy functions and_(), or_() and not_() respectively implement ANDOR and NOT operators. 

and_() function 
It produces a conjunction of expressions joined by AND. An example is given below for better understanding: 
  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 
  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: 
  1. from sqlalchemy import asc  
  2.   
  3. stmt = select([students]).order_by(asc(students.c.name))  
The statement implements following SQL expression: 
  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 
  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: 
  1. from sqlalchemy import desc  
  2. stmt = select([students]).order_by(desc(students.c.lastname))  
The equivalent SQL expression is − 
  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: 
  1. from sqlalchemy import between  
  2. stmt = select([students]).where(between(students.c.id,2,4))  
  3. print (stmt)  
The resulting SQL expression resembles − 
  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

沒有留言:

張貼留言

[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...