Python庫之SQLAlchemy

冷冰若水發表於2020-10-13
PythonSQL

一、SQLAlchemy簡介

1.1、SQLAlchemy是什麼？

sqlalchemy是一個python語言實現的的針對關係型資料庫的orm庫。可用於連線大多數常見的資料庫，比如Postges、MySQL、SQLite、Oracle等。

1.2、為什麼要使用SQLAlchemy？

它將你的程式碼從底層資料庫及其相關的SQL特性中抽象出來。

1.3、SQLAlchemy提供了兩種主要的使用模式

  1. SQL表示式語言（SQLAlchemy Core）
  2. ORM

1.4、應該選擇哪種模式？

  • 雖然你使用的框架中已經內建了ORM，但是希望新增更強大的報表功能，請選用Core。
  • 如果你想在一個一模式為中心的檢視中檢視資料（使用者類似於SQL），請使用Core。
  • 如果你的資料不需要業務物件，請使用Core。
  • 如果你要把資料看作業務物件，請使用ORM。
  • 如果你想快速建立原型，請使用ORM。
  • 如果你需要同事使用業務物件和其他與問題域無關的資料，請組合使用Core和ORM。

1.5、連線資料庫

要連線到資料庫，需要先建立一個SQLAlchemy引擎。SQLAlchemy引擎為資料庫建立一個公共介面來執行SQL語句。這是通過包裝資料庫連線池和方言（不同資料庫客戶端）來實現的。

SQLAlchemy提供了一個函式來建立引擎。在這個函式中，你可以指定連線字串，以及其他一些可選的關鍵字引數。

from sqlalchemy import create_engine
engine = create_engine('sqlite:///cookies.db')
engine1 = create_engine('sqlite:///:memory:')
engine2 = create_engine('sqlite://///home/cookiemonster/cookies.db')
engine3 = create_engine('sqlite:///c:\\Users\\cookiemonster\\cookies.db')

engine_mysql = create_engine('mysql+pymysql://cookiemonster:chocolatechip', '@mysql01.monster.internal/cookies', pool_recycle=3600)

1.6、模式和型別

為了訪問底層資料庫，SQLAlchemy需要用某種東西來代表資料庫中的表。為此，可以使用下面三種方法總的一種：

  • 使用使用者定義的Table物件
  • 使用代表資料表的宣告式類
  • 從資料庫中推斷

二、SQLAlchemy core

SQLAlchemy core定義表結構使用的是1.5中說的第1種方式。table物件包含一系列帶有型別的列和屬性，它們與一個常見的後設資料容器相關聯。

後設資料可以看作是一種Table物件目錄。這些表可以通過MetaData.tables來訪問。

2.1、定義表結構

在SQLAlchemy Core中，我們通過Table建構函式來初始化Table物件。我們要在建構函式中提供MetaData物件（後設資料）和表名，任何其他引數都被認為是列物件。列是通過Column()函式建立的。

from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy import MedaData

metadata = MetaData()
user = table('user', metadata, 
			 Column(id, Integer(), primary_key=True), 
			 Column(name, String(255)), 
)

engine = create_engine('sqlite:///:memory:')
metadata.create_all(engine) # 表的持久化

2.2、插入資料

首先創造一條insert語句，用來把小明放入user表中。為此，先呼叫user表的insert()方法，然後再使用values()語句，關鍵字引數為各個列及相應值：

ins = user.insert().values(
	id=1, 
  name='小明'
)
print(str(ins))

到此僅僅只是建立了一個inset語句，還沒有真正執行呢，接下來執行插入操作：

connection = engine.connect()
result = connection.execute(ins)
print(result.inserted_primary_key)

2.3、查詢資料

構建查詢時，要用到select函式，它類似於標準SQL SELECT語句。

from sqlalchemy.sql import select
s = select([user])
# 可以使用str(s)檢視資料庫看到的語句
print(str(s))
rp = connection.execute(s)
results = rp.fetchall()

2.3.1、ResultProxy

execute()函式的返回值是一熱ResultProxy物件，它允許使用索引、名稱或Column物件進行訪問。

使用ResultProxy處理行

first_row = results[0]
first_row[1]
first_row.name
first_row[user.c.name]

迭代ResultProxy

rp = connection.execute(s)
for record in rp:
	print(record.user_name)

使用方法訪問結果

rp.first() # 若有記錄，則返回第一個記錄並關閉連線
rp.fetchone() # 返回一行，並保持游標為開啟狀態，以便你做更多獲取呼叫
rp.scalar() # 入股查詢結果是包含一個列的單條記錄，則返回單個值

2.3.2、控制查詢中的列數

s = select([user.c.name])
rp = connection.execute(s)
print(rp.keys())
result = rp.first()

2.3.3、排序

s = select([user.c.name])
s = s.order_by(user.c.name)
rp = connection.execute(s)
for user in rp:
	print(user.name)

2.3.4、限制返回結果集的條數

s = select([user.c.name])
s = s.order_by(user.c.name)
s = s.limit(2)
rp = connection.execute(s)
for user in rp:
	print(user.name)

2.3.5、內建SQL函式和標籤

