2019年3月23日 星期六

[ Python 文章收集 ] SQLAlchemy Core - Using Functions (7)

Source From Here 
Using Functions 
Standard SQL has recommended many functions which are implemented by most dialects. They return a single value based on the arguments passed to it. Some SQL functions take columns as arguments whereas some are generic. The func keyword in SQLAlchemy API is used to generate these functions. 

In SQL, now() is a generic function. Following statements renders the now() function using func: 
>>> from sqlalchemy.sql import func 
>>> result = conn.execute(select([func.now()])) 
2019-03-24 08:26:56,296 INFO sqlalchemy.engine.base.Engine SELECT now() AS now_1 
2019-03-24 08:26:56,296 INFO sqlalchemy.engine.base.Engine {}
 
>>> print(result.fetchone()) 
(datetime.datetime(2019, 3, 24, 0, 26, 56, 297582, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=0, name=None)),)

On the other hand, count() function which returns number of rows selected from a table, is rendered by following usage of func: 
  1. from sqlalchemy.sql import func  
  2. result = conn.execute(select([func.count(students.c.id)]))  
  3. print (result.fetchone())  
From the above code, count of number of rows in students table will be fetched. 

Some built-in SQL functions are demonstrated using Employee table with following data: 
  1. testdb=# SELECT * FROM employee;  
  2. id |   name    | marks  
  3. ----+-----------+-------  
  4.   1 | Mamal     |    56  
  5.   2 | Fernandez |    85  
  6.   3 | Sunil     |    62  
  7.   4 | Bhaskar   |    76  
  8. (4 rows)  
The max() function is implemented by following usage of func from SQLAlchemy which will result in 85, the total maximum marks obtained: 
  1. from sqlalchemy.sql import func  
  2. result = conn.execute(select([func.min(employee.c.marks)]))  
  3. print (result.fetchone())  
So, the AVG() function can also be implemented by using the below code: 
  1. from sqlalchemy.sql import func  
  2. result = conn.execute(select([func.avg(employee.c.marks)]))  
  3. print (result.fetchone())  
Functions are normally used in the columns clause of a select statement. They can also be given label as well as a type. A label to function allows the result to be targeted in a result row based on a string name, and a type is required when you need result-set processing to occur: 
>>> from sqlalchemy.sql import func 
>>> result = conn.execute(select([func.max(students.c.lastname).label('Name')])) 
2019-03-24 08:55:23,187 INFO sqlalchemy.engine.base.Engine SELECT max(students.lastname) AS "Name" 
FROM students 
2019-03-24 08:55:23,188 INFO sqlalchemy.engine.base.Engine {}
 
>>> print(result.fetchone()) 
('Sattar',)

Using Set Operations 
Set operations such as UNION and INTERSECT are supported by standard SQL and most of its dialect. SQLAlchemy implements them with the help of following functions. 

union() 
While combining results of two or more SELECT statements, UNION eliminates duplicates from the resultset. The number of columns and datatype must be same in both the tables. 

The union() function returns a CompoundSelect object from multiple tables. Following example demonstrates its use: 
- demo13.py 
  1. #!/usr/bin/env python36  
  2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union  
  3.   
  4. db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
  5. engine = create_engine(db_string, echo = True)  
  6. meta = MetaData(bind=engine)  
  7. meta.reflect()  
  8. addresses = meta.tables['addresses']  
  9. conn = engine.connect()  
  10.   
  11. u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com')),  
  12.           addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))  
  13.   
  14. result = conn.execute(u)  
  15. print('Total {:,d} row(s):'.format(result.rowcount))  
  16. for r in result.fetchall():  
  17.     print("\t{}".format(r))  
