Python操作MySql——使用SQLAlchemy ORM操作數(shù)據(jù)庫

乙甲壬 2020-07-18

展開全文

1 基礎(chǔ)知識介紹

1.1 ORM框架介紹

ORM（Object Ralational Mapping，對象關(guān)系映射），用來把對象模型表示的對象映射到基于S Q L 的關(guān)系模型數(shù)據(jù)庫結(jié)構(gòu)中去。我們在具體的操作實(shí)體對象的時候，就不需要再去和復(fù)雜的 SQ L 語句打交道，只需簡單的操作實(shí)體對象的屬性和方法。

常見的ORM框架

SQLAlchemy:SQLAlchemy 采用了數(shù)據(jù)映射模式，其工作單元主要使得有必要限制所有的數(shù)據(jù)庫操作代碼到一個特定的數(shù)據(jù)庫session，在該session中控制每個對象的生命周期。

SQLObject:是一種流行的對象關(guān)系管理器，用于為數(shù)據(jù)庫提供對象接口，其中表為類，行為實(shí)例，列為屬性。SQLObject包含一個基于Python對象的查詢語言，使SQL更抽象，并為應(yīng)用程序提供了大量的數(shù)據(jù)庫獨(dú)立性。

Storm :是一個介于單個或多個數(shù)據(jù)庫與Python之間映射對象的 Python ORM 。為了支持動態(tài)存儲和取回對象信息，它允許開發(fā)者構(gòu)建跨數(shù)據(jù)表的復(fù)雜查詢。Stom中 table class 不需要是框架特定基類的子類。每個table class是的sqlobject.SQLObject 的子類。

Django's ORM : 因?yàn)镈jango的ORM 是緊嵌到web框架的，所以就算可以也不推薦，在一個獨(dú)立的非Django的Python項(xiàng)目中使用它的ORM。Django，一個最流行的Python web框架，有它獨(dú)有的 ORM。相比 SQLAlchemy， Django 的 ORM 更吻合于直接操作SQL對象，操作暴露了簡單直接映射數(shù)據(jù)表和Python類的SQL對象。

1.2 SQLAlchemy介紹

sqlalchemy是Python ORM的開源框架，使用它可以快速方便的構(gòu)建數(shù)據(jù)庫模型

SQLAlchemy框架

Python操作MySql——使用SQLAlchemy ORM操作數(shù)據(jù)庫

Engine：框架的引擎
Connection Pooling ：數(shù)據(jù)庫連接池
Dialect ：選擇連接數(shù)據(jù)庫的DB API類型
Schema / Type ：架構(gòu)和類型
SQL Expression Language： SQL表達(dá)式語言

SQLALchemy本身無法操作數(shù)據(jù)庫，需要依賴pymysql第三方模塊，Dialect用于和數(shù)據(jù)API進(jìn)行交流，根據(jù)配置文件的不同調(diào)用不同的數(shù)據(jù)庫API，從而實(shí)現(xiàn)對數(shù)據(jù)庫的操作

使用pymysql連接數(shù)據(jù)庫格式：

mysql + pymysql: / / <username>:<password>@<host> / <dbname>[?<options>]

2 使用SQLAlchemy

2.1安裝SQLAlchemy與檢查是否安裝成功

#安裝pip install SQLAlchemy#檢查是否安裝成功C:\Users\lsl\Desktop>pythonPython 3.7.0rc1 (v3.7.0rc1:dfad352267, Jun 12 2018, 07:05:25) [MSC v.1914 64 bit (AMD64)] on win32Type 'help', 'copyright', 'credits' or 'license' for more information.>>> import sqlalchemy>>> sqlalchemy.__version__'1.3.18'復(fù)制代碼

2.2使用SQLAlchemy對數(shù)據(jù)庫的表進(jìn)行操作

2.2.1創(chuàng)建連接對象也就是為了連接到本地的數(shù)據(jù)庫

create_engine中的字段的意義介紹：

engine = create_engine(' dialect+driver://username:password@host:port/database ')

dialect -- 數(shù)據(jù)庫類型

driver -- 數(shù)據(jù)庫驅(qū)動選擇（我的數(shù)據(jù)庫驅(qū)動選擇是pymysql，默認(rèn)會調(diào)用MySQLdb，如果運(yùn)行的時候提示ImportError: No module named 'MySQLdb'/ImportError: No module named 'pymysql'，則意味著沒有安裝你想使用的數(shù)據(jù)驅(qū)動，安裝命令：pip install pymysql）

username -- 數(shù)據(jù)庫用戶名

password -- 用戶密碼

host 服務(wù)器地址

port 端口

database 數(shù)據(jù)庫

創(chuàng)建連接：

