《大數據的Python基礎》課件-第13章 數據庫編程

第13章數據庫編程學習目標

掌握利用DB-API訪問關系數據庫的方法掌握利用ORM方法實現數據庫的對象關系映射22025/3/613.1關系數據庫訪問

關系數據庫是建立在實體關系模型之上的數據庫。

以一種小型關系數據庫SQLite為例對關系數據庫的訪問方法加以介紹。SQLite是一種流行的文件型關系數據庫，經常被集成到各種應用程序，應用場合涉及桌面系統(tǒng)和移動平臺等各類嵌入式系統(tǒng)。32025/3/613.1.1數據庫連接SQLite文件型數據庫本身就是一個磁盤文件，不需要服務器進程，靈活方便、易于使用。

SQLite支持2TB大小的單個數據庫，每個數據庫完全存儲在單個磁盤文件中，以B+樹數據結構存儲。SQLite遵循ACIDAtomicity（原子性）、Consistency（一致性）、Isolation（隔離性）、Durability（持久性）等特性。

訪問和操作SQLite數據時，需要導入sqlite3模塊，它提供了與DB-API2.0規(guī)范相兼容的SQL接口。42025/3/613.1.1數據庫連接

使用sqlite3模塊時，應首先創(chuàng)建一個與數據庫關聯(lián)的連接（Connection），作為進一步數據庫操作的基礎。52025/3/6方法說明sqlite3.connect(database[,timeout,otheroptionalarguments])打開或創(chuàng)建數據庫，建立連接，返回連接Connection的實例Connection.execute(sql[,optionalparameters])執(zhí)行一個SQL語句Connection.executemany(sql[,parameters])執(zhí)行多條SQL命令Connection.cursor([cursorClass])創(chuàng)建一個游標（cursor）Cmit()提交當前的事務Connection.rollback()回滾自上一次調用commit()以來對數據庫所做的更改Connection.close()關閉數據庫連接13.1.1數據庫連接

在調用connect函數時指定SQLite數據庫文件的磁盤路徑，如果指定的數據庫文件存在則打開這個數據庫，否則會新創(chuàng)建一個數據庫文件打開。SQLite也可以在內存中創(chuàng)建數據庫，只要指定數據庫文件為“:memory:”即可。62025/3/6conn1=sqlite3.connect("D:/test.db")#打開或創(chuàng)建E盤根目錄下的文件test.dbconn2=sqlite3.connect(":memory:")#創(chuàng)建一個內存數據庫13.1.2游標的使用

實現對數據庫的訪問操作，需要引入游標對象（Cursor）72025/3/6方法說明Cursor.execute(sql[,optionalparameters])執(zhí)行sql語句Cursor.executemany(sql,seq_of_parameters)執(zhí)行多條sql語句Cursor.executescript(sql_script)執(zhí)行多條SQL命令Cursor.fetchone()從查詢結果中取一條記錄，并將游標指向下一條記錄

Cursor.fetchmany([size=cursor.arraysize])從查詢結果中取多條記錄Cursor.fetchall()從查詢結果中取出所有記錄【例13-1】創(chuàng)建數據庫表employee并插入員工的數據

為數據庫建立了一個company表，并為該表插入了3條數據，通過數據庫連接的commit()方法。82025/3/6importsqlite3conn=sqlite3.connect('employee.db')c=conn.cursor()c.execute('''CREATETABLEemployee(idINTPRIMARYKEYNOTNULL,nameTEXTNOTNULL,ageINTNOTNULL,addressCHAR(50),salaryREAL);''')c.execute("INSERTINTOemployee(id,name,age,address,salary)VALUES(1,'張三',25,'北京',5000.00)")【例13-1】創(chuàng)建數據庫表并插入數據92025/3/6c.execute("INSERTINTOemployee(id,name,age,address,salary)VALUES(2,'李四',30,'上海',6000.00)")c.execute("INSERTINTOemployee(id,name,age,address,salary)VALUES(3,'王五',23,'廣州',3000.00)")mit()conn.close()print("done")【例13-2】更新和刪除數據庫表employee，并進行表數據的查詢

刪除記錄2，并修改記錄1的salary為4000102025/3/6importsqlite3conn=sqlite3.connect('employee.db')c=conn.cursor()print(c)c.execute('UPDATEemployeeSETsalary=4000.00WHEREid=1')mit()cursor=c.execute('SELECTid,name,address,salaryFROMemployee')print(cursor)print('-'*50)【例13-2】更新和刪除數據庫表employee，并進行表數據的查詢112025/3/6forrowincursor:print('id=',row[0],end=',')print('name=',row[1],end=',')print('address=',row[2],end=',')print('salary=',row[3])c.execute('DELETEFROMemployeeWHEREID=2;')mit()cursor=c.execute('SELECTid,name,address,salaryFROMemployee')print('-'*50)print(c.fetchall())conn.close()【例13-2】更新和刪除數據庫表employee，并進行表數據的查詢

