第17章 数据库操作

17.1 sqlite3数据库

SQLite 是一个C语言库，它可以提供一种轻量级的基于磁盘的数据库，这种数据库不需要独立的服务器进程，也允许需要使用一种非标准的 SQL 查询语言来访问它。一些应用程序可以使用 SQLite 作为内部数据存储。可以用它来创建一个应用程序原型，然后再迁移到更大的数据库，比如 PostgreSQL 或 Oracle。

Python3内置sqlite3模块用于操作数据库，只需直接导入模块即可。

17.1.1 sqlite3模块

  1. 内置常量

    • sqlite3.version 以字符串形式记录了模块的版本号。
    • sqlite3.sqlite_version 以字符串形式记录了sqlite数据库的版本号。
    • sqlite3.isolation_level 用于配置隔离级别。
    • sqlite3.in_transaction 布尔值，活动状态标记。

  2. connection对象

    • sqlite3.connect(database, timeout, *args) 可以读取或创建数据库文件，并返回connection对象。

      • “:memory:" 作database时，可以在RAM中打开一个数据库连接，而不是在磁盘上。
      • 若一个数据库被多个连接访问，一个用户的事务会被锁定，直到事务提交。
      • 参数timeout表示连接等待锁定的持续时间，直到发生异常断开。
      • 若数据库名database不存在，会自动创建一个，可以使用带有路径的名称来指定创建位置。

    • connection对象中包含非常多操作数据库的方法，详情查找文档。

  3. cursor对象

    • 该对象含有 execute() 等方法，可用于执行SQL语句。
    • 连接对象的connection.cursor(cursorClass) 方法可以创建并返回cursor对象。
      • 该方法只有一个可选参数，只能是sqlite3.Cursor类的自定义子类。

  4. cursor.execute(sql, optionalParam) 方法：

    • 可以用占位符传参的形式执行一条SQL语句，类似java的PreparedStatement接口。
    import sqlite3                                       # 引入模块
conn = sqlite3.connect(":memory:")                   # 在内存中建立数据库，并获取连接
curs = conn.cursor()                                 # 从连接对象中获取光标对象
curs.execute("create table t_student(Sname, Sage)")  # 用光标对象执行SQL语句创建表
name = "Jarjack"
age = 18
# 1. 以“？”作占位符的形式执行SQL语句
curs.execute("insert into t_student values (?, ?)",(name,age))
# 2. 以参数命名的形式作占位符执行SQL语句
curs.execute("select * from t_student where Sname= :name",{"name":name})
# 3. 从结果集抓取一条记录输出。
print(curs.fetchone())

  5. cursor.executemany(sql, seq_of_param)方法：

    • 可以自动将seq_of_param序列中的所有参数依次执行一条SQL。
    import sqlite3                                       # 引入模块
conn = sqlite3.connect(":memory:")                   # 在内存中建立数据库，并获取连接
curs = conn.cursor()                                 # 从连接对象中获取光标对象
curs.execute("create table t_student(Sname, Sage)")  # 用光标对象执行SQL语句创建表
# 1. 定义参数表的序列
students_param = [["张学友",24],["刘德华",25],["郭富城",19],["黎明",28]]
# 2. 以“？”作占位符，以参数表的序列作为参数，依次为每个参数表执行数据插入。
curs.executemany("insert into t_student values (?,?)", students_param)
# 3. 执行查询，返回结果集到cursor对象。
curs.execute("select * from t_student")
print(curs.fetchall())                               # 从结果集抓取一条记录输出。

  6. cursor.executescript(sql, optional_param)方法：

    • 执行多台SQL语句作为脚本，首先执行commit语句，再执行输入的脚本，每条语句用“；”分隔。
    • 在executescript()中执行的查询，不会返回结果集给cursor对象，要另外用execute()查询。
    import sqlite3                                       # 引入模块
conn = sqlite3.connect(":memory:")                   # 在内存中建立数据库，并获取连接
curs = conn.cursor()                                 # 从连接对象中获取光标对象
# 1. 执行多条SQL语句
curs.executescript("""
        create table t_person(name, age, sex);
        insert into t_person values("Jarjack",18,"male");
        insert into t_person values("Leon",25,"male");
        """)
curs.execute("select * from t_person")  # 再另外执行的查询
print(curs.fetchall())

  7. 可以在connection对象中调用execute()方法，相当于一种快捷方式，它会先建立一个临时的cursor对象，再由该对象执行。

    • connection.execute()
    • connection.executemany()
    • connection.executescript()

  8. connection对象和cursor对象中还有许多数据库操作的方法，如提交、回滚、创建函数、权限管理等，详情查找文档。

17.1.2 sqlite3的数据类型

  • sqlite3仅支持5种类型：NULL, INTEGER, REAL, TEXT, BLOB，分别对应Python中的None, int, float, str, bytes类型。

  • 若实际中需要处理多种类型，就需要用到类型扩展技术，一是对象适配，二是强制类型转换。

17.2 MySQL数据库

  1. 在Python安装MySQL的驱动模块PyMySQL，下载地址：https://github.com/PyMySQL/PyMySQL

    • 可以使用管理工具pip安装：pip install PyMySQL

  2. 在Python连接并操作MySQL数据库：

    # 1. 引入MySQL驱动模块
import pymysql
# 2. 连接数据库获取连接对象
conn = pymysql.connect(host="localhost",user="root",password="123456",database="db_practice")
# 3. 在连接对象中获取光标对象用于执行SQL
curs = conn.cursor()
try:
    # 4. 插入数据
    curs.execute("insert into t_dpmt values(50,'manufacture','Los Angle')")
    # 5. 更新数据（类型占位符预编译的方式！）
    curs.execute("update t_dpmt set locate='%s' where name='%s'" % ('guangzhou','manufacture'))
    # 6. 删除数据（类型占位符预编译的方式！）
    curs.execute("delete from t_dpmt where locate='%s'" % "guangzhou")
    # 7. 提交事务，使操作持久化到数据库（没有commit()数据不会保存，仅在面板中显示）。
    conn.commit()
    # 8. 查询数据
    curs.execute("select * from t_dpmt") # cursor对象保存结果集
    resultset = curs.fetchall()          # 获取结果集
    for row in resultset:                # 按行解析结果集
        id = row[0]
        name = row[1]
        locate = row[2]
        print(id, name, locate)          # 输出行
except:
    print("语句不正确！")
    conn.rollback()                      # 出现操作异常时回滚
finally:
    conn.close()                         # 最后关闭连接

17.3 MariaDB数据库

  1. 安装驱动模块 pip install mysql-connector。其他略。