HackMD
SQLAlchemy 2.0(01)概觀
tags: python sqlalchemy
最近收到sqlalchemy已經正式發佈2.0，似乎有不少改變，這影響flask-slqalchemy的使用，所以在這邊簡單整理一些使用上的資訊。當然，我給的始終是一個『我看過』覺得重要的資料，如果有辦法的話真心的建議你一定要快速的看過一次官方文件。
粗淺來看，SQLAlchemy有兩個組成，一個是Core，另一個是建立在Core上的ORM，這是一種把資料庫當做物件一般操作的概念，官方文件中特別提到，1.4跟2.0之間的ORM在使用上有著明顯的差異，一定要特別的注意。
基本觀念
首先讓我們利用create_engine來建立一個資料庫的連線：
from sqlalchemy import create_engine
engine = create_engine('sqlite+pysqlite:///:memory:', echo=True)
上面定義的是一個記憶體資料庫，用完就丟了，只是測試。值得注意的是，sqlite+pysqlite中間不能有空格。
有這個engine之後就可以跟資料庫連接，如果是Core的話就是engine.connect，如果是ORM的話就另外有個Session來包裝它。
先看Core的情況，我們測試在一個上下文中執行select：
from sqlalchemy import text
with engine.connect() as conn:
result = conn.execute(text('select "good job test"'))
print(result.all())
上面執行的就有點像是select xxx from dual，而且因為我們在create_engine的時候有設置echo=True，所以執行之後會看到一些響應資訊：
2023-03-08 16:16:55,205 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-03-08 16:16:55,205 INFO sqlalchemy.engine.Engine select "good job test"
2023-03-08 16:16:55,205 INFO sqlalchemy.engine.Engine [generated in 0.00064s] ()
[('good job test',)]
2023-03-08 16:16:55,206 INFO sqlalchemy.engine.Engine ROLLBACK
可以看到最終出現的是ROLLBACK，目前預設情況下都不會自動commit(印象中也無法設置自動commit)。也因此最後的所有行為都必需要自己手動加一個conn.commit()來完成整個交易記錄。官方有特別提到，這種走到那commit到那的風格稱之為commit as you go
如下官方給出的一個建立table，然後寫入一筆資料的範例：
with engine.connect() as conn:
conn.execute(text("CREATE TABLE some_table (x int, y int)"))
conn.execute(
text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
[{"x": 1, "y": 1}, {"x": 2, "y": 4}],
)
conn.commit()
執行之後得到的響應就會是：
2023-03-08 16:22:04,551 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2023-03-08 16:22:04,551 INFO sqlalchemy.engine.Engine CREATE TABLE some_table (x int, y int)
2023-03-08 16:22:04,551 INFO sqlalchemy.engine.Engine [generated in 0.00058s] ()
2023-03-08 16:22:04,554 INFO sqlalchemy.engine.Engine INSERT INTO some_table (x, y) VALUES (?, ?)
2023-03-08 16:22:04,554 INFO sqlalchemy.engine.Engine [generated in 0.00074s] [(1, 1), (2, 4)]
2023-03-08 16:22:04,554 INFO sqlalchemy.engine.Engine COMMIT
另外有一種把conn context在一開始就定義為transaction block的方式：
with engine.begin() as conn:
conn.execute(
text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
[{"x": 6, "y": 8}, {"x": 9, "y": 10}],
)
看的出上下文的宣告差異，engine.begin() as conn。因為已經定義成是交易記錄的區塊，所以執行之後沒異常就會自動commit，這樣的風格則稱之為begin once。
官方建議是採用begin once，明確的定義整個交易記錄的上下文，不過範例中的說明都會採用commit as you go，就是想要就commit一發。
這邊已經有一點概念了，利用create_engine來定義一個engine，然後在交易記錄上下文中利用conn.execute搭配text來執行資料庫語法。
如果是ORM的話也會有相對應的Session.execute()的方法可以執行
基本查詢
利用剛剛寫入的資料來做一個基本的查詢吧：
with engine.connect() as conn:
result = conn.execute(text("SELECT x, y FROM some_table"))
for row in result:
print(f"x: {row.x} y: {row.y}")
其中result是一個CursorResult的物件。我們可以直接對這個回傳的物件做迭代取值的動作。當然你也可以選擇先用result.all()把資料先丟去另一個變數再來看你要做什麼處理，又或者像我們搜尋的是兩個欄位，也可以直接用for x, y in result:的迭代方式來處理，還是用索引的方式x = row[0]、或是利用mappings()把結果轉為dict也可以：
result = conn.execute(text("select x, y from some_table"))
for dict_row in result.mappings():
x = dict_row["x"]
y = dict_row["y"]
條件參數
查詢如果有需要條件的話，在execute的時候可以利用參數加入：
with engine.connect() as conn:
result = conn.execute(text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2})
for row in result:
print(f"x: {row.x} y: {row.y}")
任何資料庫的操作都請一定要考慮到sql injection的問題。
寫入的時候我們也可以利用參數來寫入資料：
with engine.connect() as conn:
conn.execute(
text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
[{"x": 11, "y": 12}, {"x": 13, "y": 14}],
)
conn.commit()
上面範例可以看的到利用list搭配dict來做多筆的寫入，這樣的作法稱為executemany，會對單個sql語法做多次的執行，結果來看就是會執行兩次的寫入動作。
ORM
在ORM的情況下，同樣的範例可以這麼寫：
from sqlalchemy.orm import Session
stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y")
with Session(engine) as session:
result = session.execute(stmt, {"y": 6})
for row in result:
print(f"x: {row.x} y: {row.y}")
利用Session建立一個上下文的區塊來執行我們所定義的資料庫語法stms，在execute的過程中給定參數y=6。
利用ORM做多筆資料的更新：
with Session(engine) as session:
result = session.execute(
text("UPDATE some_table SET y=:y WHERE x=:x"),
[{"x": 9, "y": 11}, {"x": 13, "y": 15}],
)
session.commit()
在抽象觀念上跟採用Core沒有什麼不一樣。不過這邊真的只是一個ORM的概念說明，能這麼用，但比較不會這樣用。