from sqlalchemy import create_engine# 連接本地test數(shù)據(jù)庫engine = create_engine('mysql+pymysql://root:root@localhost:3306/testdab',#數(shù)據(jù)庫類型是mysql，采用pymysql數(shù)據(jù)庫驅(qū)動來連接，用戶名是root，密碼也是root，連接本地數(shù)據(jù)庫testdab（連接的數(shù)據(jù)庫要是已存在的，就是你本地已有的數(shù)據(jù)庫）                       encoding='utf-8',  # 編碼格式                       echo=True,  # 是否開啟sql執(zhí)行語句的日志輸出                       pool_recycle=-1,  # 多久之后對線程池中的線程進(jìn)行一次連接的回收（重置） （默認(rèn)為-1）,其實(shí)session并不會被close                       poolclass=NullPool  # 無限制連接數(shù)                       )復(fù)制代碼

2.2.2 簡單查詢——使用SQL語句

result = engine.execute('select * from students')//在execute()里的參數(shù)是查詢的sql語句print(result.fetchall()) //打印出查詢的結(jié)果復(fù)制代碼

2.2.3 創(chuàng)建映射

創(chuàng)建映射后我們就可以減少sql語句對數(shù)據(jù)庫的操作，而是通過操作我們建立的與數(shù)據(jù)庫中的數(shù)據(jù)表的class來對數(shù)據(jù)庫中的數(shù)據(jù)進(jìn)行操作。

創(chuàng)建一個py文件來做數(shù)據(jù)表的映射text2.py

#引入要使用的declarative_basefrom sqlalchemy.ext.declarative import declarative_base#在要映射的數(shù)據(jù)表students中有id，name兩個字段，所以要引入Integer對應(yīng)id，String對應(yīng)namefrom sqlalchemy import Column, Integer, String#聲名BaseBase = declarative_base()#User類就是對應(yīng)于 __tablename__ 指向的表，也就是數(shù)據(jù)表students的映射class User(Base):#students表是我本地數(shù)據(jù)庫testdab中已存在的    __tablename__ = 'students'    id = Column(Integer, primary_key=True, autoincrement=True)    name = Column(String(64),nullable=False)    __table_args__ = {        'mysql_charset': 'utf8'    }復(fù)制代碼

在要進(jìn)行操作之前還要創(chuàng)建一下會話，Session的主要目的是建立與數(shù)據(jù)庫的會話，它維護(hù)你加載和關(guān)聯(lián)的所有數(shù)據(jù)庫對象。它是數(shù)據(jù)庫查詢（Query）的一個入口。在Sqlalchemy中，數(shù)據(jù)庫的查詢操作是通過Query對象來實(shí)現(xiàn)的。而Session提供了創(chuàng)建Query對象的接口。

# 創(chuàng)建會話session = sessionmaker(engine)mySession = session()復(fù)制代碼

接下來，我們就可以通過操作User類來操作數(shù)據(jù)表students

2.2.4 查詢

查詢students表中所有的數(shù)據(jù)

result = mySession.query(News).all()print(result[0])復(fù)制代碼

查詢students表中第一條數(shù)據(jù)

result = mySession.query(User).first()print(result.name) #打印對象屬性復(fù)制代碼

通過id查詢數(shù)據(jù)（id=2）

result = mySession.query(User).filter_by(id=2).first()print(result.name)復(fù)制代碼

自定義過濾條件

result = mySession.query(User).filter(text('id>:id')).params(id=2).all()復(fù)制代碼

根據(jù)主鍵查詢

result = mySession.query(User).get(2)復(fù)制代碼

其他查詢操作介紹

all() 返回查詢到的所有的結(jié)果。這個方法比較危險的地方是，如果數(shù)據(jù)量大且沒有使用 limit 子句限制的話，所有的結(jié)果都會加載到內(nèi)存中。它返回的是一個列表，如果查詢不到任何結(jié)果，返回的是空列表。
first() 返回查詢到的第一個結(jié)果， 如果沒有查詢到結(jié)果，返回 None 。
.scalar() 這個方法與 .one_or_none() 的效果一樣。如果查詢到很多結(jié)果，拋出 sqlalchemy.orm.exc.MultipleResultsFound 異常。如果只有一個結(jié)果，返回它，沒有結(jié)果返回 None 。
one() 如果只能查詢到一個結(jié)果，返回它，否則拋出異常。沒有結(jié)果時拋 sqlalchemy.orm.exc.NoResultFound ，有超過一個結(jié)果時拋 sqlalchemy.orm.exc.MultipleResultsFound 。
one_or_none() 比起 one() 來，區(qū)別只是查詢不到任何結(jié)果時不再拋出異常而是返回 None 。
get() 這是個比較特殊的方法。它用于根據(jù)主鍵來返回查詢結(jié)果，因此它有個參數(shù)就是要查詢的對象的主鍵。如果沒有該主鍵的結(jié)果返回 None ，否則返回這個結(jié)果。

2.2.5 增加數(shù)據(jù)(添加一條數(shù)據(jù) name=”小紅“)，注意要commit

user = User(name='小紅')mySession.add(user)mySession.commit()復(fù)制代碼

2.2.6刪除數(shù)據(jù)（根據(jù)id進(jìn)行修改）

mySession.query(User).filter(User.id == 1).delete()mySession.commit()復(fù)制代碼

2.2.7修改數(shù)據(jù)（修改一條數(shù)據(jù)，把小紅的名字修改成小白）

