《Python数据库操作》课件

数据库操作Python欢迎学习数据库操作课程！本课程将系统地介绍如何使用连接Python Python和操作各种数据库系统，从基础的到企业级的和，SQLite MySQL PostgreSQL甚至非关系型数据库如MongoDB通过本课程，你将掌握数据库连接、基本操作、事务处理、性能优化等核心技能，这些都是后端开发和数据分析工作中不可或缺的能力我们还将通过一个完整的图书管理系统案例，将所学知识应用到实际项目中无论你是刚开始学习的新手，还是想要提升数据库操作技能的有经验开Python发者，这门课程都将为你提供系统而实用的指导数据库基础回顾关系型数据库非关系型数据库关系型数据库是基于关系模型的数据库，以表格的形式存储数据，非关系型数据库（）不使用表格模型存储数据，而是采用NoSQL每个表格由行和列组成每行代表一个记录，每列代表记录的一其他数据结构如文档、键值对、列族或图形个属性主要特点包括灵活的数据模型、高扩展性、分布式架构、适合主要特点包括结构化的数据存储、事务支持、查询语处理大数据等ACID SQL言、表间关系等文档型，存储•MongoDB JSON开源、高性能、广泛应用•MySQL键值对，内存数据库•Redis功能强大、扩展性好•PostgreSQL列族存储，高可扩展性•Cassandra轻量级、嵌入式数据库•SQLite图形数据库，关系分析•Neo4j企业级、高可靠性•Oracle如何与数据库交互Python应用代码Python包含业务逻辑和数据库操作代码数据库驱动适配器/实现与数据库系统之间的通信Python数据库系统存储和管理数据的软件系统与数据库交互的应用场景非常广泛，包括企业信息系统、应用后端、数据分析与挖掘、科学计算应用等不同场景可能使用不同的数据库系统和交互方式Python Web常用的数据库交互库包括（标准库）、、（）、（）、（框架）、Python sqlite3Python mysql-connector-python psycopg2PostgreSQL pymongoMongoDB SQLAlchemy ORM等我们将在后续章节详细介绍这些库的使用方法Django ORM数据库连接原理连接释放会话维护使用完毕后，程序需要主动关闭连接连接建立连接建立后，驱动程序与数据库之间以释放资源未正确关闭的连接可能驱动程序加载程序通过驱动程序创建连接对象，提保持会话，可以发送命令、接收结果、导致资源泄漏和数据库连接数过多的Python程序首先加载相应的数据库驱供服务器地址、端口、用户名、密码处理事务等会话会占用系统资源，问题动程序，如、等信息驱动程序将这些信息打包，因此需要合理管理mysql-connector等这些驱动程序是通过网络协议发送连接请求sqlite3Python与特定数据库系统通信的桥梁连接字符串是数据库连接最重要的元素，它包含了所有连接所需的参数信息不同数据库系统的连接字符串格式可能不同，但通常包含以下关键元素主机名/IP地址、端口号、用户名、密码、数据库名称、字符集等安装数据库驱动驱动SQLite驱动已包含在标准库中，不需要额外安装只需在代码中导入SQLite Pythonimportsqlite3驱动MySQL驱动需要通过安装MySQL pippip install mysql-connector-python也可以使用PyMySQL pip install pymysql驱动PostgreSQL驱动的安装命令PostgreSQLpip installpsycopg2如果编译有问题，可以使用二进制版本pip installpsycopg2-binary驱动MongoDB驱动的安装命令MongoDBpip installpymongo安装数据库驱动时可能遇到的依赖问题某些驱动程序可能需要本地编译，因此需要安装相应的编译工具和开发库例如，需要的开发库，在不同操作系统上安装方式不同psycopg2PostgreSQL标准库简介Python sqlite3轻量级嵌入式数据库是一个自包含、零配置、事务性数据库引擎它不需要单独的服务器进程，SQLite SQL可以直接嵌入到应用程序中数据库以单个文件的形式存储在磁盘上，非常便携内置于标准库Python从开始，模块就已经集成到标准库中，无需安装任何额外Python

2.5sqlite3Python的包这使得它成为快速原型开发和小型应用的理想选择支持标准功能SQL尽管轻量级，支持大多数标准功能，包括事务、子查询、视图、触发器等SQLite SQL它支持多种数据类型、、、和TEXT INTEGERREAL BLOBNULL适用场景特别适合于嵌入式设备、单文件应用、测试环境、小型应用、本地数据SQLite Web存储等场景当需要简单的数据存储而不想配置完整的数据库服务器时，是最SQLite佳选择数据库基本操作SQLite创建连接数据库文件/1使用方法创建数据库连接如果指定的数据库文件不存sqlite

3.connect在，将自动创建创建游标对象2conn=sqlite

3.connectexample.db通过连接对象创建游标，用于执行命令SQL也可以使用特殊路径创建内存数据库:memory:conn=cursor=conn.cursorsqlite

3.connect:memory:创建表3使用游标执行语句CREATE TABLEcursor.executeCREATE TABLEusers id INTEGER PRIMARY提交更改4KEY,name TEXT,email TEXTUNIQUE,age INTEGER创建表后需要提交更改以保存到数据库conn.commit关闭连接5操作完成后关闭连接conn.close连接与断开SQLite创建连接执行操作使用方法创建到数据库文件的连接通过连接对象进行数据库操作sqlite

3.connect关闭连接提交事务通过方法释放资源通过方法保存更改close commit连接最佳实践是使用的上下文管理器（语句），它能自动处理连接的关闭SQLite Pythonwithwith sqlite

