一、簡介
sqlalchemy是python中開源的orm框架，用於簡化在python中對資料庫的操作。這裡對sqlalchemy中的常用操作進行介紹。
二、準備
2.1 官網地址
官網地址:https://docs.sqlalchemy.org/en/14/
2.2 依賴包安裝
pip3 install sqlalchemy
三、使用
3.1 建立對映物件
定義物件類，屬性欄位與對應表字段關聯，同時可指定預設值等屬性，示例如下：
# 基類
Base = declarative_base()
class Stu(Base):
# 設定表名
__tablename__ = 'stu'
# 屬性名與表中欄位名對映
id = Column('id', Integer, primary_key=True)
no = Column('no', String)
# server_default為預設值,text為文字表示
stuName = Column('stu_name', String, server_default='')
createTime = Column('create_time', DateTime, server_default=text('NOW()'))
# 返回物件的字串表示
def __repr__(self) -> str:
return str(self.__dict__)
3.2 建立資料庫連線
指定要連線的資料庫，建立會話，同時可根據需要設定相關引數，示例如下：
# 連線地址,格式為 mysql+pymysql://[賬號]:[密碼]@[主機]:[埠]/[資料庫]?charset=utf8"
url = 'mysql+pymysql://root:[email protected]:3306/school?charset=utf8'
# echo為True時，列印sql,可用於除錯
engine = create_engine(url, echo=False, encoding='utf-8', pool_size=5)
sessionClass = sessionmaker(bind=engine)
# 建立會話
session = sessionClass()
# 使用完後關閉會話
# session.close()
3.3 查詢
3.3.1 常用查詢
這裡列中常用查詢，包含排序、分組、條件查詢、個數查詢、單個查詢、列表查詢等，具體請看示例程式碼：
# 查所有,並排序
stuList = session.query(Stu).order_by(Stu.id).all()
# 查詢指定屬性並遍歷,方式一
for id, no in session.query(Stu.id, Stu.no):
print("id:{} no:{}".format(id, no))
# 查詢指定屬性並遍歷,方式二
for t in session.query(Stu.id, Stu.no).all():
print("id:{} no:{}".format(t.id, t.no))
# 查所有,並排序
stuList = session.query(Stu).order_by(Stu.id).all()
# 分頁查詢
stuList = session.query(Stu).limit(2).offset(0).all()
# 查詢個數
stuList = session.query(Stu).count()
# 查詢個數，scalar（）表示返回第一個結果的第一個元素
cnt = session.query(func.count('*')).select_from(Stu).scalar()
# 查詢個數,帶條件統計
cnt = session.query(func.count('*')).select_from(Stu).filter(Stu.id>2).scalar()
# 根據id統計
cnt = session.query(func.count(Stu.id)).scalar()
# 指定條件查詢
stuList = session.query(Stu).filter(Stu.id > 2, Stu.createTime < datetime.datetime.now()).all()
# 獲取第一個,沒有則拋異常
# stu = session.query(Stu).filter(Stu.id==2).one()
# 獲取第一個,沒有則返回None
stu = session.query(Stu).filter(Stu.id == 1).first()
# 獲取第一個,沒有則返回None
stu = session.query(Stu).filter(Stu.id == 2).one_or_none()
# group by查詢
stuList = session.query(func.count('*'), Stu.stuName).filter(or_(Stu.id > 2, Stu.stuName == 'apple1')).group_by(
Stu.stuName).all()
3.3.2 條件查詢
這裡列出常用的條件查詢，示例如下：
# 相等查詢
stuList = session.query(Stu).filter(Stu.id == 2).all()
# 不等查詢
stuList = session.query(Stu).filter(Stu.id != 2).all()
# 大於查詢
stuList = session.query(Stu).filter(Stu.id > 2).all()
# in查詢
stuList = session.query(Stu).filter(Stu.id.in_([82, 83])).all()
# not in 查詢
stuList = session.query(Stu).filter(~Stu.id.in_([82, 83])).all()
# 巢狀查詢
stuList = session.query(Stu).filter(Stu.id.in_(session.query(Stu.id).filter(Stu.id <= 83))).all()
# like查詢(不同後端時,有時大小寫不敏感)
stuList = session.query(Stu).filter(Stu.stuName.like('Apple%')).all()
# like查詢(明確大小寫不敏感)
stuList = session.query(Stu).filter(Stu.stuName.ilike('Apple%')).all()
# null查詢
stuList = session.query(Stu).filter(Stu.stuName.is_(None)).all()
# not null查詢
stuList = session.query(Stu).filter(Stu.stuName.isnot(None)).all()
# and查詢
stuList = session.query(Stu).filter(Stu.id > 2, Stu.stuName == 'apple1').all()
# or查詢
stuList = session.query(Stu).filter(or_(Stu.id > 2, Stu.stuName == 'apple1')).all()
# 多級過濾
stuList = session.query(Stu).filter(Stu.id > 2).filter(Stu.stuName == 'apple1').all()
3.3.3 直接sql語句查詢
除了sqlalchemy帶的查詢方法，還可直接使用sql語句查詢，使用如下：
# 直接使用sql語句查詢(只有條件sql語句)
stuList = session.query(Stu).filter(text("id>2 and stu_name='apple1'")).order_by(text("id")).all()
# 直接使用sql語句查詢(完整的sql語句)
stuList = session.query(Stu).from_statement(
text("select * from stu where id>:id and stu_name=:name order by id").params(id=2, name='apple1')).all()
3.4 插入
3.4.1 單條插入
# 單條插入
stu = Stu(no='86', stuName='apple86', createTime=datetime.datetime.now())
session.add(stu)
session.commit()
3.4.2 批次插入
# 批次插入
stuList = [Stu(id=83, no='83', stuName='apple83', createTime=datetime.datetime.now()), Stu(id=84, no='84', stuName='apple84')]
session.add_all(stuList)
session.commit()
3.4.3 使用sql語句插入
# 直接使用sql語句,用Stu物件轉dict插入
stu = Stu(id=91, no='2', stuName='tree4', createTime=datetime.datetime.now())
session.execute("insert into stu(no, stu_name) value(:no, :stuName)", stu.__dict__)
# 直接使用sql語句,用stu對映
stu_obj = {'id': 3, 'no': '3', 'stuName': 'tree3', 'createTime': datetime.datetime.now()}
session.execute("insert into stu(no, stu_name) value(:no, :stuName)", stu_obj)
3.4.4 忽略已存在的插入
若資料已經存在，則不插入，否則直接插入。
# 直接使用sql語句,用stu對映,若物件已存在,則忽略
stu = Stu(id=91, no='2', stuName='tree4', createTime=datetime.datetime.now())
session.execute("insert ignore into stu(no, stu_name) value(:no, :stuName)", stu.__dict__)
3.4.5 更新插入
若資料已經存在，則做更新操作，否則直接插入。
# 直接使用sql語句,若物件已存在,則更新
stu = Stu(id=91, no='2', stuName='tree4', createTime=datetime.datetime.now())
session.execute("insert into stu(no, stu_name) value(:no, :stuName) on duplicate key update stu_name=values(stu_name)", stu.__dict__)
3.5 更新
更新時，流程是先查出物件,再對物件修改,最後直接commit。使用示例如下：
# 更新,流程:先查出物件,再對物件修改,最後直接commit
stu = session.query(Stu).filter(Stu.id==82).first()
stu.stuName ='rice1'
session.commit()
# 更新(根據條件可批次更新),流程:查物件同時進行修改,最後直接commit
session.query(Stu).filter(Stu.id >= 85).update({Stu.stuName: 'tree1'})
session.commit()
stu = session.query(Stu).filter(Stu.id==83).first()
session.delete(stu)
session.commit()
session.query(Stu).filter(Stu.id >= 85).delete()
session.commit()
四、結尾
上面列出了sqlalchemy中的常用部分操作，更多操作可檢視官網或原始碼。