查詢數據庫記錄查看結果122025/3/6<sqlite3.Cursorobjectat0x0000013D9C36EEA0><sqlite3.Cursorobjectat0x0000013D9C36EEA0>--------------------------------------------------

id=1,name=張三,address=北京,salary=4000.0id=3,name=王五,address=廣州,salary=3000.0--------------------------------------------------[(1,'張三','北京',4000.0),(3,'王五','廣州',3000.0)]13.1.3行對象

行對象Row提供了更加細致的數據映射方法，可以通過索引或鍵值訪問列數據，實現了數據的方便訪問和使用。要使用行對象，只要為數據庫連接實例設置其成員row_factory為sqlite3.Row即可。132025/3/6【例13-3】建立一個內存數據庫表，觀察其行對象142025/3/6importsqlite3conn=sqlite3.connect(':memory:')persons=[('Alice','manager'),('Bob','engineer'),('Mike','scientist')]conn.execute('CREATETABLE\ person(name,title)')conn.executemany('INSERTINTO\ person(name,title)VALUES(?,?)', persons)forrowinconn.execute('SELECT\ name,titleFROMperson'):print(row)mit()conn.row_factory=sqlite3.Rowc=conn.cursor()cursor=c.execute('SELECT\ name,titleFROMperson')print('-'*50)r=cursor.fetchone()print(r)print('-'*50)print(r.keys())print(len(r))print('-'*50)foriinr:print(i,end='\t')print('\n'+'-'*50)forjinr.keys():print(j,r[j],end='\t')conn.close()【例13-3】建立一個內存數據庫表，觀察其行對象152025/3/6執(zhí)行以上程序，得到結果如下：('Alice','manager')('Bob','engineer')('Mike','scientist')--------------------------------------------------<sqlite3.Rowobject at0x00000244B0E00C10>--------------------------------------------------['name','title']2--------------------------------------------------Alice manager --------------------------------------------------nameAlice titlemanager 13.2對象關系映射

對象關系映射（ObjectRalationalMapping，ORM）是一種用于將對象模型表示的對象映射到基于SQL的關系數據庫結構的方法。

一旦完成了對象與數據庫表的映射，就可以單純依賴于簡單的對象屬性和方法來實現數據庫操作。162025/3/613.2對象關系映射

以最為常用的關系型數據庫MySQL為例進行ORM用法的介紹。

安裝MySQL數據庫可直接從官方網站下載CommunityServer5.6.x或更高的版本。172025/3/613.2.1數據庫引擎SQLAlchemy是進行ORM對象關系映射的常用工具，它實現了完整的企業(yè)級持久模型，可以實現高效和高性能的數據庫訪問設計。

Python在編程處理時采取了一切皆對象的原則，而數據庫采取了實體關系的邏輯方法加以設計，一旦進行了對象模型與數據庫關系模型的關系映射，就可以直接利用對象進行數據庫操作，不必直接使用SQL數據庫查詢語言。182025/3/613.2.1數據庫引擎

具體使用時首先要連接到數據庫上，并創(chuàng)建數據庫引擎。

要采用SQLAlchemy進行數據庫連接，需要進行以下安裝：192025/3/6pip3installsqlalchemypipinstallmysql-connector13.2.1數據庫引擎數據庫連接字符串MicrosoftSQLServer'mssql+pymssql://[user]:[pass]@[domain]:[port]/[dbname]'MySQL'mysql+mysqlconnector://[user]:[pass]@[domain]:[port]/[dbname]'或'mysql+pymysql://[user]:[pass]@[domain]:[port]/[dbname]'Oracle'oracle+cx_oracle://[user]:[pass]@[domain]:[port/[dbname]]'PostgreSQL'postgresql+psycopg2://[user]:[pass]@[domain]:[port]/[dbname]'SQLite'sqlite://[file_pathname]'202025/3/613.2.1數據庫引擎212025/3/6

采用安裝sqlalchemy模塊和mysql-connector數據庫驅動模塊的方法。

要進行數據庫連接，還需要指出數據庫用戶名、口令、所在機器的IP地址以及數據庫服務引擎的端口號、數據庫名稱等,如

mysql+mysqlconnector://root:123456@localhost:3306/test?charset=utf8

指出了要連接本機3306端口號的test數據庫。

可以通過sqlalchemy模塊中的create_engine()函數創(chuàng)建數據庫引擎。【例13-4】建立數據庫引擎并生成數據庫表