3.connectexample.db asconn:cursor=conn.cursor执行各种操作#conn.commit执行语句SQL创建游标对象游标是数据库操作的主要接口编写语句SQL准备需要执行的命令SQL执行命令SQL通过方法执行cursor.execute获取结果或提交查询操作获取结果，修改操作提交更改对象是执行语句的主要工具，它提供了和两个关键方法方法用于执行单条语句，而用于执行带有cursor SQLexecute executemanyexecute SQLexecutemany多组参数的语句，特别适合批量插入数据SQL方法返回游标对象本身，可以用于链式调用对于查询，需要使用、或方法获取结果对于、、execute SELECTfetchone fetchallfetchmany INSERTUPDATE等修改操作，需要调用方法提交更改DELETE connection.commit数据插入示范基本插入语法参数化查询在中插入数据使用语句，基本语法如下使用占位符可以防止注入攻击，提高安全性SQLite INSERT INTO SQLINSERT INTO table_name column1,column2,...cursor.executeINSERT INTOusers name,email,age李四VALUES value1,value2,...;VALUES,,,,lisi@example.com,30通过执行插入操作支持两种占位符风格Python SQLite问号占位符cursor.executeINSERT INTOusers name,email,age•VALUES,,张三VALUES,zhangsan@example.com,25命名占位符•VALUES:name,:email,:ageconn.commit命名占位符使用字典传参cursor.executeINSERT INTOusers VALUES王五:name,:email,:age,{name:,email:wangwu@example.com,age:28}数据查询操作语句基础条件过滤排序与分组获取查询结果SELECT语句用于从数据库子句用于过滤符合子句用于对结使用获取SELECT WHEREORDER BYcursor.fetchone表中检索数据，基本语法为特定条件的记录，可以使用果排序，用于单行结果，GROUP BYcursor.fetchall各种操作符如分组获取所有结果，=,,,=,等获=,!=,LIKE,IN cursor.fetchmanysize列名列名SELECT1,2,...SELECT dept,取指定数量的结果表名条件例如FROM WHERE;SELECT*FROM AVGsalaryFROMusers WHERE name employeesGROUP BY例如SELECT name,张LIKE%;dept ORDER BYemail FROM usersAVGsalary DESC;WHERE age20;查询示例代码cursor.executeSELECT name,email FROM users WHEREage,20,results=cursor.fetchallfor row in results:姓名邮箱printf:{row

[0]},:{row

[1]}数据更新与删除更新数据（）删除数据（）UPDATE DELETE语句用于修改表中已存在的数据基本语法为语句用于删除表中的行基本语法为UPDATE DELETE表名列名值列名值条件表名条件UPDATE SET1=1,2=2,...WHERE;DELETE FROMWHERE;示例示例Python Pythoncursor.executeUPDATE usersSET age=WHEREnamecursor.executeDELETE FROM users WHEREage,张三=,26,18,conn.commit conn.commit更新操作完成后，可以通过获取受影响的行数同样，可以通过获取删除的行数cursor.rowcount cursor.rowcount更新了条记录删除了条记录printf{cursor.rowcount}printf{cursor.rowcount}注意如果条件省略，将删除表中的所有记录，需谨慎WHERE使用事务与回滚开始事务执行操作决策点提交回滚/默认情况下，非自动提交模式下的连接会在执执行多条语句，构成一个逻辑工作单元根据执行结果决定提交或回滚成功则提交，失败则回滚SQL commit rollback行第一条语句时自动开始事务SQL事务是一组操作，它们要么全部成功执行，要么全部不执行事务具有特性原子性（）、一致性（）、隔离性（）和持久性（）ACID AtomicityConsistency IsolationDurability在中，默认情况下连接不会自动提交更改，需要显式调用方法如果在提交前发生错误，可以调用方法回滚所有未提交的更改，恢复到事务开始前的状态Python SQLitecommitrollback事务使用示例try:cursor.executeUPDATE accountsSET balance=balance-100WHERE id=1cursor.executeUPDATE accountsSET balance=balance+100WHERE id=2conn.commitexcept:conn.rollbackraise异常处理机制使用捕获异常try-except将数据库操作包装在块中try捕获特定异常捕获具体的数据库异常类型回滚与清理出错时回滚事务并释放资源记录错误信息记录异常细节便于调试模块定义了几种常见的异常类型SQLite所有错误的基类•sqlite

3.Error完整性约束违反，如唯一性约束•sqlite

3.IntegrityError语法错误•sqlite

3.ProgrammingError SQL数据库操作问题，如磁盘满、表锁定等•sqlite

3.OperationalError异常处理示例try:赵六cursor.executeINSERT INTOusers name,email VALUES,,,zhaoliu@example.comconn.commitexcept sqlite

3.IntegrityError as e:数据完整性错误printf:{e}conn.rollbackexcept sqlite

