一、访问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()
网友评论