（十一）Python 数据库的操作

一、访问SQLite数据库

• SQLite是一个开源关系型数据库，具有零配置（Zero Configuration）、自我包含（Self-contained）和便于传输（Easy Transfer）等优点，由于其高度便携，使用方便、结构紧凑、高效和可靠，因此被广泛用于移动设备嵌入式数据库作为前端数据存储。SQLite支持规范的SQL(Structured Query Language,结构化查询语言)，可方便地支持数据库系统原型研发和移植。

• SQLite将整个数据库的表、索引、数据都存储在一个单一的.db文件中，不需要网络配置和管理，没有用户账户和密码，数据库的访问权限依赖于文件所在的操作系统。这个小型的数据库系统能够支持事务，具有原子性、一致性、隔离性和持久性，还能支持触发器、复杂查询，以及多进程并发访问。

• 关系型数据库的数据存放于多个二维表中，在表中，行称为记录(record),列称为字段(field)。

• 一个数据库中可以包含多个表。例如，在student.db数据库中除包含基本情况表base外，还包含成绩表score等数据表。各表中都包含一个学号字段，通过学号可以建立两个表乃至多个表之间的关联关系，作为一个逻辑整体提供查询应用。这样，既避免了单个表之间庞大复杂，又增加了引用数据的灵活性，减少了数据的冗余。