3.Error as e:错误printfSQLite:{e}conn.rollback数据表结构操作创建表（）1CREATE TABLE定义表结构，包括列名、数据类型和约束cursor.executeCREATE TABLEproducts id INTEGER PRIMARYKEY AUTOINCREMENT,name TEXTNOT NULL,price REAL,stock INTEGERDEFAULT0修改表（）2ALTER TABLE添加、删除或修改表中的列cursor.executeALTER TABLEproducts ADDCOLUMN categoryTEXT对的支持有限，主要支持添加列和重命名表SQLite ALTERTABLE删除表（）3DROP TABLE完全删除表及其所有数据cursor.executeDROP TABLEIF EXISTStemp_products使用子句可以避免表不存在时的错误IF EXISTS查看表结构4提供了特殊的语句查询表结构SQLite PRAGMAcursor.executePRAGMA table_infoproductscolumns_info=cursor.fetchall还可以查询系统表获取所有表的信息sqlite_mastercursor.executeSELECT nameFROM sqlite_master WHEREtype=table简介及安装MySQL主要特性MySQL高性能、高可靠性、易用性•支持多种存储引擎（、等）•InnoDB MyISAM完整的事务支持（使用）•InnoDB强大的复制特性，支持主从复制•完整的事务实现•ACID广泛的编程语言支持•安装Windows从官网下载安装包（）•MySQL MSI运行安装向导，按提示配置•设置密码•root配置服务启动选项•可选安装•MySQL Workbench安装macOS使用•Homebrew brew install mysql使用官方包安装•DMG启动服务•brew servicesstart mysql首次设置•mysql_secure_installation安装Linux•Ubuntu:apt install mysql-server•CentOS:yum installmysql-server启动服务•systemctl startmysqld设置开机启动•systemctl enablemysqld是世界上最流行的开源关系型数据库管理系统之一，广泛应用于应用、企业级应用和各种需要结构化数据存储的场景MySQL Web使用MySQL Connector/Python安装MySQL Connector1使用安装官方的连接器pip MySQLpip installmysql-connector-python导入模块2这个包提供了与服务器通信的纯实现，无需额外的扩展MySQL PythonC在代码中导入必要的模块import mysql.connector创建连接3from mysql.connector importError使用方法创建数据库连接connectconn=mysql.connector.connect数据库主机地址host=localhost,#用户名user=root,#创建游标4密码password=password,#创建游标对象，用于执行命令SQL数据库名database=testdb#cursor=conn.cursor支持多种配置选项，可以通过连接参数调整MySQL Connector/Python指定服务器端口（默认）•port MySQL3306指定字符集（如）•charset utf8mb4是否自动提交事务•autocommit是否使用缓冲游标•buffered连接池大小•pool_size基本操作演示MySQL创建数据库1CREATE DATABASEtestdb CHARACTERSET utf8mb4COLLATE utf8mb4_unicode_ci;创建表CREATE TABLEcustomers id INT AUTO_INCREMENT PRIMARYKEY,name VARCHAR100,email VARCHAR100;连接数据库使用方法连接数据库mysql.connector.connect执行SQL使用对象执行命令cursor SQL关闭连接和关闭资源cursor.close conn.close连接最佳实践示例MySQLtry:conn=mysql.connector.connecthost=localhost,user=root,password=password,database=testdbcursor=conn.cursor执行操作#SQLcursor.executeCREATE TABLEIF NOTEXISTS customersid INTAUTO_INCREMENT PRIMARYKEY,name VARCHAR100,email VARCHAR100conn.commitexcept Error ase:错误printf:{e}finally:if conn.is_connected:cursor.closeconn.close插入与查询数据MySQL插入数据查询数据使用方法执行语句执行语句并获取结果execute INSERTSELECTcursor.execute cursor.executeSELECT*FROM customersINSERTINTO customers name,email rows=cursor.fetchallVALUES%s,%s for row in rows:张三姓名邮箱,,zhangsan@example.com printfID:{row

[0]},:{row

[1]},:{row

[2]}使用字典游标获取命名列conn.commit注意使用作为参数占位符，而不是的MySQL%s SQLitecursor=conn.cursordictionary=True批量插入数据cursor.executeSELECT*FROM customers李四王五data=[,lisi@example.com,,wangwu@example.com]rows=cursor.fetchallcursor.executemanyINSERT INTOcustomersname,email VALUES%s,for row inrows:%s,data姓名邮箱printfID:{row[id]},:{row[name]},:{row[email]}conn.commit的字符集设置很重要，尤其是处理中文等非字符时连接时指定可以支持完整的字符集，包括表情符号创建数据库和表时也应指定相应MySQL ASCIIcharset=utf8mb4Unicode的字符集和排序规则读取大批量数据使用分批获取使用流式游标fetchmany SSCursor对于大型结果集，可以使用方法分批获取数据，避免一次性加载所有结果到内存提供了（服务器端游标），它不在客户端缓存结果，适合处理非常大的结果集fetchmany MySQLConnector SSCursorcursor.executeSELECT*FROM large_table frommysql.connector.cursor importMySQLCursorSSCursorbatch_size=1000cursor=conn.cursorcursor_class=MySQLCursorSSCursorwhile True:cursor.executeSELECT*FROM very_large_table逐行处理，不会一次性加载到内存rows=cursor.fetchmanybatch_size for rowin cursor:#if notrows:process_rowrowbreak处理这一批数据process_datarows#使用子句分页查询使用适当的索引和排序LIMIT对于应用等需要分页的场景，可以使用和确保查询使用了合适的索引，尤其是在和子句中的列上建立索引，可以显著提高大数据集Web LIMITOFFSET WHEREORDER BY的查询性能page_size=100page=1offset=page-1*page_sizecursor.executeSELECT*FROM productsLIMIT%s OFFSET%s,page_size,offset批量数据写入技巧1000x1000060%性能提升建议批量大小网络开销减少相比逐行插入，批量插入通常可提高数百倍性能每批次插入数千至上万条记录通常是最佳平衡点批量操作可显著减少数据库往返通信次数批量插入数据的优化技巧使用方法一次性执行多条插入•executemany启用事务，在单个事务中完成批量插入•考虑关闭自动提交•conn.autocommit=False对于，可以使用导入文本文件数据，速度极快•MySQL LOADDATA INFILE对于非常大的数据集，考虑分批插入，每批次提交一次事务•示例代码用户data=[f{i},fuser{i}@example.com,random.randint18,60for iin range10000]禁用自动提交conn.autocommit=False#try:cursor.executemanyINSERT INTOusers name,email,age VALUES%s,%s,%s,data一次性提交所有插入conn.commit#except Error ase:出错时回滚conn.rollback#错误printf:{e}事务与锁MySQL事务隔离级别脏读不可重复读幻读可能发生可能发生可能发生READ UNCOMMITTED不会发生可能发生可能发生READ COMMITTED不会发生不会发生可能发生REPEATABLE READ不会发生不会发生不会发生SERIALIZABLE提供了四种事务隔离级别，控制事务间的可见性和并发性默认使用级别可以通过MySQL REPEATABLEREAD SETTRANSACTION ISOLATION语句更改隔离级别LEVEL的存储引擎实现了两种类型的锁MySQL InnoDB行锁（）只锁定特定行，允许其他事务访问同一表的其他行，并发性高但开销较大•Row-level Lock表锁（）锁定整个表，阻止其他事务访问表中的所有行，并发性低但开销小•Table-level Lock默认使用行锁，但在某些情况下会升级为表锁，如查询没有使用索引或使用了语句理解锁机制对于编写高并发应用至关重要InnoDB LOCKTABLES连接池简介连接池的必要性连接池实现Python创建数据库连接是一个资源密集型操作，涉及网络、认证和服务器资源分配在高并发应用中，如果几种常用的数据库连接池库I/O WebPython每个请求都创建新连接，将导致严重的性能问题提供和两种连接池