擁有數據庫引擎后，可以通過繼承ORM基類的方式開始ORM類的定義，在類中利用預設置的__tablename__成員變量指定表名。通過設置成員名的值為一個Column()實例的方式即可生成表中字段的對象。222025/3/6【例13-4】建立數據庫引擎并生成數據庫表232025/3/6fromsqlalchemyimportcreate_enginefromsqlalchemy.ext.declarativeimportdeclarative_basefromsqlalchemyimportColumn,Integer,Stringengine=create_engine( 'mysql+mysqlconnector://root:123456@localhost:3306/test?charset=utf8',encoding='utf-8')Base=declarative_base() classUser(Base):__tablename__='user' id=Column(Integer,primary_key=True)

name=Column(String(32))password=Column(String(16))Base.metadata.create_all(engine) 【例13-4】建立數據庫引擎并生成數據庫表

執(zhí)行完畢后，可以在MySQL的Workbench中查看所創(chuàng)建的user表。242025/3/6【例13-4】建立數據庫引擎并生成數據庫表252025/3/6Base.metadata.create_all(engine)語句創(chuàng)建數據庫表BaseModel.metadata.drop_all(engine)語句刪除數據庫表13.2.2數據庫的映射與綁定

對于數據庫的更多操作，需要采用數據庫會話對象。建立數據庫表的元數據與表結構之間的映射，同時實現會話類與數據庫引擎的綁定，如Session_class=sessionmaker(bind=engine)

其中返回的數據Session_class為會話類。

使用會話時，首先為會話類創(chuàng)建一個實例，然后就可以以會話實例為基礎，再結果表結構類的實例，進行更多的表操作。262025/3/6【例13-5】添加數據庫數據272025/3/6fromsqlalchemyimportcreate_enginefromsqlalchemyimportTable,MetaData,Column,Integer,Stringfromsqlalchemy.ormimportmapper,sessionmakerengine=create_engine( "mysql+mysqlconnector://root:123456@localhost/test",encoding='utf-8',echo=True)metadata=MetaData()user=Table('user',metadata, Column('id',Integer,primary_key=True), Column('name',String(32)), Column('password',String(32)) ) 【例13-5】添加數據庫數據282025/3/6classUser(object): def__init__(self,name,id,password):self.id=id=nameself.password=passwordmapper(User,user) Session_class=sessionmaker(bind=engine) session=Session_class() user_obj=User(id=1,name="mary",password="123456")session.add(user_obj) user_obj=User(id=2,name="kate",password="123456")session.add(user_obj) mit() session.close() 【例13-5】添加數據庫數據292025/3/6INFOsqlalchemy.engine.base.EngineSHOWVARIABLESLIKE'sql_mode'INFOsqlalchemy.engine.base.Engine{}INFOsqlalchemy.engine.base.EngineSHOWVARIABLESLIKE'lower_case_table_names'INFOsqlalchemy.engine.base.Engine{}INFOsqlalchemy.engine.base.EngineSELECTDATABASE()INFOsqlalchemy.engine.base.Engine{}INFOsqlalchemy.engine.base.EngineSELECTCAST('testplainreturns'ASCHAR(60))ASanon_1INFOsqlalchemy.engine.base.Engine{}INFOsqlalchemy.engine.base.EngineSELECTCAST('testunicodereturns'ASCHAR(60))ASanon_1INFOsqlalchemy.engine.base.Engine{}INFOsqlalchemy.engine.base.EngineBEGIN(implicit)INFOsqlalchemy.engine.base.EngineINSERTINTOuser(id,name,password)VALUES(%(id)s,%(name)s,%(password)s)INFOsqlalchemy.engine.base.Engine({'id':1,'name':'mary','password':'123456'},{'id':2,'name':'kate','password':'123456'})INFOsqlalchemy.engine.base.EngineCOMMIT【例13-5】添加數據庫數據

要通過會話實現數據的查詢，需要利用query()方法，將會在User類所對應的user表中以id=2為條件進行查詢，并取其結果中的第一個行數據賦值給user。302025/3/6user=session.query(User).filter_by(id=2).first()【例13-5】添加數據庫數據

要實現行數據的修改，只需要直接設置如user.password=‘abcdef’，進行會話的提交（mit()）以后即已經實現了數據的修改。也可以對filter結果上運用update()和delete()方法進行更新和刪除。312025/3/6session.query(User).filter(User.id==1).update({'name':'admin'})session.query(User).filter(User.id==2).delete()【例13-6】查詢和修改數據庫數據322025/3/6fromsqlalchemyimportcreate_enginefromsqlalchemyimportTable,MetaData,Column,Integer,Stringfromsqlalchemy.ormimportmapper,sessionmakerengine=create_engine("mysql+mysqlconnector://root:123456@localhost/test",encoding='utf-8')metadata=MetaData()user=Table('user',metadata,Column('id',Integer,primary_key=True),Column('name',String(32)),Column('password',String(32)))classUser(object):def__init__(self,name,id,password):self.id=id=nameself.pas