（十一）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。
  

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
  

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,'18500652890')")
conn.commit()
print ("Records created successfully")
conn.close()

• 修改：

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

• 删除：

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

• 例如2、编写Python 程序为例1中创建的student 库的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 库中根据所输入的专业查询学生学号、姓名和性别。
  

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 数据源，如下：
  

• 预先安装 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()