1.DBUtils PersistentDBPooledDB连接池通过预先创建并管理一组数据库连接，使应用程序可以重用这些连接，而不是每次都创建新连接这大安装pip installDBUtils大提高了应用性能，并帮助控制数据库服务器的负载基本用法减少连接创建开销（通常在几十到几百毫秒）••控制最大并发连接数from dbutils.pooled_db importPooledDB管理连接的生命周期•pool=PooledDB优化资源使用•使用的数据库模块creator=mysql.connector,#连接池允许的最大连接数maxconnections=10,#host=localhost,user=root,password=password,database=testdbconn=pool.connectioncursor=conn.cursor使用完后将连接归还池而不是关闭#cursor.close实际上是归还连接池conn.close#入门PostgreSQL主要特性完全符合标准•ACID强大的索引系统（等）•B-tree,GiST,GIN先进的查询优化器•多版本并发控制（）•MVCC丰富的数据类型和扩展•地理空间数据支持（）•PostGIS数据类型•JSON/JSONB安装指南Windows从下载安装程序•postgresql.org运行安装向导并设置密码•macOS使用•Homebrew:brewinstallpostgresql启动•:brew servicesstart postgresqlLinux•Ubuntu:apt installpostgresql•CentOS:yum installpostgresql-server基本连接默认使用端口PostgreSQL5432默认创建名为的数据库和用户postgres首次连接通常需要使用用户postgrespsql-U postgres创建新用户和数据库CREATE USERtestuser WITHPASSWORD password;CREATE DATABASEtestdb OWNERtestuser;是世界上最先进的开源关系型数据库之一，提供了企业级的功能、可靠性和性能它特别适合需要复杂查询、地理空间功能或扩展性的应用程序相比，PostgreSQL MySQL对标准的支持更完整，且提供了更多高级功能PostgreSQL SQL连接psycopg2PostgreSQL安装psycopg21是最流行的适配器，使用安装psycopg2PostgreSQL Pythonpippip installpsycopg2-binary基本连接2注意是预编译版本，适合快速开始使用；生产环境可考虑使用（需要本地编译）psycopg2-binary psycopg2创建到数据库的连接PostgreSQLimport psycopg2conn=psycopg

2.connecthost=localhost,执行查询3database=testdb,创建游标并执行SQL命令user=testuser,cursor=conn.cursor password=passwordcursor.executeSELECT versionversion=cursor.fetchone版本关闭连接printfPostgreSQL:{version

[0]}4完成操作后关闭游标和连接cursor.closeconn.close提供了一些特殊功能，充分利用的高级特性psycopg2PostgreSQL支持多种特有的数据类型，如数组、、•PostgreSQL hstoreJSON支持异步操作（通过）•psycopg