• 1.1、SQLite数据库连接对象及表的SQL操作
  SQLite3是Python的内置库，用import sqlite3 引用后，访问SQLite数据库通常需要经历如下的步骤：

  • 用connect()创建数据库连接对象conn。
  • 若需要对表进行创建新表、插入数据、修改或删除数据操作，可使用conn.execute()方法，并使用conn.commit()提交事务。
  • 若需要查询操作，应先使用conn.cursor()方法返回游标对象 cur,然后通过执行cur.execute()进行查询。
  • 调用cur.fetchone()、cur.fetchmany()或cur.fetchall()方法返回查询结果。
  • 最后关闭 cur 和 conn 对象。

  用 connect()函数可建立对已有数据库文件的链接对象（下例中的 conn）,若不存在该数据库文件，则新建该数据库。例如：在桌面建立一个空数据库：test.db。
  

  屏幕快照 2018-08-29 下午9.54.29.png

  import sqlite3
  conn = sqlite3.connect('/Users/wangchong/Desktop/test.db')
  

  • 由于sqlite3不是可视化呈现的，因此可使用 Navicat for SQLite 、SQLite Expert、SQLite Studio、SQLiteTool等第三方工具协助管理数据库。

  • 建立数据库对象后，用数据库连接对象的 execute （SQL 语句）方法可执行SQL语句，对数据库及表实现创建、插入、修改、删除和查询操作。SQL语句大小写不敏感，可分行，关键字之间可使用空格。在Python字符串的三重引号定符'''的支持下，可将SQL语句分行呈现，增加可读性。

  • 成功创建数据库后，应在其中合理的创建表。表结构的设计是否合理，对程序的运行效率至关重要。设计和创建表，主要应关注表中应包含哪些字段，每个字段的名字、数据类型和宽度。

  • SQLite3的表支持以下4种类型。

    • 整数型（INTEGEER）: 有符号整数，按实际存储大小自动存储为1、2、3、4、6或8字节，通常不需要指定位数。
    • 实数型（REAL）: 浮点数，以8字节指数形式存储，可指定总位数和小数位数。
    • 文本型（TEXT）: 字符串，以数据库编码方式存储（以UTF-8 支持汉字）。
    • BLOB型： 二进制对象存储，通常用来保存图片、视频、XML等数据。

    创建表的语句的通式为：

    CREATE TABLE <表>(<字段元组>)
    

    SQL语句大小写不敏感，但此处为与Python语句相区别，以大写表示。设计表结构时作为一种数据完整性约束，可指定某字段是否允许空，若不允许为空，可用 NOT NULL 关键字加以限制。在大多数表中，往往会指定一个非空且唯一的字段作为关键字（PRIMARY KRY,如学号）。为了便于快速检索，通常将表按主关键字建立索引。

    • 例如1、在桌面建立一个如下的数据库 test.db
      

      学生基本情况表 base的数据结构

      import sqlite3
      
      conn = sqlite3.connect('/Users/wangchong/Desktop/test.db')
      c = conn.cursor()
      c.execute('''CREATE TABLE base \
         (学号 TEXT(10) PRIMARY KEY  NOT NULL, \
          姓名 TEXT(10) NOT NULL,\
          性别 TEXT(1) NOT NULL,\
          专业 TEXT(6),\
          生源 TEXT(6),\
          身高 INTEGER,\
          电话 TEXT(6) )''')
      conn.commit()
      conn.close()
      

      与数据库链接对象conn.execute()方法相关的常用SQL语句通式如下：

      • 添加：

        INSERT INTO <表>(<字段元组>) VALUES (<数据元组>)
        
        如下：
        import sqlite3
        
        conn = sqlite3.connect('/Users/wangchong/Desktop/test.db')
        c = conn.cursor()
        
        print ("Opened database successfully")
        
        c.execute("INSERT INTO base (学号,姓名,性别,专业,生源,身高,电话) \
                  VALUES ('20','王冲','男','电气工程及其自动化','本科',178,'18500652880')")
        
        conn.commit()
        print ("Records created successfully")
        conn.close()
        

      • 修改：

        UPDATE <表> SET <字段>=<值>
        

      • 删除：

        DELETE FROM <表> WHERE <条件表达式>
        

    • 例如2、编写Python 程序为例1中创建的student 库的base表添加新学生学号、姓名和性别三项非空数据。
      

      base表中添加数据

      import sqlite3
      # 链接数据库
      conn = sqlite3.connect('/Users/wangchong/Desktop/test.db')
      while True:
            id = input('请输入新生学号：（输入0退出程序）\n')
            if id == '0':
                 break
            name = input('请输入新生的姓名：\n')
            gender = input('请输入新生的性别：\n')
            # 格式化构建 SQL 字符串
            SQL = '''insert into base \
                    (学号,姓名,性别) values ('%s','%s','%s')''' % (id,name,gender)
            # 插入数据
            conn.execute(SQL)
            # 提交事务
            conn.commit()
      
      conn.close()
      

      运行结果如下：

      请输入新生学号：（输入0退出程序）
      21
      请输入新生的姓名：
      邵豪杰
      请输入新生的性别：
      男
      请输入新生学号：（输入0退出程序）
      22
      请输入新生的姓名：
      马也
      请输入新生的性别：
      女
      请输入新生学号：（输入0退出程序）
      

      在格式化构建SQL字符串时应该注意，values 后面的数据元组应与前面的表达字段元组顺序一直，且TEXT类型的数据要加单引号界定符。

• 1.2、游标对象和SQL查询

  与游标对象 cur.execute() 方法相关的SQL语句通式为：

  SELECT [DISTINCT] <目标列表表达式> [AS <列名>]
  [,<目标列表达式> [AS <列名> ...] FROM <表名> [,<表名>...]
  [WHERE <条件表达式> [AND | OR <条件表达式>...]
  [GROUP BY 列名 [HAVING  <条件表达式>>
  [ORDER BY 列名 [ASC | DESC>
  

  其中DISTINCT表示不包括重复行；
  <目标列表达式>包含对目标列的 AVG、COUNT、SUM、MIN、MAX等聚合函数；
  <GROUP BY 列名> 为对聚合函数查询的分组；
  [HAVING<条件表达式>] 为分组筛选的条件；
  [ORDER BY 列名 [ASC | DESC>表示对查询结果的排序，ASC 为升序（默认），DESC为降序。
  执行游标对象 cur.execute(<SELECT 查询SQL语句>)后，用 cur.fetchall()或cur.fetchone() 可接收查询结果。其中，cur.fetchall() 返回的是每条记录为 一个元组作为列表元素的数据集列表，而cur.fetchone()则只返回第一条记录的元组类型结果。

  • 例如3、创建Python 程序对前面例子创建的student 库中根据所输入的专业查询学生学号、姓名和性别。

    
    base表

    import sqlite3
    conn = sqlite3.connect('/Users/wangchong/Desktop/test.db')
    while True:
        major = input('请输入查询专业：（输入0退出程序）\n')
        if major == '0':
            break
        SQL="SELECT * from base where 专业='%s'" % major
        cur = conn.execute(SQL)
        list1=cur.fetchall()
       print('学号 ',' 姓名 ',' 性别 ',' 专业 ')
       for rec in list1:
            print(rec[0],rec[1],rec[2],rec[3])
    
    conn.close()
    

    运行的结果如下：

    请输入查询专业：（输入0退出程序）
    电气工程及其自动化
    学号   姓名   性别   专业 
    20 王冲 男 电气工程及其自动化
    4 李云东 女 电气工程及其自动化
    请输入查询专业：（输入0退出程序）
    

二、访问Access、MySQL 和 SQLServer数据库

Python 可支持访问不同的数据库。但由于不同的数据库及其服务的通信协议各有不同，早期版本访问不同数据库需要不同的代码支持，二Python DB-API作为 Python标准数据库接口的诞生，为Python数据库应用提供了标准的编程接口，支持MySQL 、PostgreSQL、Microsoft SQL Server 、Oracle 、Sybase 等常用数据库。即使所需链接的数据库底层服务协议不同，也能够标准的DB-API 接口实现访问。
利用微软操作系统对各种数据库驱动的开放数据库连接接口（ODBC，Open Database Connecttivity） 也可以实现对数据库的标准访问。通过标准的 DB-API 访问各类数据库通常如上节访问 SQLite 一样，也需经历如下步骤：

 1、用connect创建数据库连接对象 conn;
 2、如果需要对表进行创建、插入数据、修改或删除数据、可使用 conn.execute()方法，并使用conn.commit()提交事务。
 3、如果需要查询操作，应先使用 conn.cursor()方法返回游标对象 cur,然后通过执行 cur.execute()进行查询。
 4、调用 cur.fetchone()、cur.fetchmany()或cur.fetchall()方法返回查询结果。
 其中，对不同类型的数据库需要引用不同的标准库，用不同的语句创建链接对象。

• 2.1、用 ODBC 创建链接对象
  对 Access 数据库文件的访问，利用 Windows开放数据库连接接口 ODBC 对文件、dBase、Foxpro、SQL Server 等微软数据库的访问，均可预先在控制面板中建立 ODBC 数据源，如下：

  
  ODBC数据源

  预先安装 pyodbc 库 ，然后用下列语句建立数据库连接对象：

  import pyodbc
  conn= pyodbc.connect('DNS=student')
  

• 2.2、对 MySQL 创建链接对象
  MySQL 数据库是近来流行的开源关系型数据库服务，对其建立连接对象可预先安装 PyMySQL库，然后用下列语句创建：

  import pymysql
  conn= pymysql.connect(host=服务器地址或域名,port=3306,user='root',passwd=密码,db=数据库名字)
  

• 2.3、对 MS SQL Server 创建链接对象
  MS SQL Server 数据库是微软主流的大型关系型数据库服务，对其建立连接对象可预先安装 pymssql 库 ，然后用下列语句创建：

  import pymssql
  conn= pymssql.connect(host=<服务器地址或域名>,database=<数据库名>,user=<用户名>,password=<密码>)
  

  数据库连接对象建立后，对各类数据库的访问操作方法均与前面介绍的对SQLite的访问操作类似。这里不再重复叙述。值的注意的是，由于目前各类数据库的编码不统一，因此对中文查询的支持尚不够理想。

• 例如4、用Python 程序分别在 ODBC 链接的 student.db、MySQL数据库 student(数据库地址 192.168.145.253，root 密码为test) 和 MS SQL Server 数据库 student （数据库地址 192.168.145.253，sa 密码为 123456）的base表中，查询身高（height）181cm以上的学生学号、姓名和性别。

  # 1、用 obdc 连接数据库（Access 等）
  # import pyodbc
  # conn=pyodbc.connect('DNS=student')
  
  # 2、连接 MySQL 数据库  
  import pymysql         
  conn=pymysql.connect(host='192.168.145.253',port=3306,user='root',passwd='test',db='base')
  
  # 3、连接 MS SQL Server  数据库
  
  # import pymssql
  # conn=pymssql.connect(host=".",database="student",user='sa',password='123456')
  
  cur = conn.cursor()
  cur.execute("SELECT * FROM BASE WHERE HEIGHT>181")
  list1=cur.fetchall()
  print('学号 ',' 姓名 ',' 性别 ',' 专业')
  for rec in list1:
     print(rec[0],rec[1],rec[2],rec[3])
  conn.close()