mySession.query(User).filter(User.name=='小紅').update({'name':'小白'})mySession.commit()復(fù)制代碼

3 常用條件查詢代碼

表名：User1.條件查詢session.query(User).filter(User.name=='張三'){                                               .all()   查詢所有                                               .one()   查詢單個（如果存在多個會異常）                                               .first() 查詢符合條件的第一個                                               .limit(1).one() limit限制查詢，limit(1).one()升級第一個                                               .count()  查詢符合條件的總個數(shù)                                               }2.主鍵查詢session.query(User).get(0)  查詢主鍵ID=03.offset(n) 限制前面n個，顯示后面n+1個#查詢出第三個后面的所有session.query(User).offset(3).all()4.slice()切片#slice(1,3) 與python的slice一致，從0開始 左閉右開，顯示1,2兩個元素session.query(User).slice(1,,3).all()5.order_by() 默認(rèn)升序session.query(User).order_by(User.id).all()6.desc() 降序session.query(User).order_by(desc(User.id)).all()7.like 模糊匹配，與sql一樣session.query(User).filter(User.neme.like('%吳')).add()8.notlike 與7相反form operator import *9.in_() 包含#查詢是否包含唐人、吳新喜這個用戶的信息session.query(User).filter(User.name.in_(['唐人','吳新喜'])).all()10.notin_() 不包含11.is_  兩種表達(dá)方式 None#查詢所有手機(jī)號為null的信息session.query(User).filter(User.phone==None).all()session.query(User).filter(User.phone.is_(None)).all()12. isnot()13. or_ 條件或者關(guān)系#查詢name==吳新喜或者唐人的用戶信息session.query(User).filter(or_(User.name=='唐人',User.name=='吳新喜'))聚合函數(shù)1.count group_by#查詢所有的密碼并且計算其相同的個數(shù)from sqlalchemy import funcssession.query(db_user.psw,func.count(db_user.psw)).group_by(db_user.psw).all()2.havinghaving字句可以讓我們篩選成組后的各種數(shù)據(jù)，where字句在聚合前先篩選記錄，也就是說作用在group by和having字句前。而having子句在聚合后對組記錄進(jìn)行篩選。真實(shí)表中沒有此數(shù)據(jù)，這些數(shù)據(jù)是通過一些函數(shù)生存。即先成組在篩選#查詢所有的密碼并且計算其相同的個數(shù),having條件相同密碼總數(shù)大于1的數(shù)據(jù)ssession.query(db_user.psw,func.count(db_user.psw)).group_by(db_user.psw).having(func.count(db_user.psw)>1).all()3.sum#計算所有id的總和ssession.query(func.sum(db_user.id)).all()4.max#最大的ID ssession.query(func.max(db_user.id)).all()5.min#最小的id ssession.query(func.min(db_user.id)).all()6.lable 別名lable別名不能用在having中7.extract 提取時間元素from sqlalchemy import extract復(fù)制代碼

4 總的代碼

#importfrom sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.pool import NullPool#創(chuàng)建連接對象也就是為了連接到本地的數(shù)據(jù)庫engine = create_engine('mysql+pymysql://root:root@localhost:3306/testdab',                       encoding='utf-8',  # 編碼格式                       echo=True,  # 是否開啟sql執(zhí)行語句的日志輸出                       pool_recycle=-1,  # 多久之后對線程池中的線程進(jìn)行一次連接的回收（重置） （默認(rèn)為-1）,其實(shí)session并不會被close                       poolclass=NullPool  # 無限制連接數(shù)                       )		#聲名BaseBase = declarative_base()# 創(chuàng)建會話session = sessionmaker(engine)mySession = session()		# 創(chuàng)建類，繼承基類，用基本類型描述數(shù)據(jù)庫結(jié)構(gòu)class User(Base):    __tablename__ = 'students'    id = Column(Integer, primary_key=True, autoincrement=True)    name = Column(String(64),nullable=False)    __table_args__ = {        'mysql_charset': 'utf8'    }	#sql語句查詢result = engine.execute('select * from students')print(result.fetchall()) # 查詢第一條result = mySession.query(User).first()print(result.name) #打印對象屬性# 查詢所有result = mySession.query(User).all()print(result[0])# 查詢id為2的result = mySession.query(User).filter_by(id=2).first()print(result.name)# 分頁查詢 0,2result = mySession.query(User).filter(User.id>1).limit(2).offset(0).all()print(result)#插入新數(shù)據(jù)user = User(name='小紅')mySession.add(user)mySession.commit()result = mySession.query(User).filter_by(name='小紅').first()print(result.name)#修改已有數(shù)據(jù)mySession.query(User).filter(User.name=='小紅').update({'name':'小白'})mySession.commit()result = mySession.query(User).filter_by(name='小白').first()print(result.name)#刪除數(shù)據(jù)mySession.query(User).filter(User.id == 1).delete()mySession.commit()result = mySession.query(User).first()print(result.name) #打印對象屬性復(fù)制代碼

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報。

一区二区三区日韩精品-日韩经典一区二区三区-五月激情综合丁香婷婷-欧美精品中文字幕专区