建議從這裡下載這篇文章對應的.ipynb檔案和相關資源。這樣你就能在Jupyter中邊閱讀，邊測試文中的程式碼。
說明
接sqlalchemy 入門(上)，這篇會更詳細的講解和演示如果用SQLAlchemy完成Select, Update, Delete的操作。
準備
實驗準備內容和sqlalchemy 入門(上)的內容一致，這裡不在重複多說。
|
1 2 3 4 5 6 7 8 9 10 11 |
from sqlalchemy import create_engine user = 'test' password = 'test' port = '5432' dbname = 'test' engine = create_engine('postgresql://{user}:{password}@localhost:{port}/{dbname}'.format(**locals())) from helper import reset_tables,clear_tables,clear_schema,get_select,get_table,print_sql from IPython.display import display |
選擇
選擇全部列
選擇全部的列
|
1 2 3 4 5 6 7 |
users, addresses = reset_tables(engine) s = users.select() print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.name, test.users.fullname FROM test.users >>> |
| id | name | fullname | |
|---|---|---|---|
| 0 | 1 | jack | Jack Jones |
| 1 | 2 | wendy | Wendy Williams |
可以用下面方法得到涉及的列名
|
1 |
s.c.keys() |
[‘id’, ‘name’, ‘fullname’]
另一種稍繁瑣但是更通用的方法
|
1 2 3 4 5 6 7 8 9 10 11 |
from sqlalchemy import select s = select( [ users ] ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.name, test.users.fullname FROM test.users >>> |
| id | name | fullname | |
|---|---|---|---|
| 0 | 1 | jack | Jack Jones |
| 1 | 2 | wendy | Wendy Williams |
選擇指定列
可以在select的list中指定需要的列
|
1 2 3 4 5 6 7 8 9 |
s = select( [ users.c.id, users.c.fullname, ] ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.fullname FROM test.users >>> |
| id | fullname | |
|---|---|---|
| 0 | 1 | Jack Jones |
| 1 | 2 | Wendy Williams |
也可以用下面這種語法指定要選擇的列。好處是可以支援變數作為列名。
|
1 2 3 4 5 6 7 8 9 10 11 |
colname = 'fullname' s = select( [ users.c['id'], users.c[colname] ] ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.fullname FROM test.users >>> |
| id | fullname | |
|---|---|---|
| 0 | 1 | Jack Jones |
| 1 | 2 | Wendy Williams |
注意
上面的幾個例子,並沒有指定FROM條件,SQLAlchemy自動新增了FROM語句。。
修改列名
用label實現
|
1 2 3 4 5 6 7 8 9 10 |
s = select( [ users.c.id.label('user_id'), users.c.name.label('user_name'), ] ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id AS user_id, test.users.name AS user_name FROM test.users >>> |
| user_id | user_name | |
|---|---|---|
| 0 | 1 | jack |
| 1 | 2 | wendy |
選擇計算後的結果
選擇的時候也可以進行一些運算。例如字串拼接。
|
1 2 3 4 5 6 7 8 9 |
s = select( [ users.c.id, ('Fullname:'+users.c.fullname).label('fullname') ] ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id, 'Fullname:' || test.users.fullname AS fullname FROM test.users >>> |
| id | fullname | |
|---|---|---|
| 0 | 1 | Fullname:Jack Jones |
| 1 | 2 | Fullname:Wendy Williams |
下面是數值相加的例子。
|
1 2 3 4 5 6 7 8 9 |
s = select( [ users.c.id, (10+users.c.id).label('new_id') ] ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id, 10 + test.users.id AS new_id FROM test.users >>> |
| id | new_id | |
|---|---|---|
| 0 | 1 | 11 |
| 1 | 2 | 12 |
注意
上面的兩個例子裡，都使用了’+’號,SQLAlchemy根據資料型別自動決定編譯後的結果是字串連線還是數值相加。
使用sql function
可以使用func.func_name的形式應用函式,使用的時候只需要匯入func模組,接上資料庫中的函式名即可
|
1 2 3 4 5 6 7 8 9 10 11 12 |
from sqlalchemy import func s = select( [ users.c.id.label('user_id'), func.upper(users.c.name).label('user_name'), ] ) print_sql(engine,s,False) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id AS user_id, upper(test.users.name) AS user_name FROM test.users >>> |
| user_id | user_name | |
|---|---|---|
| 0 | 1 | JACK |
| 1 | 2 | WENDY |
注意應用函式的時候，label要放在在函式之外使用，否則是無效的，這是一個容易犯的錯誤。可以看到下面的例子裡，name列採用了自動命名。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
from sqlalchemy import func s = select( [ users.c.id.label('user_id'), func.upper(users.c.name.label('user_name')), ] ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id AS user_id, upper(test.users.name) AS upper_1 FROM test.users >>> |
| user_id | upper(test.users.name) | |
|---|---|---|
| 0 | 1 | JACK |
| 1 | 2 | WENDY |
使用window function
使用func.func_name().over()的方式即可
|
1 2 3 4 5 6 7 8 9 10 11 |
s = select([ users.c.id, users.c.name, func.row_number().over( order_by=users.c.name, ).label('num') ]) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.name, row_number() OVER (ORDER BY test.users.name) AS num FROM test.users >>> |
| id | name | num | |
|---|---|---|---|
| 0 | 1 | jack | 1 |
| 1 | 2 | wendy | 2 |
使用case
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
from sqlalchemy import case s = select( [ case( [ (users.c.id == 1, 'A'), (users.c.id == 3, 'C'), ], else_='B' ).label('case_test') ] ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT CASE WHEN (test.users.id = 1) THEN 'A' WHEN (test.users.id = 3) THEN 'C' ELSE 'B' END AS case_test FROM test.users >>> |
| case_test | |
|---|---|
| 0 | A |
| 1 | B |
新增常數列
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
from sqlalchemy import literal, text,literal_column s = select( [ users.c.id.label('user_id'), literal('AAAAAA').label('constant'), literal(None).label('null') ] ) print_sql(engine,s,False) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.id AS user_id, %(param_1)s AS constant, %(param_2)s AS "null" FROM test.users >>> |
| user_id | constant | null | |
|---|---|---|---|
| 0 | 1 | AAAAAA | None |
| 1 | 2 | AAAAAA | None |
指定limit and offset
例如用offset跳過1行，並且用limit只顯示2行
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
s = ( select( [ addresses.c.id, addresses.c.email_address, ] ).offset(1) .limit(2) ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 6 |
***Compiled SQL*** <<< SELECT test.addresses.id, test.addresses.email_address FROM test.addresses LIMIT 2 OFFSET 1 >>> |
| id | email_address | |
|---|---|---|
| 0 | 2 | jack@msn.com |
| 1 | 3 | www@www.org |
Order By排序
下面的例子中根據user.id和user.name排序
|
1 2 3 4 5 6 7 8 |
s = select( [users.c.name] ).order_by( users.c.id, users.c.name ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.name FROM test.users ORDER BY test.users.id, test.users.name >>> |
| name | |
|---|---|
| 0 | jack |
| 1 | wendy |
如果要控制升序降序的話。可以呼叫desc()方法或者使用desc函式。
|
1 2 3 4 5 6 7 8 |
s = select( [users.c.name] ).order_by( users.c.id.desc(), users.c.name.desc() ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.name FROM test.users ORDER BY test.users.id DESC, test.users.name DESC >>> |
| name | |
|---|---|
| 0 | wendy |
| 1 | jack |
|
1 2 3 4 5 6 7 8 9 |
from sqlalchemy import desc s = select( [users.c.name] ).order_by( desc(users.c.id), desc(users.c.name) ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.name FROM test.users ORDER BY test.users.id DESC, test.users.name DESC >>> |
| name | |
|---|---|
| 0 | wendy |
| 1 | jack |
Group By
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
s = ( select( [ users.c.name, func.count(addresses.c.id).label('count') ] ) .select_from(users.join(addresses)) .group_by(users.c.name) ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.name, count(test.addresses.id) AS count FROM test.users JOIN test.addresses ON test.users.id = test.addresses.user_id GROUP BY test.users.name >>> |
| name | count | |
|---|---|---|
| 0 | wendy | 2 |
| 1 | jack | 2 |
如果要根據多列去做group by的話、
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
s = ( select( [ users.c.name, func.count(addresses.c.id).label('count') ] ) .select_from(users.join(addresses)) .group_by( users.c.id, users.c.name ) ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 |
***Compiled SQL*** <<< SELECT test.users.name, count(test.addresses.id) AS count FROM test.users JOIN test.addresses ON test.users.id = test.addresses.user_id GROUP BY test.users.id, test.users.name >>> |
| name | count | |
|---|---|---|
| 0 | jack | 2 |
| 1 | wendy | 2 |
使用Having條件
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
s = ( select( [ users.c.name, func.count(addresses.c.id).label('count') ] ) .select_from(users.join(addresses)) .group_by(users.c.name) .having(func.count(addresses.c.id)>1) ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 6 |
***Compiled SQL*** <<< SELECT test.users.name, count(test.addresses.id) AS count FROM test.users JOIN test.addresses ON test.users.id = test.addresses.user_id GROUP BY test.users.name HAVING count(test.addresses.id) > 1 >>> |
| name | count | |
|---|---|---|
| 0 | wendy | 2 |
| 1 | jack | 2 |
集合操作
這裡至演示union_all。 union， except_， except_all， intersect， intersect_all同理，不再演示。
|
1 2 3 4 5 6 7 8 9 10 |
from sqlalchemy.sql import union, union_all, except_, except_all, intersect,intersect_all s = union_all( addresses.select().where(addresses.c.email_address == 'foo@bar.com'), addresses.select().where(addresses.c.email_address.like('%@yahoo.com')), ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 6 7 8 |
***Compiled SQL*** <<< SELECT test.addresses.id, test.addresses.user_id, test.addresses.email_address FROM test.addresses WHERE test.addresses.email_address = 'foo@bar.com' UNION ALL SELECT test.addresses.id, test.addresses.user_id, test.addresses.email_address FROM test.addresses WHERE test.addresses.email_address LIKE '%@yahoo.com' >>> |
| id | user_id | email_address | |
|---|---|---|---|
| 0 | 1 | 1 | jack@yahoo.com |
where條件
|
1 2 3 4 5 6 7 8 9 10 11 12 |
s = select( [ users, addresses.c.user_id, addresses.c.email_address, ] ).where( users.c.id == addresses.c.user_id ) print_sql(engine,s) display(get_select(engine,s)) |
|
1 2 3 4 5 6 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.name, test.users.fullname, test.addresses.user_id, test.addresses.email_address FROM test.users, test.addresses WHERE test.users.id = test.addresses.user_id >>> |
| id | name | fullname | user_id | email_address | |
|---|---|---|---|---|---|
| 0 | 1 | jack | Jack Jones | 1 | jack@yahoo.com |
| 1 | 1 | jack | Jack Jones | 1 | jack@msn.com |
| 2 | 2 | wendy | Wendy Williams | 2 | www@www.org |
| 3 | 2 | wendy | Wendy Williams | 2 | wendy@aol.com |
常見的where條件
等於
|
1 |
print_sql(engine,users.c.id == addresses.c.user_id) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.id = test.addresses.user_id >>> |
大於
|
1 |
print_sql(engine,users.c.id > addresses.c.user_id) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.id > test.addresses.user_id >>> |
不等於
|
1 |
print_sql(engine,users.c.id != addresses.c.user_id) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.id != test.addresses.user_id >>> |
有些條件需要呼叫方法實現，例如表現SQL中的IN的時候，這樣是不行的
|
1 |
print(users.c.id in [1,2,3]) |
False
而是應該用object本身提供的函式in_
|
1 2 |
s = users.c.id.in_([1,2,3]) print_sql(engine,s,compile_kwargs={"literal_binds": True}) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.id IN (1, 2, 3) >>> |
類似的有between
|
1 2 |
s = users.c.id.between(1,3) print_sql(engine,s,compile_kwargs={"literal_binds": True}) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.id BETWEEN 1 AND 3 >>> |
字串匹配like
|
1 2 |
s = users.c.name.like('C%') print_sql(engine,s,compile_kwargs={"literal_binds": True}) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.name LIKE 'C%' >>> |
特殊的算符
如果有一些非常規的operator，總是可以用.op方法取實現
|
1 2 |
s = users.c.id.op('special_operator')('foo') print_sql(engine,s) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.id special_operator 'foo' >>> |
邏輯連詞
最常用的有and_, or_, not_
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
from sqlalchemy.sql import and_, or_, not_ s = and_( users.c.name.like('j%'), users.c.id == addresses.c.user_id, or_( addresses.c.email_address == 'wendy@aol.com', addresses.c.email_address == 'jack@yahoo.com' ), not_(users.c.id > 5) ) print_sql(engine,s) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users.name LIKE 'j%' AND test.users.id = test.addresses.user_id AND (test.addresses.email_address = 'wendy@aol.com' OR test.addresses.email_address = 'jack@yahoo.com') AND test.users.id <= 5 >>> |
連續多個where連用也可以起到and的效果
|
1 2 3 4 5 6 7 8 9 |
s=( select( [users] ) .where(users.c.name.like('j%')) .where(users.c.id == addresses.c.user_id) ) print_sql(engine,s,compile_kwargs={"literal_binds": True}) |
|
1 2 3 4 5 6 |
***Compiled SQL*** <<< SELECT test.users.id, test.users.name, test.users.fullname FROM test.users, test.addresses WHERE test.users.name LIKE 'j%' AND test.users.id = test.addresses.user_id >>> |
也可以用python的&,|,~等邏輯連線符號代替and_(), or_(), not_
|
1 2 3 4 5 6 7 8 9 10 |
s = ( users.c.name.like('j%') & users.c.id == addresses.c.user_id & ( (addresses.c.email_address == 'wendy@aol.com') | (addresses.c.email_address == 'jack@yahoo.com') ) & (~(users.c.id > 5)) ) print_sql(engine,s,compile_kwargs={"literal_binds": True}) |
|
1 2 3 4 |
***Compiled SQL*** <<< (test.users.name LIKE 'j%' AND test.users.id) = (test.addresses.user_id AND (test.addresses.email_address = 'wendy@aol.com' OR test.addresses.email_address = 'jack@yahoo.com') AND test.users.id <= 5) >>> |
join表
|
1 2 3 4 5 6 |
s = users.join( addresses, users.c.id==addresses.c.user_id ) print_sql(engine,s) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users JOIN test.addresses ON test.users.id = test.addresses.user_id >>> |
自動Join
如果join時沒有指定on條件。SQLAlchemy會去檢查是否存在foreign key的定義。
helper.py的reset_tables函式中，已經定義了users.id是addresses.user_id的外來鍵。所以下面的語句能自動新增test.users.id = test.addresses.user_id的條件。
|
1 2 |
s = users.join(addresses) print_sql(engine,s) |
|
1 2 3 4 |
***Compiled SQL*** <<< test.users JOIN test.addresses ON test.users.id = test.addresses.user_id >>> |
注意
auto_load得到的表無法找到正確的foreign_key
|
1 2 3 4 |
from sqlalchemy import MetaData, Table metadata = MetaData() addresses = Table('addresses', metadata, schema = 'test', autoload=True, autoload_with=engine) addresses.c.values() |
|
1 2 3 |
[Column('id', INTEGER(), table=, primary_key=True, nullable=False, server_default=DefaultClause(, for_update=False)), Column('user_id', INTEGER(), ForeignKey('test.users.id'), table=), Column('email_address', VARCHAR(length=20), table=, nullable=False)] |
看上去定義了foreign key的資訊,但是執行的話會出錯
|
1 2 3 4 |
try: s = users.join(addresses) except Exception as e: print(type(e),e) |
|
1 |
Can't find any foreign key relationships between 'users' and 'addresses'. |
select_from
如果要將join語句用於select的話，可以用select_from語句,功能相當於SQL中的FROM。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
s = ( select( [ users.c.id, addresses.c.email_address ] ) .select_from( users.join(addresses) ) .where(users.c.id==1) ) print_sql(engine,s) display(get_select(engine,s)) |
Left Join
如果要使用left outer join，把join換成outerjoin
|
1 2 3 4 5 6 7 8 9 10 11 12 |
s = ( select( [ users.c.id, addresses.c.email_address ] ) .where(users.c.id==1) .select_from(users.outerjoin(addresses)) ) print_sql(engine,s,compile_kwargs={"literal_binds": True}) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
--------------------------------------------------------------------------- NoForeignKeysError Traceback (most recent call last) in () 7 ) 8 .select_from( ----> 9 users.join(addresses) 10 ) 11 .where(users.c.id==1) /home/exolution/venv3.5/lib/python3.5/site-packages/sqlalchemy/sql/selectable.py in join(self, right, onclause, isouter, full) 446 """ 447 --> 448 return Join(self, right, onclause, isouter, full) 449 450 def outerjoin(self, right, onclause=None, full=False): /home/exolution/venv3.5/lib/python3.5/site-packages/sqlalchemy/sql/selectable.py in __init__(self, left, right, onclause, isouter, full) 792 793 if onclause is None: --> 794 self.onclause = self._match_primaries(self.left, self.right) 795 else: 796 self.onclause = onclause /home/exolution/venv3.5/lib/python3.5/site-packages/sqlalchemy/sql/selectable.py in _match_primaries(self, left, right) 926 else: 927 left_right = None --> 928 return self._join_condition(left, right, a_subset=left_right) 929 930 @classmethod /home/exolution/venv3.5/lib/python3.5/site-packages/sqlalchemy/sql/selectable.py in _join_condition(cls, a, b, ignore_nonexistent_tables, a_subset, consider_as_foreign_keys) 974 "Can't find any foreign key relationships " 975 "between '%s' and '%s'.%s" % --> 976 (a.description, b.description, hint)) 977 978 crit = [(x == y) for x, y in list(constraints.values())[0]] NoForeignKeysError: Can't find any foreign key relationships between 'users' and 'addresses'. |
Outer Join
如果要使用outer join，把join換成outerjoin,並且加上引數full = True
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
s = ( select( [ users.c.id, addresses.c.email_address ] ) .where(users.c.id==1) .select_from( users.outerjoin(addresses,full = True) ) ) print_sql(engine,s,compile_kwargs={"literal_binds": True}) display(get_select(engine,s)) |
Alias
要把一個子查詢結果像一張表那樣被使用時,需要使用alias()給子查詢命名。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
email_count = ( select( [ addresses.c.email_address, func.count(addresses.c.email_address).label('count') ] ).group_by( addresses.c.email_address ).alias('email_count') ) print_sql(engine, email_count) s = ( select( [ addresses.c.email_address, email_count.c.count ] ) .select_from( email_count.outerjoin(addresses,email_count.c.email_address == addresses.c.email_address) ) ) print_sql(engine,s) display(get_select(engine,s)) |
使用SQLAlchemy的時候，由於可以通過python變數名來找到正確的查詢，因此並不一定要去指定命名，SQLAlchemy會新增自動的命名。下面的例子裡，SQLAlchemy採用了自動命名anon_1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
email_count = ( select( [ addresses.c.email_address, func.count(addresses.c.email_address).label('count') ] ).group_by( addresses.c.email_address ) ).alias() print_sql(engine, email_count) s = ( select( [ addresses.c.email_address, email_count.c.count ] ) .select_from( email_count.outerjoin(addresses,email_count.c.email_address == addresses.c.email_address) ) ) print_sql(engine,s,compile_kwargs={"literal_binds": True}) display(get_select(engine,s)) |
關聯子查詢
基本概念
先用一個例子演示一下關聯子查詢,這個查詢的功能是找出每個使用者的id和擁有的郵箱的個數。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
s = ''' SELECT test.users.id, ( SELECT count(test.addresses.id) FROM test.addresses WHERE test.users.id = test.addresses.user_id ) AS email_count FROM test.users ''' print(engine.execute(s).fetchall()) |
這裡需要注意的是,子查詢有以下幾個特點，
- 子查詢中使用了test.users.id,但是子查詢的FROM語句中並沒有出現test.users。
- 子查詢返回的結果為一行,一列。
實際上，這種情況下子查詢的test.users是由外層的test.users關聯的，如果外層是id為1的user，那麼子查詢的test.users.id就是1;如果外層是id為2的user，那麼子查詢的test.users.id就是2。
為了方便理解，可以想象有一個根據傳入的user id，查詢擁有的郵箱數的函式email_count，那麼下面的例子和上面的例子是等價的。
|
1 2 3 4 |
SELECT test.users.id, email_count(test.users.id) #假象的函式 FROM test.users |
SQLAlchemy中的實現
上面例子中的查詢方式稱為關聯子查詢，下面演示怎麼在SQLAlchemy中構造關聯子查詢。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
from sqlalchemy import func email_count = ( select( [ func.count(addresses.c.id) #返回的結果只有1列 ] ). where(users.c.id == addresses.c.user_id) #返回的結果只有1行 .label('email_count') # 最後需要用label而不是alias ) s = select( [ users.c.id, email_count ] ) print_sql(engine,s) display(get_select(engine, s)) |
這裡要注意關聯子查詢的結尾必須是label，label一般用於給列重新命名。 (實際上由於關聯子查詢返回一行一列，的確像是一個列。) 如果關聯子查詢最後沒有加label而是alias，那麼就不會編譯成關聯子查詢。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
from sqlalchemy import func email_count = ( select( [ func.count(addresses.c.id).label('email_count') #返回的結果只有1列 ] ). where(users.c.id == addresses.c.user_id) #返回的結果只有1行 .alias() ) s = select( [ users.c.id, email_count.c.email_count ] ) print_sql(engine,s) display(get_select(engine, s)) |
可以看到上面編譯後的SQL語句中,FROM語句新增了test.users。子查詢本身就構成了一個完整的查詢，統計了所有郵箱的計數，結果為固定的4。
關聯行為的調整
可以通過correlate, correlate_except指定或排除sub query中需要關聯的外層的table範圍。例如通過correlate(None)或者correlate_except(users)禁止sub query關聯外層的users
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
email_count = ( select( [ func.count(addresses.c.id) ] ). where(users.c.id == addresses.c.user_id) .correlate(None) #.correlate_except(users)在這個例子裡也能起到同樣的效果 .label('email_count') #！！ ) s = select( [ users.c.id, email_count #!! ] ) print_sql(engine,s) display(get_select(engine, s)) |
可以看到上面的例子裡，sub query中自動在From語句中補充了users，成為完成的select語句，結果也變成了固定的4
join lateral
目前只有postgresql支援。 join lateral實際上可以看做另一種形式的關聯子查詢。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
from sqlalchemy import true sub_query = ( select( [ func.count(addresses.c.id).label('count') #返回一列 ] ). where(users.c.id == addresses.c.user_id) #返回一行 .lateral('sub_query') #！！用lateral()代替常規的label() ) s = select( [ users.c.id, sub_query.c.count ] ).select_from( users.join( sub_query, true() #用這個代替常規的on條件 ) ) print_sql(engine,s) display(get_select(engine, s)) |
join lateral語句實際上也起到了遍歷users,取出id和對應email_address個數的功能。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT test.users.id, sub_query.count FROM test.users #被sub query關聯的表 JOIN LATERAL ( SELECT count(test.addresses.id) AS count FROM test.addresses WHERE test.users.id = test.addresses.user_id #users關聯了join lateral左邊的test.users ) AS sub_query ON true |
這段程式碼等價於
|
1 2 3 4 5 6 7 8 9 |
SELECT sub_query.id, sub_query.count FROM ( select users.id as id, email_count(user.id) as count #email_count是假想的函式 from users ) AS sub_query |
Update
最基本的update，不指定任何條件，相當於對所有的row做了遍歷
|
1 2 3 4 5 6 7 8 9 10 11 12 |
stmt = ( users.update(). values(fullname="Fullname: " + users.c.name) ) print_sql(engine, stmt, compile_kwargs={"literal_binds": True}) engine.execute(stmt) s = users.select() display(get_select(engine,s)) |
選擇單行並進行遍歷
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
users, addresses = reset_tables(engine) stmt = ( users.update(). where(users.c.name == 'jack'). values(name='JACK') ) print_sql(engine, stmt, compile_kwargs={"literal_binds": True}) engine.execute(stmt) s = users.select() get_select(engine,s) |
更新資料
對全資料更新
下面是最基本的update方式,在values方法中指定資料的更新方法。
如果不加where條件對的話，這個更新邏輯會應用與所有的資料。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
users,addresses = reset_tables(engine) stmt = ( users.update(). values(fullname="Fullname: " + users.c.name) ) print_sql(engine, stmt, compile_kwargs={"literal_binds": True}) engine.execute(stmt) s = users.select() display(get_select(engine,s)) |
對單行更新
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
users,addresses = reset_tables(engine) stmt = ( users.update(). values( name='Name:'+users.c.name ). where(users.c.name == 'jack') ) print_sql(engine, stmt) engine.execute(stmt) display(get_table(engine,users)) |
也可以用dict的方式去指定更新方式。 如果用這種方式的話, object和列名都可以作為key。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
users,addresses = reset_tables(engine) stmt_1 = ( users.update(). values( {users.c.name:'Name:'+users.c.name} # object作為key ). where(users.c.name == 'jack') ) stmt_2 = ( users.update(). values( {'name':'Name:'+users.c.name} # 列名作為key ). where(users.c.name == 'jack') ) print_sql(engine, stmt_1) print_sql(engine, stmt_2) engine.execute(stmt_1) display(get_table(engine,users)) |
對多行進行更新
用給定的資料進行更新
可以藉助上一個教程提到過的bindparam。 編寫針對單個row的update邏輯， 但是將需要更新的多條資料用list傳入即可實現批量的更新。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
from sqlalchemy import bindparam users, addresses = reset_tables(engine) stmt = ( users.update(). where(users.c.name == bindparam('oldname')). values(name=bindparam('newname')) ) data = [ {'oldname':'jack', 'newname':'JACK'}, {'oldname':'wendy', 'newname':'mary'}, {'oldname':'jim', 'newname':'jake'} ] engine.execute(stmt,data) print_sql(engine, stmt, False) engine.execute(stmt,data) s = users.select() display(get_select(engine,s)) |
不過update語句的引數數值只能在execute階段傳入，不能使用params()傳入
|
1 2 3 4 5 6 7 8 9 10 11 12 |
from sqlalchemy import bindparam users, addresses = reset_tables(engine) try: stmt = ( users.update(). where(users.c.name == bindparam('oldname')). values(name=bindparam('newname')) ).params(data) except Exception as e: print(type(e),e) |
用另一個select結果更新資料
關鍵是通過where條件去指定一下資料來源(select結果)和待更新的表之間是怎麼的匹配的。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
from sqlalchemy import bindparam,func,select users, addresses = reset_tables(engine) first_addresses = ( select( [ addresses.c.user_id.label('user_id'), func.min(addresses.c.email_address).label('name') ] ).group_by( addresses.c.user_id ) ).alias('first_address') s = ( users .update() .values( { users.c.name:first_addresses.c.name } ).where( first_addresses.c.user_id == users.c.id ) ) engine.execute(s) print_sql(engine, s) s1 = users.select() display(get_select(engine,s1)) |
也可以用類似關聯子查詢的方式那樣去進行資料升級。
下面我們用每個人字典排序的第一個郵箱去替換原來的name。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
from sqlalchemy import bindparam,func users, addresses = reset_tables(engine) first_address = ( select( [ func.min(addresses.c.email_address) ] ).group_by( addresses.c.user_id ).where( addresses.c.user_id == users.c.id #users沒有定義來源，會自動和待升級的表進行關聯。 ) ).label('name') s = ( users .update() .values( { users.c.name:first_address } ) ) engine.execute(s) print_sql(engine, s) display(get_table(engine,users)) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
from sqlalchemy import bindparam,func users, addresses = reset_tables(engine) first_address = ( select( [ func.min(addresses.c.email_address).label('name') ] ).group_by( addresses.c.user_id ).where( addresses.c.user_id == users.c.id #users沒有定義來源，會自動和待升級的表進行關聯。 ) ).label('first_address') s = ( users .update() .values( { users.c.name:first_address } ) ) engine.execute(s) print_sql(engine, s) display(get_table(engine,users)) |
和關聯子查詢一樣，用這種方式修改資料時，返回的資料也必須是單行單列。
控制列操作的順序
由於SQLAlchemy編譯update語句的時候，預設是按照列在users表中的出現順序去編譯。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
users, addresses = reset_tables(engine) s = ( users .update() .values( { users.c.fullname:users.c.name, users.c.name:'*****', } ) ) engine.execute(s) print_sql(engine, s) display(get_table(engine,users)) |
|
1 2 3 4 |
***Compiled SQL*** <<< UPDATE test.users SET name='*****', fullname=test.users.name >>> |
| id | name | fullname | |
|---|---|---|---|
| 0 | 1 | ***** | jack |
| 1 | 2 | ***** | wendy |
編譯的語句中，順序是name=’*****’, fullname=test.users.name。如果我們需要精確的去控制編譯後語句列更新的順序，可以用下面的方式
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
users, addresses = reset_tables(engine) s = ( users .update(preserve_parameter_order=True) #新增preserve_parameter_order=True .values( #使用tuple list定義列更新邏輯 [ (users.c.fullname,users.c.name), (users.c.name,'*****'), ] ) ) engine.execute(s) print_sql(engine, s) display(get_table(engine,users)) |
|
1 2 3 4 |
***Compiled SQL*** <<< UPDATE test.users SET fullname=test.users.name, name='*****' >>> |
| id | name | fullname | |
|---|---|---|---|
| 0 | 1 | ***** | jack |
| 1 | 2 | ***** | wendy |
現在可以看到編譯的語句是SET fullname=test.users.name, name=’*****’，反映了tuple list的順序。通過這種方式我們就可以改變編譯的SET語句操作列的順序。
注意
對於postgresql，上面兩種SET的順序結果都是一致的。但是對於MYSQL資料庫，如果編譯結果是
|
1 |
UPDATE test.users SET name='*****', fullname=test.users.name |
在執行fullname=test.users.name部分的時候，會採用”*”作為name的數值，導致最後fullname也變成’*’，只有用
|
1 |
UPDATE test.users SET fullname=test.users.name, name='*****' |
才能得到正確的結果。
這時就有必要去指定SET語句中列操作的順序。
刪除資料
掌握了修改資料的操作後, 刪除的操作就非常簡單。不過要注意約束條件。例如addresses的user_id是users表的外來鍵，那麼在刪除addresses表的資料前，是不能刪除users表的資料的。
刪除全量資料
|
1 |
users, addresses = reset_tables(engine) |
由於外來鍵造成的約束，直接刪除users表是會報錯的。
|
1 2 3 4 5 6 7 |
d = users.delete() try: engine.execute(d) display(get_table(engine,users)) except Exception as e: print(type(e),e) |
|
1 2 3 |
(psycopg2.IntegrityError) update or delete on table "users" violates foreign key constraint "addresses_user_id_fkey" on table "addresses" DETAIL: Key (id)=(1) is still referenced from table "addresses". [SQL: 'DELETE FROM test.users'] |
需要先刪除addresses, 再刪除users
|
1 2 3 4 5 6 7 8 |
del_addresses = addresses.delete() del_users = users.delete() engine.execute(del_addresses) engine.execute(del_users) display(get_table(engine,users)) display(get_table(engine,addresses)) |