程式扎記

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:

view plaincopy to clipboardprint?
from sqlalchemy.sql import func  
result = conn.execute(select([func.count(students.c.id)]))  
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:

view plaincopy to clipboardprint?
testdb=# SELECT * FROM employee;  
id |   name    | marks  
----+-----------+-------  
  1 | Mamal     |    56  
  2 | Fernandez |    85  
  3 | Sunil     |    62  
  4 | Bhaskar   |    76  
(4 rows)  

The max() function is implemented by following usage of func from SQLAlchemy which will result in 85, the total maximum marks obtained:

view plaincopy to clipboardprint?
from sqlalchemy.sql import func  
result = conn.execute(select([func.min(employee.c.marks)]))  
print (result.fetchone())  

So, the AVG() function can also be implemented by using the below code:

view plaincopy to clipboardprint?
from sqlalchemy.sql import func  
result = conn.execute(select([func.avg(employee.c.marks)]))  
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

view plaincopy to clipboardprint?
#!/usr/bin/env python36  
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union  
  
db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
engine = create_engine(db_string, echo = True)  
meta = MetaData(bind=engine)  
meta.reflect()  
addresses = meta.tables['addresses']  
conn = engine.connect()  
  
u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com')),  
          addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))  
  
result = conn.execute(u)  
print('Total {:,d} row(s):'.format(result.rowcount))  
for r in result.fetchall():  
    print("\t{}".format(r))  

Output:

view plaincopy to clipboardprint?
...  
2019-03-24 09:07:28,603 INFO sqlalchemy.engine.base.Engine SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
FROM addresses  
WHERE addresses.email_add LIKE %(email_add_1)s UNION SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
FROM addresses  
WHERE addresses.email_add LIKE %(email_add_2)s  
2019-03-24 09:07:28,603 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
Total 8 row(s):  
        (1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')  
        (2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')  
        (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')  
        (4, 5, 'MG Road Bangaluru', 'as@yahoo.com')  
        (6, 1, 'Shivajinagar Pune', 'ravi@gmail.com')  
        (7, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')  
        (8, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')  
        (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:

view plaincopy to clipboardprint?
u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')),  
          addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))  

Output:

view plaincopy to clipboardprint?
...  
2019-03-24 09:16:57,046 INFO sqlalchemy.engine.base.Engine SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
FROM addresses  
WHERE addresses.email_add LIKE %(email_add_1)s UNION ALL SELECT addresses.id, addresses.st_id, addresses.postal_add, addresses.email_add  
FROM addresses  
WHERE addresses.email_add LIKE %(email_add_2)s  
2019-03-24 09:16:57,046 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
Total 8 row(s):  
        (1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')  
        (2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')  
        (3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')  
        (6, 1, 'Shivajinagar Pune', 'ravi@gmail.com')  
        (7, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com')  
        (8, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')  
        (4, 5, 'MG Road Bangaluru', 'as@yahoo.com')  
        (9, 5, 'MG Road Bangaluru', 'as@yahoo.com')  

If you want to get an unique email address result, try below code:
- demo14.py

view plaincopy to clipboardprint?
#!/usr/bin/env python36  
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union, union_all  
from sqlalchemy.sql import select  
  
db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
engine = create_engine(db_string, echo = True)  
meta = MetaData(bind=engine)  
meta.reflect()  
addresses = meta.tables['addresses']  
conn = engine.connect()  
  
u = union(select([addresses.c.email_add]).where(addresses.c.email_add.like('%@gmail.com')),  
          select([addresses.c.email_add]).where(addresses.c.email_add.like('%@yahoo.com')))  
  
u.order_by(u.c.email_add)  
  
result = conn.execute(u)  
print('Total {:,d} row(s):'.format(result.rowcount))  
for r in result.fetchall():  
    print("\t{}".format(r))  

Output:

view plaincopy to clipboardprint?
2019-03-24 09:35:40,909 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add  
FROM addresses  
WHERE addresses.email_add LIKE %(email_add_1)s UNION SELECT addresses.email_add  
FROM addresses  
WHERE addresses.email_add LIKE %(email_add_2)s  
2019-03-24 09:35:40,909 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'email_add_2': '%@yahoo.com'}  
Total 4 row(s):  
        ('as@yahoo.com',)  
        ('kapoor@gmail.com',)  
        ('komal@gmail.com',)  
        ('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:

view plaincopy to clipboardprint?
u = except_(select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.email_add.like('%@gmail.com')),  
            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:

view plaincopy to clipboardprint?
...  
2019-03-24 09:42:55,989 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add, addresses.postal_add  
FROM addresses  
WHERE addresses.email_add LIKE %(email_add_1)s EXCEPT SELECT addresses.email_add, addresses.postal_add  
FROM addresses  
WHERE addresses.postal_add LIKE %(postal_add_1)s  
2019-03-24 09:42:55,989 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'postal_add_1': '%Pune'}  
Total 2 row(s):  
        ('kapoor@gmail.com', 'ChurchGate Mumbai')  
        ('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

view plaincopy to clipboardprint?
#!/usr/bin/env python36  
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union, union_all, except_, intersect  
from sqlalchemy.sql import select  
  
db_string = "postgresql://postgres:<yourpassword>@localhost/testdb"  
engine = create_engine(db_string, echo = True)  
meta = MetaData(bind=engine)  
meta.reflect()  
addresses = meta.tables['addresses']  
conn = engine.connect()  
  
u = intersect(select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.email_add.like('%@gmail.com')),  
              select([addresses.c.email_add, addresses.c.postal_add]).where(addresses.c.postal_add.like('%Pune')))  
  
  
result = conn.execute(u)  
print('Total {:,d} row(s):'.format(result.rowcount))  
for r in result.fetchall():  
    print("\t{}".format(r))  

Output:

view plaincopy to clipboardprint?
...  
2019-03-24 09:48:22,347 INFO sqlalchemy.engine.base.Engine SELECT addresses.email_add, addresses.postal_add  
FROM addresses  
WHERE addresses.email_add LIKE %(email_add_1)s INTERSECT SELECT addresses.email_add, addresses.postal_add  
FROM addresses  
WHERE addresses.postal_add LIKE %(postal_add_1)s  
2019-03-24 09:48:22,347 INFO sqlalchemy.engine.base.Engine {'email_add_1': '%@gmail.com', 'postal_add_1': '%Pune'}  
Total 1 row(s):  
        ('ravi@gmail.com', 'Shivajinagar Pune')  

程式扎記

標籤

2019年3月23日星期六

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

沒有留言:

張貼留言

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

檢舉濫用情形

學習筆記

標籤

2019年3月23日 星期六

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

沒有留言:

張貼留言

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

檢舉濫用情形

學習筆記

2019年3月23日星期六