from sqlalchemy.sql import func
s = select([func.sum(user.c.score)])
rp = connection.execute(s)
print(rp.scalar())

2.3.6、過濾

對查詢過濾是通過新增where()語句來完成的。

s = select([user]).where(user.c.name == '小明')
rp = connection.execute(s)
record = rp.first()
print(record.items())

這裡只是介紹了常用的查詢方法，更多複雜的查詢請查閱官方文件。

2.3、更新資料

update()方法和前面的insert()方法很相似，它們的語法幾乎完全一樣，但是update()可以指定一個where()子句，用來指出要更新哪些行。

from sqlalchemy import update
u = update(user).where(user.c.name == '小明')
u = u.values(name='小華')
result = connection.execute(u)
print(result.rowcount)

2.4、刪除資料

建立刪除語句時，既可以使用delete()函式，也可以使用表的delete()方法。與insert()和update()不同，delete()不接收值引數，只接收一個可選where子句，用來指定刪除範文。

from sqlalchemy import delete
u = delete(user).where(user.c.name == '小華')
result = connection.execute(u)
print(result.rowcount)

注意：

更多的高階操作：連線、別名、分組、鏈式呼叫、原始查詢等，請查閱官方文件。

2.5、事務

通過connection.begin()開啟一個事務，返回一個transaction物件，接下來根據執行的情況呼叫transaction.commit()提交修改或者呼叫transaction.rollback()回滾操作。

三、SQLAlchemy orm

SQLAlchemy orm定義表結構使用的是1.5中說的第2種方式。通過定義一個類，它繼承自一個名為declarative_base的特殊基類。declarative_base把後設資料容器和對映器（用來把類對映到資料表）結合在一起。

orm使用的類應該滿足如下四個要求：

  • 繼承自declarative_base物件。
  • 包含__tablename__，這是資料庫中使用的表名。
  • 包含一個或多個屬性，它們都是column物件。
  • 確保一個或多個屬性組成主鍵。

3.1、定義表結構：

from sqlalchemy import create_engine
from sqlalchemy import Column
from sqlalchemy import Integer
from sqlalchemy import String
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'user'
    
	id = Column(Integer, primary_key=True)
	name = Column(String(255))
	
engine = create_engine('sqlite:///')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

3.2、會話（session)

會話是SQLAlchemy ORM和資料庫互動的方式。它通過引擎包裝資料庫連線，併為通過會話載入或與會話關聯的物件提供標識對映（identity map）。標識對映是一種類似於快取的資料結構，它包含由物件表和主鍵確定的一個唯一的物件列表。會話還包裝了一個事務，這個事務將一直保持開啟狀態，直到會話提交或回滾。

為建立會話，SQLAlchemy提供了一個sessionmaker類，這個類可以確保在整個應用程式中能夠使用相同的引數建立會話。sessionmaker類通過建立一個Session類來實現這一點，Session類是根據傳遞給sessionmaker工廠的引數配置的。

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

engine = create_engine('sqlite:///:memory:')
Session = sessionmaker(bind=engine)
session = Session()

3.2、插入

user = User(1, '小明')
session.add(user)
session.commit()

3.3、查詢

for row in session.query(User):
	print(row.id, row.name)

注意：session.query()的返回值是Query物件，不能使用它的返回值作為查詢結果。關於Query物件的用法，請參閱：https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query

常用Query物件的方法：

q = session.query(User)
q.count() # 獲取查詢結果的數量
q.all() # 返回查詢結果的list，會觸發執行SQL查詢
q.get(id) # 根據primary_key查詢單個物件
q.as_scalar() # 返回此次查詢的SQL語句

3.3.1、控制查詢中的列數

print(session.query(user.name).first())

3.3.2、排序

for record in sesssion.query(user).order_by(user.name):
	print(user.name)

3.3.3、限制返回結果集的條數

query = session.query(user).order_by(user.name).[:2]
print([result.user_name for result in query])

3.3.4、內建SQL函式和標籤

from sqlalchemy import func
inv_count = session.query(func.sum(user.name)).scalar()
print(inv_count)

3.3.5、過濾

record = session.query(user).filter(user.name == '小華')
print(record)

3.4、更新資料

query = session.query(user)
xm_user = query.filter(user.user_name == '小華').first()
xm_user.name = 'robin'
session.commit()
print(xm_user.quantity)

3.5、刪除資料

query = session.query(user)
xm_user = query.filter(user.user_name == '小華').first()
session.delete(xm_user)
session.commit()
print(xm_user)

注意：

這裡簡單介紹了SQLAlchemy orm的常見用法，更高階的用法請查閱官方文件。

四、反射

使用反射技術可以利用先用資料庫填充SQLAlchemy物件。

4.1、反射單個表

建立初始物件：

from sqlalchemy import Metadata, create_engines
metadata = MetaData()
engine = reate_engine('sqlite:///')

反射Artist表

from sqlalchmy impport Table
artist = Table('Artist', metadata, autoload=True, autoload_with=engine)

4.2、反射整個資料庫

metadata.reflect(bind=engine)

參考資料

  1. https://docs.sqlalchemy.org/en/13/core/type_basics.html#generic-types

相關文章