2.extras.wait_select提供类型，返回列名索引的结果•DictCursor支持服务器端游标，适用于处理大型结果集•通过和函数支持自定义类型转换•register_adapter register_type非关系型数据库简介MongoDB文档模型无模式设计使用类文档存储数据，灵活且直观集合中的文档可以有不同的字段，支持动态演化MongoDB JSON强大的查询4水平扩展支持丰富的查询，包括文档内嵌域和数组操作通过分片实现横向扩展，处理海量数据是一个流行的文档型数据库，它不使用传统的行和列来组织数据，而是使用类似的（）文档特别适合处理半结构化数据、需要快速迭代开发的应用，以及MongoDB JSONBSON BinaryJSON MongoDB需要高度可扩展性的大数据应用的主要概念对应到关系型数据库MongoDB数据库（）与关系型数据库的数据库概念相同•Database集合（）相当于关系型数据库的表•Collection文档（）相当于关系型数据库的行，但更灵活•Document字段（）相当于关系型数据库的列•Field操作Python MongoDBPyMongo基本操作CRUD访问数据库和集合插入文档连接MongoDB访问指定数据库和集合安装张三PyMongo user={name:,age:25,email:创建连接到服务器MongoDB或db=client.testdb#client[testdb]zhangsan@example.com}使用安装库pip PyMongofrompymongo importMongoClient或collection=db.users#db[users]result=collection.insert_oneuserpip installpymongoclient=插入的printf ID:{result.inserted_id}如果需要使用（云服务），还需要MongoDB AtlasMongoClientmongodb://localhost:27017/安装查询文档dnspython也可以使用连接字符串参数张三pip installdnspython user=collection.find_one{name:}client=MongoClienthost=localhost,port=27017printuser更新文档张三result=collection.update_one{name:},{$set:{age:26}}删除文档张三result=collection.delete_one{name:}简介与应用ORM什么是ORM对象关系映射（）是一种编程技术，将关系型数据库中的数据转换为面向对象编程语言中的对象充当应用程Object-Relational MappingORM序和数据库之间的桥梁，使开发者可以使用面向对象的方式操作数据库主要优点减少重复的（增删改查）代码•CRUD自动处理数据库方言差异•提供数据验证和类型转换•支持数据库迁移和版本控制•降低注入风险•SQL可以更专注于业务逻辑而非数据访问•潜在缺点学习曲线可能较陡•对复杂查询的支持可能有限•性能开销（尤其是关系复杂时）•可能导致对象关系阻抗不匹配问题•可能隐藏底层操作细节•SQL常用库Python ORM功能全面、灵活的框架•SQLAlchemy ORM框架的内置•Django ORMDjango ORM轻量级、简单易用的•Peewee ORM支持生成器表达式的•Pony ORM ORM异步，适用于等•Tortoise ORM ORM FastAPI简要介绍SQLAlchemy应用业务逻辑层使用模型对象操作数据ORM层ORM处理对象和关系数据的映射表达式语言SQL提供方式构建语句Pythonic SQL适配层DBAPI连接不同的数据库系统是中最流行的库，它提供了全面的抽象，由两个不同的部分组成SQLAlchemy PythonORM SQLDatabase底层抽象层，允许使用构建表达式•SQLAlchemy CoreSQL PythonSQL高级对象关系映射系统，将类映射到数据库表•SQLAlchemy ORM的主要优势在于其灵活性和可组合性，允许在不同抽象级别工作例如，可以纯粹使用，也可以在必要时直接使用层构建复杂查询它支持所有主流SQLAlchemyORMCore数据库，包括、、、和SQLite MySQLPostgreSQL OracleMicrosoft SQLServer基础增删改查SQLAlchemy定义模型基本操作CRUD创建会话from sqlalchemyimport create_engine,Column,Integer,String#from sqlalchemy.ext.declarative importdeclarative_base session=Session创建新用户from sqlalchemy.orm importsessionmaker#Create张三engine=create_enginesqlite:///example.db new_user=Username=,email=zhangsan@example.com,age=25Base=declarative_base session.addnew_userclass UserBase:session.commit查询用户__tablename__=users#Read张三id=ColumnInteger,primary_key=True user=session.queryUser.filter_byname=.firstname=ColumnString printuser更新用户email=ColumnString,unique=True#Updateage=ColumnInteger user.age=26def__repr__self:session.commit删除用户return fUserid={self.id},name={self.name}#DeleteBase.metadata.create_allengine session.deleteuserSession=sessionmakerbind=engine session.commit关闭会话#session.close概述Django ORM特点模型定义查询操作Django ORM是框架的内置组件，与框架紧在中，模型定义在应用的文件中提供强大的进行查询Django ORMDjango Djangomodels.py DjangoQuerySet API密集成，专为应用开发优化它遵循电池已包含Web获取所有图书from django.db importmodels#的理念，提供丰富的内置功能，包括数据验证、迁移、管理界面等相比，更class Authormodels.Model:all_books=Book.objects.allSQLAlchemy DjangoORM加简单直观，但灵活性稍低过滤查询name=models.CharFieldmax_length=100#birth_date=models.DateFieldnull=True,cheap_books=blank=True Book.objects.filterprice__lt=

50.00关联查询def__str__self:#return self.name zhang_books=张Book.objects.filterauthor__name__contains=class Bookmodels.Model:title=models.CharFieldmax_length=200排序#author=models.ForeignKeyAuthor,sorted_books=Book.objects.order_by-on_delete=models.CASCADEpublication_datepublication_date=models.DateFieldprice=models.DecimalFieldmax_digits=6,decimal_places=2数据库模式设计数据库模式设计是数据库开发的基础，关系型数据库中主要涉及到三种类型的表关系一对一关系如用户表与用户详情表，每个用户只有一个详情记录，通常使用外键实现一对多关系如作者与图书，一个作者可以写多本书，使用外键实现多对多关系如学生与课程，一个学生可以选多门课程，一门课程可以有多个学生，使用中间表实现数据库规范化是减少数据冗余和提高数据完整性的过程，主要包括第一范式（确保原子性）、第二范式（消除部分依赖）和第三范式（消除传递依赖）在某些情况下，为了性能考虑，可能需要进行反规范化设计，接受一定程度的数据冗余以提高查询性能数据库索引与优化确定需要优化的查询通过分析慢查询日志，找出执行时间长的查询分析查询执行计划使用分析查询如何执行和使用索引EXPLAIN创建适当的索引基于查询模式为经常查询的列创建索引优化查询语句重写查询以更好地利用索引数据库索引是提高查询性能的关键索引可以大大加速数据检索，但会增加写入操作的开销和存储空间常见索引类型包括索引最常用的索引类型，适合等值查询和范围查询B-Tree哈希索引只适合等值查询，但等值查询速度极快全文索引专为文本搜索优化空间索引用于地理空间数据的索引索引优化原则为子句中的列、条件列和列创建索引；避免在频繁更新的列上创建过多索引；考虑使用复合索引以支持多列查询；定期分析和重建索引以保持性能WHERE JOINORDER BY安全性问题与防御注入原理防御措施SQL注入攻击通过在用户输入中插入恶意代码，使应用程序执行意外的命令例如，用户输防御注入的主要方法是使用参数化查询（预处理语句），它将语句和数据分开处理，防止SQL SQL SQL SQL SQL入包含引号和关键字，可能导致数据泄露、损坏或未授权访问注入其他防御措施包括输入验证、最小权限原则、错误信息过滤等SQLSQL123常见注入类型常见注入方式包括在表单输入中添加命令、篡改参数、通过注入、通过请求SQL URLCookie HTTP头注入等这些攻击可能导致数据泄露、身份冒用、提权或完全控制数据库对比易受攻击的代码和安全的代码不安全的代码（易受注入攻击）#SQLusername=request.form[username]cursor.executefSELECT*FROM users WHERE username={username}安全的代码（使用参数化查询）#username=request.form[username]cursor.executeSELECT*FROMusersWHERE username=,username,使用框架也可以提供额外的安全保护，因为它们通常会自动使用参数化查询但即使使用，也需要注意原始查询和构造动态查询的安全性ORMORM SQL数据库用户权限控制用户管理最小权限原则MySQL使用基于主机和用户名的访问控制系统创建用户最小权限原则是一种安全实践，只给用户提供完成其任务所需的最小权限集MySQL这减少了系统被滥用或遭到入侵时可能造成的损害CREATE USERusername@localhost IDENTIFIEDBY password;应用最小权限原则的最佳实践授予权限创建专用的应用程序用户，而不使用账户•rootGRANT SELECT,INSERT,UPDATE ON database.table TO根据应用程序的实际需要分配权限•username@localhost;对只需读取数据的操作仅授予权限•SELECT撤销权限考虑使用视图限制可见数据•REVOKE INSERTONdatabase.table FROMusername@localhost;避免授予全局权限（如上的）•*.*ALL定期审计用户权限，移除不必要的权限删除用户•为不同模块功能创建不同的数据库用户•/DROP USERusername@localhost;查看用户权限SHOW GRANTSFOR username@localhost;数据迁移与备份导出数据批量导出导入数据Python使用数据库自带工具导出数据使用脚本进行自定义导使用数据库工具导入Python出命令•MySQL:mysqldump mysql-u root-p dbname命令import csvbackup.sql•PostgreSQL:pg_dump•SQLite:.dump命令或文cursor.executeSELECT*或使用Python批量导入件复制FROM userswithopendata.csv,ras f:例如mysqldump-u root-rows=cursor.fetchallreader=csv.readerfp dbnamebackup.sqlwith openusers.csv,w,跳过标题行nextreader#newline=asf:forrowin reader:writer=csv.writerfcursor.executeINSERTwriter.writerow[i

[0]for iINTOtable VALUES列in cursor.description]#,,,row名数writer.writerowsrows#conn.commit据行自动化备份使用定时任务执行备份脚本使用定时•Linux:crontab执行使用任务计划•Windows:程序使用库•Python:schedule定时执行性能监控与分析关键性能指标监控数据库性能的关键指标包括查询响应时间、每秒查询数（）、每秒事务数（）、缓存命中率、内存使用情况、QPS TPS磁盘、连接数和锁等待时间等这些指标可以帮助识别性能瓶颈和潜在问题I/O监控工具常用的数据库监控工具包括•MySQL:MySQL Workbench,PMM PerconaMonitoring andManagement•PostgreSQL:pgAdmin,pg_stat_statements通用•:Prometheus+Grafana,Nagios库统计扩展•Python:SQLAlchemy,django-debug-toolbar日志分析SQL开启慢查询日志可以捕获执行时间超过阈值的查询，这是找出性能问题的宝贵资源例如，在中启用慢查询日志MySQLSET GLOBALslow_query_log=ON;设置阈值为秒SET GLOBALlong_query_time=1;#1使用工具如分析慢查询日志，识别最耗时的查询模式pt-query-digest性能分析Python可以使用工具监控应用程序中的数据库操作Python装饰器测量函数执行时间•timing分析程序性能•cProfile模块记录操作和时间•logging SQL并发处理与多线程扩展异步数据库操作并发数据库操作执行异步查询使用同时执行多个查询创建异步连接asyncio.gather使用执行异步数据库操作安装异步数据库驱动awaitasync defget_useruser_id:使用异步上下文管理器创建连接async withdb.executeSELECT*FROM根据数据库类型安装相应的异步驱动async withaiosqlite.connectexample.db asimportasyncio usersas cursor:db:SQLite:pipinstallaiosqliteimport aiosqliteresults=await cursor.fetchallasync withdb.executeSELECT*FROMMySQL:pipinstallaiomysqlasync defmain:forrowin results:usersWHERE id=,user_id,as cursor:PostgreSQL:pipinstallasyncpgasync withaiosqlite.connectexample.db asprintrow returnawait cursor.fetchonedb:results=await asyncio.gather执行异步数据库操作#get_user1,...get_user2,get_user3异步数据库操作特别适合密集型应用，如服务器和应用，可以在单线程中处理数千个并发连接，提高资源利用率常见的异步框架如、和都可以与这些异步数据库驱动完美配合I/O APIWeb WebFastAPI aiohttpSanic常见数据库运维问题连接数过多症状数据库报错，新连接被拒绝•too manyconnections原因应用未正确关闭连接、连接泄漏、流量突增•解决方案•实现连接池管理•确保块中关闭连接•finally使用上下文管理器（语句）•with增加配置（应谨慎）•max_connections监控活动连接数•死锁问题症状事务被阻塞、超时或死锁错误•原因多个事务以不同顺序锁定资源•解决方案•保持事务简短•按固定顺序访问表•使用合适的隔离级别•添加死锁检测和超时机制•对于，使用•InnoDB innodb_deadlock_detect慢查询症状某些操作响应缓慢•原因缺少索引、低效查询、表过大•解决方案•开启并分析慢查询日志•使用分析执行计划•EXPLAIN添加适当索引•优化查询语句•考虑分表或归档历史数据•备份与恢复关注点数据一致性、性能影响、恢复速度•实战图书管理系统需求分析核心功能数据表设计我们的图书管理系统需要实现以下核心功能根据功能需求，我们需要以下主要数据表图书信息管理添加、编辑、删除和查询图书存储图书基本信息••books作者管理维护作者信息及其作品作者信息••authors分类管理图书分类及类别管理图书分类••categories用户管理注册、登录、权限控制出版社信息••publishers借阅管理借书、还书、预约、续借系统用户信息••users搜索功能按作者、标题、等搜索借阅记录•ISBN•loans统计报表借阅统计、库存统计等预约记录••reservations图书与作者多对多关系表•book_authors图书与分类多对多关系表•book_categories表关系主要表关系包括一本书可以有多个作者，一个作者可以写多本书（多对多）•一本书可以属于多个分类，一个分类可以包含多本书（多对多）•一本书对应一个出版社，一个出版社可以出版多本书（一对多）•一个用户可以借多本书，一本书也可以被多个用户借阅（通过借阅表关联）•本系统将使用结合实现，以便于部署和学习实际生产环境可考虑迁移到或以获得更Python SQLiteMySQLPostgreSQL好的性能和并发处理能力实战系统建模与表设计表表表表books authorsusers loansCREATE TABLE booksCREATE TABLEauthorsCREATE TABLEusersCREATE TABLEloans id INTEGER PRIMARYKEY id INTEGER PRIMARYKEY id INTEGER PRIMARYKEY id INTEGER PRIMARYKEYAUTOINCREMENT,AUTOINCREMENT,AUTOINCREMENT,AUTOINCREMENT,title TEXTNOT NULL,name TEXTNOT NULL,username TEXTUNIQUE NOT NULL,book_idINTEGER,isbn TEXTUNIQUE,birth_date TEXT,password TEXTNOT NULL,user_idINTEGER,publication_year INTEGER,biography TEXTemail TEXTUNIQUE,loan_date TEXTNOT NULL,publisher_idINTEGER,;role TEXTDEFAULT user,due_date TEXTNOTNULL,total_copies INTEGERDEFAULT1,CREATETABLEbook_authorsregistered_date TEXTDEFAULT return_date TEXT,CURRENT_TIMESTAMPavailable_copies INTEGERDEFAULT1,book_idINTEGER,FOREIGN KEY book_id REFERENCES;booksid,FOREIGN KEYpublisher_id REFERENCESauthor_idINTEGER,publishersid FOREIGN KEY user_id REFERENCESPRIMARYKEYbook_id,author_id,usersid;FOREIGN KEYbook_id REFERENCES;booksid,FOREIGNKEYauthor_id REFERENCESauthorsid;这个图书管理系统的图展示了实体间的关系书籍和作者是多对多关系，通过表关联；书籍和分类也是多对多关系，通过表关联；用户和书籍之间通过表建立借阅关系表设E-R book_authors book_categories loans计遵循第三范式，确保数据完整性的同时最小化冗余实战数据导入与初始化生成模拟数据批量插入数据使用生成大量模拟数据进行测试创建数据库和表结构Python使用方法批量插入数据准备测试数据executemanyimport random执行脚本创建所有必要的表SQLauthors_data=[创建样本数据脚本，准备初始化系统所需的图书、作者和用户数据import stringimportsqlite3可以使用Python生成随机数据，或从CSV文件导入真实数据莫言,1955-02-17,中国作家，诺贝尔文学奖获得者,def generate_isbn:conn=sqlite

