《大数据的Python基础》课件-第13章 数据库编程

第13章数据库编程学习目标

掌握利用DB-API访问关系数据库的方法掌握利用ORM方法实现数据库的对象关系映射22025/3/613.1关系数据库访问

关系数据库是建立在实体关系模型之上的数据库。

以一种小型关系数据库SQLite为例对关系数据库的访问方法加以介绍。SQLite是一种流行的文件型关系数据库，经常被集成到各种应用程序，应用场合涉及桌面系统和移动平台等各类嵌入式系统。32025/3/613.1.1数据库连接SQLite文件型数据库本身就是一个磁盘文件，不需要服务器进程，灵活方便、易于使用。

SQLite支持2TB大小的单个数据库，每个数据库完全存储在单个磁盘文件中，以B+树数据结构存储。SQLite遵循ACIDAtomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（持久性）等特性。

访问和操作SQLite数据时，需要导入sqlite3模块，它提供了与DB-API2.0规范相兼容的SQL接口。42025/3/613.1.1数据库连接

使用sqlite3模块时，应首先创建一个与数据库关联的连接（Connection），作为进一步数据库操作的基础。52025/3/6方法说明sqlite3.connect(database[,timeout,otheroptionalarguments])打开或创建数据库，建立连接，返回连接Connection的实例Connection.execute(sql[,optionalparameters])执行一个SQL语句Connection.executemany(sql[,parameters])执行多条SQL命令Connection.cursor([cursorClass])创建一个游标（cursor）Cmit()提交当前的事务Connection.rollback()回滚自上一次调用commit()以来对数据库所做的更改Connection.close()关闭数据库连接13.1.1数据库连接

在调用connect函数时指定SQLite数据库文件的磁盘路径，如果指定的数据库文件存在则打开这个数据库，否则会新创建一个数据库文件打开。SQLite也可以在内存中创建数据库，只要指定数据库文件为“:memory:”即可。62025/3/6conn1=sqlite3.connect("D:/test.db")#打开或创建E盘根目录下的文件test.dbconn2=sqlite3.connect(":memory:")#创建一个内存数据库13.1.2游标的使用

实现对数据库的访问操作，需要引入游标对象（Cursor）72025/3/6方法说明Cursor.execute(sql[,optionalparameters])执行sql语句Cursor.executemany(sql,seq_of_parameters)执行多条sql语句Cursor.executescript(sql_script)执行多条SQL命令Cursor.fetchone()从查询结果中取一条记录，并将游标指向下一条记录

Cursor.fetchmany([size=cursor.arraysize])从查询结果中取多条记录Cursor.fetchall()从查询结果中取出所有记录【例13-1】创建数据库表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】创建数据库表并插入数据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执行以上程序，得到结果如下：('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对象关系映射的常用工具，它实现了完整的企业级持久模型，可以实现高效和高性能的数据库访问设计。

Python在编程处理时采取了一切皆对象的原则，而数据库采取了实体关系的逻辑方法加以设计，一旦进行了对象模型与数据库关系模型的关系映射，就可以直接利用对象进行数据库操作，不必直接使用SQL数据库查询语言。182025/3/613.2.1数据库引擎

具体使用时首先要连接到数据库上，并创建数据库引擎。

要采用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()函数创建数据库引擎。【例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】建立数据库引擎并生成数据库表

执行完毕后，可以在MySQL的Workbench中查看所创建的user表。242025/3/6【例13-4】建立数据库引擎并生成数据库表252025/3/6Base.metadata.create_all(engine)语句创建数据库表BaseModel.metadata.drop_all(engine)语句删除数据库表13.2.2数据库的映射与绑定

对于数据库的更多操作，需要采用数据库会话对象。建立数据库表的元数据与表结构之间的映射，同时实现会话类与数据库引擎的绑定，如Session_class=sessionmaker(bind=engine)

其中返回的数据Session_class为会话类。

使用会话时，首先为会话类创建一个实例，然后就可以以会话实例为基础，再结果表结构类的实例，进行更多的表操作。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