15、Python 访问数据库

一、概述Python操作数据库一般有三种方式原生驱动直接写SQLsqlite3内置SQLite、pymysqlMySQLORM框架 SQLAlchemy用Python对象映射数据库表减少手写SQL适配多种数据库适用场景小测试用SQLite业务存储用MySQL项目开发优先SQLAlchemy遵循数据库通用规范先建连接 → 获取游标 → 执行SQL → 提交事务 → 关闭游标和连接二、使用 SQLiteSQLite是文件型数据库Python内置sqlite3模块无需额外安装不需要单独启动服务适合本地测试、小型数据存储。基础示例import sqlite3 # 1. 连接数据库不存在则自动创建文件 test.db conn sqlite3.connect(test.db) # 2. 获取游标 cursor conn.cursor() # 建表 cursor.execute( CREATE TABLE IF NOT EXISTS user ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, age INTEGER ) ) # 插入单条数据 cursor.execute(INSERT INTO user(name, age) VALUES (?, ?), (zhangsan, 20)) # 批量插入 cursor.executemany(INSERT INTO user(name, age) VALUES (?, ?), [ (lisi, 22), (wangwu, 21) ]) # 查询 cursor.execute(SELECT * FROM user) print(cursor.fetchall()) # 全部结果 # cursor.fetchone() 取第一条 # cursor.fetchmany(2) 取前2条 # 更新 cursor.execute(UPDATE user SET age? WHERE name?, (25, zhangsan)) # 删除 cursor.execute(DELETE FROM user WHERE name?, (wangwu,)) # 提交事务DML操作必须commitSQLite默认事务 conn.commit() # 关闭 cursor.close() conn.close()占位符SQLite 使用?不要用字符串拼接SQL防止SQL注入三、使用 MySQLMySQL是C/S架构数据库需要先安装MySQL服务Python使用pymysql驱动。1. 安装pip install pymysql2. 基础示例import pymysql # 建立连接 conn pymysql.connect( host127.0.0.1, port3306, userroot, password你的密码, databasetest_db, charsetutf8mb4 ) cursor conn.cursor() # 建表 cursor.execute( CREATE TABLE IF NOT EXISTS user( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), age INT ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; ) # 插入pymysql占位符是 %s cursor.execute(INSERT INTO user(name,age) VALUES (%s,%s), (zhangsan,20)) conn.commit() # 查询 cursor.execute(SELECT * FROM user) print(cursor.fetchall()) cursor.close() conn.close()✅ 重点区别pymysql 占位符是%s不是字符串格式化必须提前建好数据库CREATE DATABASE test_db;异常场景网络断开、账号密码错误、库不存在异常捕获模板import pymysql try: conn pymysql.connect(...) cursor conn.cursor() cursor.execute(sql) conn.commit() except pymysql.MySQLError as e: print(数据库错误, e) conn.rollback() # 出错回滚 finally: if conn: cursor.close() conn.close()四、使用 SQLAlchemySQLAlchemy 是Python最经典的ORM对象关系映射框架一套代码可以切换 SQLite / MySQL / PostgreSQL不用大量改SQL。 分为两种使用方式CoreSQL表达式方式ORM面向对象类映射表日常开发最常用1. 安装pip install sqlalchemy2. ORM 完整示例MySQLfrom sqlalchemy import create_engine, Column, Integer, String from sqlalchemy.orm import declarative_base, sessionmaker # 1. 创建引擎连接数据库 # mysqlpymysql://user:passwordhost:port/dbname engine create_engine(mysqlpymysql://root:密码127.0.0.1:3306/test_db) # SQLite引擎写法engine create_engine(sqlite:///test.db) # 2. 基类所有模型继承它 Base declarative_base() # 3. 定义模型类对应数据库一张表 class User(Base): __tablename__ user id Column(Integer, primary_keyTrue, autoincrementTrue) name Column(String(50), nullableFalse) age Column(Integer) def __repr__(self): return fUser(name{self.name}, age{self.age}) # 4. 创建表不存在才创建 Base.metadata.create_all(engine) # 5. 创建会话用来增删改查 Session sessionmaker(bindengine) session Session() # 新增 u1 User(namezhangsan, age20) session.add(u1) session.add_all([ User(namelisi, age22), User(namewangwu, age21) ]) session.commit() # 查询 # 查询所有 res session.query(User).all() # 条件查询 res session.query(User).filter(User.name zhangsan).first() # 模糊查询 filter(User.name.like(%zhang%)) # 更新 if res: res.age 26 session.commit() # 删除 session.delete(res) session.commit() session.close()常用查询语法速记# 等于 filter(User.name xxx) # 大于小于 filter(User.age 18) # and / or from sqlalchemy import or_, and_ filter(or_(User.age20, User.namelisi)) # 排序 order_by(User.age.desc()) # 分页 offset limit session.query(User).order_by(User.id).offset(0).limit(10).all()五、三者对比总结方式依赖优点缺点适合场景sqlite3内置零服务、开箱即用并发弱、不适合多用户本地缓存、脚本、测试pymysql原生需要MySQL服务灵活完全掌控SQL重复样板代码容易注入切换数据库改动大简单脚本、需要精细控制SQLSQLAlchemy ORM驱动框架面向对象、跨数据库、防注入、代码优雅有学习成本复杂SQL性能不如手写Web项目、后端业务开发六、重要注意事项永远不要字符串拼接SQL使用参数化查询防止SQL注入写修改操作insert/update/delete一定要commit异常时rollback用完连接、游标、session要关闭避免连接泄漏MySQL注意编码utf8mb4支持emojiSQLAlchemy适合业务开发报表、超复杂聚合SQL可以用原生SQL