3.connectlibrary.db余华中国先锋派作家,1960-04-03,,return.joinrandom.choicesstring.digits,k=13with openschema.sql,rasf:更多作者#...测试图书books_data=[f{i},generate_isbn,2020+i%5,conn.executescriptf.read]i%10+1,random.randint1,10,random.randint1,conn.commit10for iin range1000]cursor.executemanyINSERT INTOauthors name,birth_date,biographyVALUES,,,authors_data在导入大量数据时，为了提高性能，应禁用自动提交并使用事务处理开启手动事务控制conn.isolation_level=None#cursor.executeBEGIN TRANSACTIONtry:执行批量插入#cursor.executemany...cursor.executemany...conn.executeCOMMITexcept:conn.executeROLLBACKraise实战增删改查功能实现图书管理模块查询和更新功能创建一个类处理图书相关操作BookManager defget_bookself,book_id:class BookManager:with sqlite

3.connectself.db_path asconn:def__init__self,db_path:cursor=conn.cursorself.db_path=db_path cursor.executedef add_bookself,title,isbn,pub_year,publisher_id,total_copies:SELECT b.*,p.name aspublisher_namewith sqlite

3.connectself.db_path asconn:FROM booksbcursor=conn.cursor LEFT JOIN publishers p ON b.publisher_id=p.idcursor.execute WHEREb.id=INSERTINTObooks title,isbn,publication_year,publisher_id,total_copies,available_copies,book_id,VALUES,,,,,return cursor.fetchone,title,isbn,pub_year,publisher_id,total_copies,total_copies defupdate_bookself,book_id,title,isbn,pub_year,publisher_id,total_copies:book_id=cursor.lastrowid with sqlite