Output: 
  1. ...  
  2. 2019-03-24 09:07:28,603 INFO sqlalchemy.engine.base.Engine SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
  3. FROM addresses  
  4. WHERE addresses.email_add LIKE %(email_add_1)s UNION SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
  5. FROM addresses  
  6. WHERE addresses.email_add LIKE %(email_add_2)s  
  7. 2019-03-24 09:07:28,603 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
  8. Total 8 row(s):  
  9.         (1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')  
  10.         (2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')  
  11.         (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')  
  12.         (4, 5, 'MG Road Bangaluru', 'as@yahoo.com')  
  13.         (6, 1, 'Shivajinagar Pune', 'ravi@gmail.com')  
  14.         (7, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')  
  15.         (8, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')  
  16.         (9, 5, 'MG Road Bangaluru', 'as@yahoo.com')  
union_all() 
UNION ALL operation cannot remove the duplicates and cannot sort the data in the resultset. For example, in above query, UNION is replaced by UNION ALL to see the effect: 
  1. u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')),  
  2.           addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))  
Output: 
  1. ...  
  2. 2019-03-24 09:16:57,046 INFO sqlalchemy.engine.base.Engine SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
  3. FROM addresses  
  4. WHERE addresses.email_add LIKE %(email_add_1)s UNION ALL SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
  5. FROM addresses  
  6. WHERE addresses.email_add LIKE %(email_add_2)s  
  7. 2019-03-24 09:16:57,046 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
  8. Total 8 row(s):  
  9.         (1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')  
  10.         (2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')  
  11.         (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')  
  12.         (6, 1, 'Shivajinagar Pune', 'ravi@gmail.com')  
  13.         (7, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')  
  14.         (8, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')  
  15.         (4, 5, 'MG Road Bangaluru', 'as@yahoo.com')  
  16.         (9, 5, 'MG Road Bangaluru', 'as@yahoo.com')  
If you want to get an unique email address result, try below code: 
- demo14.py 
  1. #!/usr/bin/env python36  
  2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union, union_all  
  3. from sqlalchemy.sql import select  
  4.   
  5. db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
  6. engine = create_engine(db_string, echo = True)  
  7. meta = MetaData(bind=engine)  
  8. meta.reflect()  
  9. addresses = meta.tables['addresses']  
  10. conn = engine.connect()  
  11.   
  12. u = union(select([addresses.c.email_add]).where(addresses.c.email_add.like('%@gmail.com')),  
  13.           select([addresses.c.email_add]).where(addresses.c.email_add.like('%@yahoo.com')))  
  14.   
  15. u.order_by(u.c.email_add)  
  16.   
  17. result = conn.execute(u)  
  18. print('Total {:,d} row(s):'.format(result.rowcount))  
  19. for r in result.fetchall():  
  20.     print("\t{}".format(r))  
Output: 
  1. 2019-03-24 09:35:40,909 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add  
  2. FROM addresses  
  3. WHERE addresses.email_add LIKE %(email_add_1)s UNION SELECT addresses.email_add  
  4. FROM addresses  
  5. WHERE addresses.email_add LIKE %(email_add_2)s  
  6. 2019-03-24 09:35:40,909 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
  7. Total 4 row(s):  
  8.         ('as@yahoo.com',)  
  9.         ('kapoor@gmail.com',)  
  10.         ('komal@gmail.com',)  
  11.         ('ravi@gmail.com',)  
except_() 
The SQL EXCEPT clause/operator is used to combine two SELECT statements and return rows from the first SELECT statement that are not returned by the second SELECT statement. The except_() function generates a SELECT expression with EXCEPT clause. 

In the following example, the except_() function returns only those records from addresses table that have ‘gmail.com’ in email_add field but excludes those which have ‘Pune’ as part of postal_add field: 
  1. u = except_(select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.email_add.like('%@gmail.com')),  
  2.             select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.postal_add.like('%Pune')))  
Result of the above code is the following SQL expression: 
  1. ...  
  2. 2019-03-24 09:42:55,989 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add, addresses.postal_add  
  3. FROM addresses  
  4. WHERE addresses.email_add LIKE %(email_add_1)s EXCEPT SELECT addresses.email_add, addresses.postal_add  
  5. FROM addresses  
  6. WHERE addresses.postal_add LIKE %(postal_add_1)s  
  7. 2019-03-24 09:42:55,989 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'postal_add_1': '%Pune'}  
  8. Total 2 row(s):  
  9.         ('kapoor@gmail.com', 'ChurchGate Mumbai')  
  10.         ('komal@gmail.com', 'Jubilee Hills Hyderabad')  
intersect() 
Using INTERSECT operator, SQL displays common rows from both the SELECT statements. The intersect() function implements this behaviour. 

In following examples, two SELECT constructs are parameters to intersect() function. One returns rows containing ‘gmail.com’ as part of email_add column, and other returns rows having ‘Pune’ as part of postal_add column. The result will be common rows from both resultsets: 
- demo16.py 
  1. #!/usr/bin/env python36  
  2. from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union, union_all, except_, intersect  
  3. from sqlalchemy.sql import select  
  4.   
  5. db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
  6. engine = create_engine(db_string, echo = True)  
  7. meta = MetaData(bind=engine)  
  8. meta.reflect()  
  9. addresses = meta.tables['addresses']  
  10. conn = engine.connect()  
  11.   
  12. u = intersect(select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.email_add.like('%@gmail.com')),  
  13.               select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.postal_add.like('%Pune')))  
  14.   
  15.   
  16. result = conn.execute(u)  
  17. print('Total {:,d} row(s):'.format(result.rowcount))  
  18. for r in result.fetchall():  
  19.     print("\t{}".format(r))  
Output: 
  1. ...  
  2. 2019-03-24 09:48:22,347 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add, addresses.postal_add  
  3. FROM addresses  
  4. WHERE addresses.email_add LIKE %(email_add_1)s INTERSECT SELECT addresses.email_add, addresses.postal_add  
  5. FROM addresses  
  6. WHERE addresses.postal_add LIKE %(postal_add_1)s  
  7. 2019-03-24 09:48:22,347 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'postal_add_1': '%Pune'}  
  8. Total 1 row(s):  
  9.         ('ravi@gmail.com', 'Shivajinagar Pune')  


[ Python 文章收集 ] SQLAlchemy Core - Using Functions (7)

Source From   Here   Using Functions   Standard SQL has recommended many functions which are implemented by most dialects. They return a si...