# SQLAlchemy 2.0(01)概觀
###### tags: `python` `sqlalchemy`
[官方文件](https://docs.sqlalchemy.org/en/20/index.html)
最近收到sqlalchemy已經正式發佈2.0，似乎有不少改變，這影響flask-slqalchemy的使用，所以在這邊簡單整理一些使用上的資訊。當然，我給的始終是一個『我看過』覺得重要的資料，如果有辦法的話真心的建議你一定要快速的看過一次官方文件。
粗淺來看，SQLAlchemy有兩個組成，一個是Core，另一個是建立在Core上的ORM，這是一種把資料庫當做物件一般操作的概念，官方文件中特別提到，1.4跟2.0之間的ORM在使用上有著明顯的差異，一定要特別的注意。
## 基本觀念
首先讓我們利用`create_engine`來建立一個資料庫的連線：
```python=
from sqlalchemy import create_engine
engine = create_engine('sqlite+pysqlite:///:memory:', echo=True)
```
上面定義的是一個記憶體資料庫，用完就丟了，只是測試。值得注意的是，`sqlite+pysqlite`中間不能有空格。
有這個`engine`之後就可以跟資料庫連接，如果是Core的話就是`engine.connect`，如果是ORM的話就另外有個`Session`來包裝它。
先看Core的情況，我們測試在一個上下文中執行`select`：
```python=
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，然後寫入一筆資料的範例：
```python=
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的方式：
```python=
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`來執行資料庫語法。
:::info
如果是ORM的話也會有相對應的`Session.execute()`的方法可以執行
:::
## 基本查詢
利用剛剛寫入的資料來做一個基本的查詢吧：
```python=
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也可以：
```pyrhon=
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`的時候可以利用參數加入：
```python=
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}")
```
:::danger
任何資料庫的操作都請一定要考慮到sql injection的問題。
:::
寫入的時候我們也可以利用參數來寫入資料：
```python=
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的情況下，同樣的範例可以這麼寫：
```python=
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做多筆資料的更新：
```python=
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的概念說明，能這麼用，但比較不會這樣用。
Sign in with Wallet
Connect another wallet