3.connectself.db_path asconn:conn.commit cursor=conn.cursorreturn book_id cursor.executeUPDATE booksSETtitle=,isbn=,publication_year=,publisher_id=,total_copies=WHEREid=,title,isbn,pub_year,publisher_id,total_copies,book_idconn.commitreturn cursor.rowcount0借阅功能实现（涉及事务处理）def borrow_bookself,book_id,user_id,loan_days=14:with sqlite

3.connectself.db_path asconn:try:实战事务处理与异常捕获开始事务明确标记事务的开始尝试执行操作在块中执行所有相关操作try检查操作结果验证操作是否成功或满足条件提交或回滚4成功则提交，失败则回滚记录操作日志记录事务结果和异常信息在图书归还流程中实现事务处理和日志记录def return_bookself,loan_id:with sqlite

3.connectself.db_path asconn:try:开始事务#conn.executeBEGIN TRANSACTIONcursor=conn.cursor查找借阅记录#cursor.executeSELECT book_id,return_date FROMloans WHEREid=,loan_id,result=cursor.fetchoneif notresult:conn.rollback归还失败找不到借阅记录self.log_errorf{loan_id}实战分页与复杂查询分页查询实现分页与结果获取实现通用的分页查询方法构建完整的子句#WHEREdef get_books_paginatedself,page=1,page_size=10,search=None,category_id=None,author_id=None:if where_clauses:with sqlite

3.connectself.db_path asconn:query+=WHERE+AND.joinwhere_clausescursor=conn.cursor count_query+=WHERE+AND.joinwhere_clauses构建基础查询获取总记录数##query=cursor.executecount_query,paramsSELECT b.id,b.title,b.isbn,b.publication_year,b.available_copies,b.total_copies,p.name aspublisher total_items=cursor.fetchone

