SQLAlchemy 入門(mén)教程
前言
目前筐钟，許多主流的語(yǔ)言，都實(shí)現(xiàn)了對(duì)象關(guān)系映射（Object Relational Mapper赋朦，簡(jiǎn)稱ORM）的庫(kù)包篓冲。ORM的主要功能是將數(shù)據(jù)庫(kù)表中的每條記錄映射成一個(gè)對(duì)象。所有的數(shù)據(jù)庫(kù)操作宠哄，都轉(zhuǎn)化為對(duì)象的操作壹将。這樣可以增加代碼的可讀性和安全性。
ORM優(yōu)點(diǎn)：
- 簡(jiǎn)潔易讀：將數(shù)據(jù)表抽象為對(duì)象（數(shù)據(jù)模型）琳拨，更直觀易讀瞭恰。
- 可移植：封裝了多種數(shù)據(jù)庫(kù)引擎屯曹，面對(duì)多個(gè)數(shù)據(jù)庫(kù)狱庇，操作基本一致，代碼易維護(hù)恶耽。
- 更安全：有效避免
SQL注入密任。
當(dāng)然性能上會(huì)低于直接執(zhí)行SQL語(yǔ)句，本文介紹SQLAlchemy的一些基礎(chǔ)操作偷俭。
1. 建立連接
任何SQLAlchemy應(yīng)用程序的開(kāi)始都是一個(gè)名為engine . 此對(duì)象充當(dāng)連接到特定數(shù)據(jù)庫(kù)的中心源浪讳，提供工廠和稱為 connection pool對(duì)于這些數(shù)據(jù)庫(kù)連接。引擎通常是一個(gè)只為特定數(shù)據(jù)庫(kù)服務(wù)器創(chuàng)建一次的全局對(duì)象涌萤，并使用一個(gè)URL字符串進(jìn)行配置淹遵，該字符串將描述如何連接到數(shù)據(jù)庫(kù)主機(jī)或后端口猜。
# dialect[+driver]://user:password@host/dbname[?key=value..]
engine = create_engine("mysql://scott:tiger@hostname/dbname", encoding='latin1', echo=True)
創(chuàng)建engine的URL格式為dialect[+driver]://user:password@host/dbname[?key=value..]，其中dialect表示數(shù)據(jù)庫(kù)類型例如：mysql透揣、oracle济炎、postgresql等，而driver代表使用的數(shù)據(jù)庫(kù)API如：psycopg2辐真、pyodbc等须尚。
create_engine.echo：設(shè)置為True時(shí)會(huì)打印日志∈淘郏可以查看調(diào)用的具體SQL語(yǔ)句方便調(diào)試耐床。create_engine.future：標(biāo)志設(shè)置為True以便我們充分利用 2.0 style ，1.x-->2.0最主要的API更改是從select()改使用Query對(duì)象楔脯。create_engine.pool_size：連接池的大小默認(rèn)為5個(gè)撩轰，設(shè)置為0時(shí)表示連接無(wú)限制。create_engine.pool_recycle：設(shè)置時(shí)間以限制數(shù)據(jù)庫(kù)多久沒(méi)連接自動(dòng)斷開(kāi)昧廷。
# python3 下使用pymysql連接mysql钧敞。
engine = create_engine(
"mysql+pymysql://user:pwd@host/dbname",
echo=True,
pool_size=8,
pool_recycle=3600
)
# 連接SQLite
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True, future=True)
2. ORM的會(huì)話
使用ORM時(shí)，基本的事務(wù)/數(shù)據(jù)庫(kù)交互對(duì)象稱為Session麸粮。
在SQLAlchemy中溉苛，這個(gè)對(duì)象通常傳遞我們給它的SQL語(yǔ)句，它管理ORM映射對(duì)象的持久性操作弄诲。
Session的主要目的是建立與數(shù)據(jù)庫(kù)的會(huì)話愚战，它維護(hù)你加載和關(guān)聯(lián)的所有數(shù)據(jù)庫(kù)對(duì)象。它是數(shù)據(jù)庫(kù)查詢Query的一個(gè)入口齐遵。
Session通常在我們需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作時(shí)創(chuàng)建寂玲。
一旦一個(gè)Session創(chuàng)建成功，我們?cè)谶@個(gè)Session下完成多個(gè)事務(wù)（transaction）梗摇。
究竟何時(shí)創(chuàng)建和關(guān)閉Session拓哟，不能一概而論，但是一個(gè)原則是Session不應(yīng)該在操作事務(wù)的方法中創(chuàng)建伶授。
sessionmaker函數(shù)是配置Session的工廠断序，通過(guò)它建立的配置參數(shù)創(chuàng)建產(chǎn)生新的Session。
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
some_engine = create_engine('mysql+pymysql://username:password@localhost/mydb?charset=utf8')
ession = sessionmaker(bind=some_engine)
session = Session()
Session的常見(jiàn)操作方法包括：
flush：預(yù)提交糜烹，提交到數(shù)據(jù)庫(kù)文件违诗，還未寫(xiě)入數(shù)據(jù)庫(kù)文件中。commit：提交了一個(gè)事務(wù)疮蹦。rollback：回滾诸迟。close：關(guān)閉會(huì)話。
3. 創(chuàng)建模型
前面有提到ORM的重要特點(diǎn)，那么我們操作表的時(shí)候就需要通過(guò)操作對(duì)象來(lái)實(shí)現(xiàn)阵苇，現(xiàn)在我們來(lái)創(chuàng)建一個(gè)類壁公，以常見(jiàn)的用戶表舉例：
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, SMALLINT, DECIMAL, Enum, TEXT, TIMESTAMP
Base = declarative_base()
class Users(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
name = Column(String(64), unique=True)
email = Column(String(64))
def __init__(self, name, email):
self.name = name
self.email = email
declarative_base()是sqlalchemy內(nèi)部封裝的一個(gè)方法，通過(guò)其構(gòu)造一個(gè)基類绅项，這個(gè)基類和它的子類贮尖，可以將Python類和數(shù)據(jù)庫(kù)表關(guān)聯(lián)映射起來(lái)。
數(shù)據(jù)庫(kù)表模型類通過(guò)__tablename__和表關(guān)聯(lián)起來(lái)趁怔，Column表示數(shù)據(jù)表的列湿硝。
4. 生成表
Base.metadata.create_all(engine)
創(chuàng)建表，如果存在則忽略润努，執(zhí)行以上代碼关斜，就會(huì)發(fā)現(xiàn)在數(shù)據(jù)庫(kù)中創(chuàng)建了users表。
5. 抽象模型
現(xiàn)在我們修改之前的用戶表铺浇，加入新字段和一張訂單表痢畜。
使用了一個(gè)基類，定義了共同的字段：id和創(chuàng)建更新時(shí)間鳍侣。值得注意的是基類中設(shè)置了__abstract__如果不設(shè)置將會(huì)報(bào)錯(cuò)丁稀。
order中定義了一個(gè)枚舉類型，同時(shí)order和user使用user_id進(jìn)行關(guān)聯(lián)并未使用外鍵倚聚。
import enum
from sqlalchemy import Column, String, Integer, SMALLINT, FLOAT, DECIMAL, Enum
from sqlalchemy.sql.functions import current_timestamp, current_time
from orm.base_model import BaseTimestampModel
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class PayType(enum.Enum):
alipay = 0
unionpay = 1
weixin = 2
balance = 3
combo = 4
package = 5
company = 6
offline = 7
class BaseModel(Base):
__abstract__ = True
id = Column(Integer, primary_key=True, autoincrement=True)
created_at = Column(TIMESTAMP, nullable=False, default=current_timestamp())
updated_at = Column(TIMESTAMP, nullable=False, default=current_timestamp()
class User(BaseModel):
__tablename__ = 'user'
mobile = Column(String)
nickname = Column(String)
status = Column(SMALLINT, default=1)
appid = Column(String, nullable=True)
def __init__(self, nickname, mobile):
self.nickname = nickname
self.mobile = mobile
class Order(BaseModel):
__tablename__ = 'order'
user_id = Column(Integer)
ordersn = Column(String, unique=True)
order_type = Column(SMALLINT, default=2)
pay_type = Column(Enum(PayType), default=PayType.alipay)
price = Column(DECIMAL)
6. 新增數(shù)據(jù)
add_user = User("test", "1351232322")
session.add(add_user)
session.commit()
session.add()將會(huì)把Model加入當(dāng)前session維護(hù)的持久空間(可以從session.dirty看到)中线衫，直到commit時(shí)提交到數(shù)據(jù)庫(kù)。
- Q1：
add之后如何直接返回對(duì)象的屬性惑折？
可以在add之后執(zhí)行db.session.flush()授账，這樣便可在session中get到對(duì)象的屬性。
- Q2：如何進(jìn)行批量插入惨驶，性能比較白热？
批量插入共有以下幾種方法，對(duì)它們的批量做了比較粗卜，分別是：
session.add_all() < bulk_save_object()< bulk_insert_mappings() < SQLAlchemy_core()
7. 查詢數(shù)據(jù)
查詢是最常用的一個(gè)操作了屋确，舉個(gè)最簡(jiǎn)單的查詢例子：
# 檢索id = 1
user = session.query(Users).filter(id == 1).first()
# 檢索全部用戶
users = session.query(Users).all()
使用Join查詢
# 未使用外鍵
q = session.query(User).join(Order, User.id==Order.user_id)
# 方法2
q = session.query(User).join(Order, Order.user_id)
# 配置了外鍵
q = session.query(User).join(Order)
下一個(gè)例子使用SQL中的函數(shù)和Left join，檢索當(dāng)日下單用戶昵稱和訂單信息续扔。
from sqlalchemy.sql.functions import current_date
result = session.query(Order, User.nickname).outerjoin(
User, User.id == Order.user_id
).filter(
Order.order_type != 2,
sqlalchemy.func.date(Order.created_at) == current_date
).all()
使用 in 攻臀、or查詢語(yǔ)句。
# 檢索id在[1, 2, 3]中的用戶测砂。
r = session.query(User).filter(User.id.in_([1,2,3])).all()
# 使用or
query = bus_session.query(User)
filters = [User.id.in_([1, 2, 3]), User.nickname.ilike("ab%")]
r = query.filter(sqlalchemy.or_(*filters)).all()
8. 更新數(shù)據(jù)
更新數(shù)據(jù)有兩種方法茵烈，一種是使用query中的update方法：
session.query(User).filter_by(id=1).update({'name': "Jack"})
另一種是操作對(duì)應(yīng)的表模型：
user = session.query(User).filter_by(name="Jack").first()
user.name = "test"
session.add(users)
session.commit()
9. 刪除數(shù)據(jù)
和更新數(shù)據(jù)類似百匆，刪除數(shù)據(jù)也有兩種方法砌些，第一種：
user = session.query(User).filter(User.name == "test").first()
session.delete(user)
session.commit()
批量刪除的時(shí)候建議使用第二種：
session.query(User).filter(User.name == "test").delete()
session.commit()
