• 第17章 数据库操作
  • 17.1 sqlite3数据库
    • 17.1.1 sqlite3模块
    • 17.1.2 sqlite3的数据类型
  • 17.2 MySQL数据库
  • 17.3 MariaDB数据库

第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。其他略。