[0]FROM booksb total_pages=total_items+page_size-1//page_size应用分页LEFTJOINpublisherspONb.publisher_id=p.id#query+=ORDERBYb.title LIMITOFFSET count_query=SELECT COUNT*FROM booksb offset=page-1*page_sizewhere_clauses=[]params.extend[page_size,offset]执行查询params=[]#添加搜索条件#cursor.executequery,paramsif search:books=cursor.fetchall转换为字典列表，便于序列化where_clauses.appendb.title LIKEOR b.isbn LIKE#JSONparams.extend[f%{search}%,f%{search}%]columns=[column

[0]for columnincursor.description]添加分类过滤#result=[dictzipcolumns,row forrowinbooks]if category_id:return{where_clauses.appendb.idINSELECT book_id FROMbook_categories WHEREcategory_id=items:result,params.appendcategory_id page:page,添加作者过滤#page_size:page_size,if author_id:total_items:total_items,where_clauses.appendb.idINSELECT book_id FROMbook_authors WHEREauthor_id=total_pages:total_pagesparams.appendauthor_id}实战防注入与安全加固使用参数化查询输入验证与清洁12参数化查询是防注入的最佳实践，它确保数据与代码分离处理实现严格的输入验证，拒绝不符合预期格式的输入SQLSQL不安全的做法#def validate_isbnisbn:应为位数字username=request.form[username]#ISBN-1313危险！query=fSELECT*FROMusersWHERE username={username}#if notisbn ornot isinstanceisbn,str:安全的做法#return Falseusername=request.form[username]isbn=isbn.replace-,.replace,cursor.executeSELECT*FROMusersWHERE username=,username,if notisbn.isdigit orlenisbn!=13:return FalsereturnTrue安全的密码处理日志与审计34使用密码哈希存储用户密码，永远不要存储明文密码实现全面的日志记录和审计跟踪import hashlibimport loggingimportos logging.basicConfigfilename=library.log,level=logging.INFO,def hash_passwordpassword:format=%asctimes-%names-%levelnames-%messages创建随机盐值#def log_user_activityuser_id,action,details:salt=os.urandom32withsqlite

3.connectdb_path asconn:使用算法进行哈希#PBKDF2cursor=conn.cursorkey=hashlib.pbkdf2_hmacsha256,password.encodeutf-8,salt,100000cursor.execute将盐值和哈希值一起存储#INSERTINTOactivity_logs user_id,action,details,timestampreturn salt.hex+:+key.hex VALUES,,,datetimenow,user_id,action,detailsconn.commitlogging.infofUser{user_id}performed{action}:{details}实战接口性能优化90%50x20ms缓存命中率目标性能提升目标响应时间高效缓存可显著提升系统响应速度优化后查询速度可提高数十倍常用接口响应时间应控制在毫秒级实现查询缓存机制提高频繁访问数据的响应速度import functoolsimporttime简单的内存缓存实现#cache={}def cachedttl_seconds=60:def decoratorfunc:@functools.wrapsfuncdef wrapper*args,**kwargs:创建缓存键#key=f{func.__name__}:{strargs}:{strkwargs}检查缓存是否有效#if keyin cache:result,timestamp=cache[key]if time.time-timestampttl_seconds:return result缓存未命中，执行函数#result=func*args,**kwargs更新缓存#cache[key]=result,time.timereturn result综合练习与思考题典型问题拓展练习Code Review未正确关闭数据库连接或游标实现图书分类系统与推荐功能••注入风险，未使用参数化查询添加用户评分和评论系统•SQL•大型查询未使用分页，可能导致内存问题实现图书库存预警和采购建议••事务处理不完整，缺少错误处理设计图书检索的高级搜索功能••缺少必要的索引导致查询效率低开发借阅统计和报表生成功能••敏感信息（如密码）未加密存储集成第三方图书获取更多信息••API表间关系设计不合理，违反数据库范式完善用户权限管理系统••硬编码的语句，难以维护实现数据备份和恢复功能•SQL•思考题如何优化大型图书管理系统的数据库架构？考虑分表、分区或？

1.NoSQL如何设计一个支持千万级图书数据的高效搜索系统？

2.在高并发场景下，如何避免数据库连接耗尽？

3.如何实现一个能抵御各类注入的安全层？

4.SQL比较和原生在大型项目中的优缺点

5.ORMSQL设计一个能处理图书馆多分馆数据同步的方案

6.以上问题和练习旨在帮助巩固课程所学知识，并进一步探索更复杂的数据库应用场景建议尝试结合实际项目需求，解决这些问题，并在实践中持续优化和学习记住，数据库设计和优化是一个迭代过程，随着应用需求和规模的变化，数据库架构也需要相应调整课程总结与展望在本课程中，我们系统学习了数据库操作的核心知识，从基础的操作到高级的技术，从单表查询到复杂的事务处理我们不仅掌握了如何连接、查询和操作各种数据库系统，还学习了如Python SQLiteORM何优化性能、保障安全并构建可靠的数据库应用未来数据库技术的发展趋势包括Python异步数据库操作随着生态系统的成熟，异步数据库驱动将变得更加普及，特别是在高并发应用中asyncio Web技术进化更智能的框架，能更好地平衡易用性和性能ORMORM与关系型混合使用应用将根据需求灵活选择合适的数据库类型NoSQL云原生数据库服务与云平台深度集成的数据库服务将更易于部署和扩展数据库集成数据库系统将集成更多功能，如智能索引、查询优化等AI AI数据分析与大数据整合数据库操作将与数据分析工具更紧密集成Python继续学习的建议深入研究一种数据库系统的高级特性，探索更多框架，了解数据库设计模式，实践复杂业务场景下的数据库应用开发，关注性能优化和安全实践，参与开源数据库工具的开发记住，ORM数据是现代应用的核心，掌握数据库技术将使你在软件开发领域具有持久的竞争力。