Python学习笔记：访问数据库

前言

最近在学习深度学习，已经跑出了几个模型，但Pyhton的基础不够扎实，因此，开始补习Python了，大家都推荐廖雪峰的课程，因此，开始了学习，但光学有没有用，还要和大家讨论一下，因此，写下这些帖子，廖雪峰的课程连接在这里：廖雪峰
Python的相关介绍，以及它的历史故事和运行机制，可以参见这篇：python介绍
Python的安装可以参见这篇：Python安装
Python的运行模式以及输入输出可以参见这篇：Python IO
Python的基础概念介绍，可以参见这篇：Python 基础
Python字符串和编码的介绍，可以参见这篇：Python字符串与编码
Python基本数据结构：list和tuple介绍，可以参见这篇：Python list和tuple
Python控制语句介绍：ifelse，可以参见这篇：Python 条件判断
Python控制语句介绍：循环实现，可以参见这篇：Python循环语句
Python数据结构：dict和set介绍Python数据结构dict和set
Python函数相关：Python函数
Python高阶特性：Python高级特性
Python高阶函数：Python高阶函数
Python匿名函数：Python匿名函数
Python装饰器：Python装饰器
Python偏函数：Python偏函数
Python模块：Python模块
Python面向对象编程（1）：Python面向对象
Python面向对象编程（2）：Python面向对象（2）
Python面向对象编程（3）：Python面向对象（3）
Python面向对象编程（4）：Pyhton面向对象（4）
Python面向对象高级编程（上）：Python面向对象高级编程（上）
Python面向对象高级编程（中上）：Python面向对象高级编程（中上）
Python面向对象高级编程（中下）：Python面向对象高级编程（中下）
Python面向对象高级编程（完）：Python面向对象高级编程（完）
Python错误调试（起）：Python调试：起
Python错误调试（承）：Python调试：承
Python错误调试（转）：Python调试：转
Python错误调试（合）：python调试：合
Python文件IO编程：Python文件IO
Python文件IO编程2：Python文件IO2
Python文件IO编程3：PYthon文件IO3
Python进程和线程（起）：Python进程和线程起
Python进程和线程（承）：Python进程和线程承
Python进程和线程（转）：Python进程和线程转
Python进程和线程（合）：Python进程和线程合
Python正则表达式：Python正则表达式
Python学习笔记:常用内建模块1：Python学习笔记：常用内建模块1
Python学习笔记:常用内建模块2：Python学习笔记：常用内建模块2
Python学习笔记:常用内建模块3：Python学习笔记：常用内建模块3
Python学习笔记:常用内建模块4：Python学习笔记: 常用内建模块4
Python学习笔记:常用内建模块5：Python学习笔记: 常用内建模块5
Python学习笔记:常用内建模块6:Python学习笔记：常用内建模块6
Python学习笔记:第三方模块1：Python常用第三方模块
Python学习笔记:第三方模块2：Python常用第三方模块
Python学习笔记:第三方模块3：Python常用第三方模块
Pytho学习笔记：网络编程：Python网络编程
Python学习笔记：电子邮件：Python电子邮件1
Python学习笔记：SMTP服务器：PythonSMTP服务器
Python学习笔记：POP3服务器：PythonPOP3服务器

目录

访问数据库

程序运行的时候，数据都是在内存中的。当程序终止的时候，通常都需要将数据保存到磁盘上，无论是保存到本地磁盘，还是通过网络保存到服务器上，最终都会将数据写入磁盘文件。

而如何定义数据的存储格式就是一个大问题。如果我们自己来定义存储格式，比如保存一个班级所有学生的成绩单：

名字	成绩
Michael	99
Bob	85
Bart	59
Lisa	87

你可以用一个文本文件保存，一行保存一个学生，用,隔开：

Michael,99
Bob,85
Bart,59
Lisa,87

你还可以用JSON格式保存，也是文本文件：

[
    {"name":"Michael","score":99},
    {"name":"Bob","score":85},
    {"name":"Bart","score":59},
    {"name":"Lisa","score":87}
]

你还可以定义各种保存格式，但是问题来了：

存储和读取需要自己实现，JSON还是标准格式，自己定义的格式就各式各样了；

不能做快速查询，只有把数据全部读到内存中才能自己遍历，但有时候数据的大小远远超过了内存（比如蓝光电影，40GB的数据），根本无法全部读入内存。

为了便于程序保存和读取数据，而且，能直接通过条件快速查询到指定的数据，就出现了数据库（Database）这种专门用于集中存储和查询的软件。

数据库软件诞生的历史非常久远，早在1950年数据库就诞生了。经历了网状数据库，层次数据库，我们现在广泛使用的关系数据库是20世纪70年代基于关系模型的基础上诞生的。
关系模型有一套复杂的数学理论，但是从概念上是十分容易理解的。举个学校的例子：
假设某个XX省YY市ZZ县第一实验小学有3个年级，要表示出这3个年级，可以在Excel中用一个表格画出来：

grade

每个年级又有若干个班级，要把所有班级表示出来，可以在Excel中再画一个表格：

class

这两个表格有个映射关系，就是根据Grade_ID可以在班级表中查找到对应的所有班级：

grade-classes

也就是Grade表的每一行对应Class表的多行，在关系数据库中，这种基于表（Table）的一对多的关系就是关系数据库的基础。

根据某个年级的ID就可以查找所有班级的行，这种查询语句在关系数据库中称为SQL语句，可以写成：

SELECT * FROM classes WHERE grade_id = ‘1’;

结果也是一个表：

---------+----------+----------
grade_id | class_id | name
---------+----------+----------
1        | 11       | 一年级一班
---------+----------+----------
1        | 12       | 一年级二班
---------+----------+----------
1        | 13       | 一年级三班
---------+----------+----------

类似的，Class表的一行记录又可以关联到Student表的多行记录：

由于本教程不涉及到关系数据库的详细内容，如果你想从零学习关系数据库和基本的SQL语句，如果你想从零学习关系数据库和基本的SQL语句，请自行搜索相关课程。

NoSQL

你也许还听说过NoSQL数据库，很多NoSQL宣传其速度和规模远远超过关系数据库，所以很多同学觉得有了NoSQL是否就不需要SQL了呢？千万不要被他们忽悠了，连SQL都不明白怎么可能搞明白NoSQL呢？

数据库类别

既然我们要使用关系数据库，就必须选择一个关系数据库。目前广泛使用的关系数据库也就这么几种：

付费的商用数据库：

Oracle，典型的高富帅；

SQL Server，微软自家产品，Windows定制专款；

DB2，IBM的产品，听起来挺高端；

Sybase，曾经跟微软是好基友，后来关系破裂，现在家境惨淡。

这些数据库都是不开源而且付费的，最大的好处是花了钱出了问题可以找厂家解决，不过在Web的世界里，常常需要部署成千上万的数据库服务器，当然不能把大把大把的银子扔给厂家，所以，无论是Google、Facebook，还是国内的BAT，无一例外都选择了免费的开源数据库：

MySQL，大家都在用，一般错不了；

PostgreSQL，学术气息有点重，其实挺不错，但知名度没有MySQL高；

sqlite，嵌入式数据库，适合桌面和移动应用。

作为Python开发工程师，选择哪个免费数据库呢？当然是MySQL。因为MySQL普及率最高，出了错，可以很容易找到解决方法。而且，围绕MySQL有一大堆监控和运维的工具，安装和使用很方便。

为了能继续后面的学习，你需要从MySQL官方网站下载并安装MySQL Community Server 5.6，这个版本是免费的，其他高级版本是要收钱的（请放心，收钱的功能我们用不上）。

使用sqlite

SQLite是一种嵌入式数据库，它的数据库就是一个文件。由于SQLite本身是C写的，而且体积很小，所以，经常被集成到各种应用程序中，甚至在iOS和Android的App中都可以集成。
Python就内置了SQLite3，所以，在Python中使用SQLite，不需要安装任何东西，直接使用。
在使用SQLite前，我们先要搞清楚几个概念：
表是数据库中存放关系数据的集合，一个数据库里面通常都包含多个表，比如学生的表，班级的表，学校的表，等等。表和表之间通过外键关联。
要操作关系数据库，首先需要连接到数据库，一个数据库连接称为Connection；
连接到数据库后，需要打开游标，称之为Cursor，通过Cursor执行SQL语句，然后，获得执行结果。
Python定义了一套操作数据库的API接口，任何数据库要连接到Python，只需要提供符合Python标准的数据库驱动即可。
由于SQLite的驱动内置在Python标准库中，所以我们可以直接来操作SQLite数据库。
我们在Python交互式命令行实践一下：

# 导入SQLite驱动:
>>> import sqlite3
# 连接到SQLite数据库
# 数据库文件是test.db
# 如果文件不存在，会自动在当前目录创建:
>>> conn = sqlite3.connect('test.db')
# 创建一个Cursor:
>>> cursor = conn.cursor()
# 执行一条SQL语句，创建user表:
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(20))')
<sqlite3.Cursor object at 0x10f8aa260>
# 继续执行一条SQL语句，插入一条记录:
>>> cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
<sqlite3.Cursor object at 0x10f8aa260>
# 通过rowcount获得插入的行数:
>>> cursor.rowcount
1
# 关闭Cursor:
>>> cursor.close()
# 提交事务:
>>> conn.commit()
# 关闭Connection:
>>> conn.close()

我们再试试查询记录：

>>> conn = sqlite3.connect('test.db')
>>> cursor = conn.cursor()
# 执行查询语句:
>>> cursor.execute('select * from user where id=?', ('1',))
<sqlite3.Cursor object at 0x10f8aa340>
# 获得查询结果集:
>>> values = cursor.fetchall()
>>> values
[('1', 'Michael')]
>>> cursor.close()
>>> conn.close()

使用Python的DB-API时，只要搞清楚Connection和Cursor对象，打开后一定记得关闭，就可以放心地使用。
使用Cursor对象执行insert，update，delete语句时，执行结果由rowcount返回影响的行数，就可以拿到执行结果。
使用Cursor对象执行select语句时，通过featchall()可以拿到结果集。结果集是一个list，每个元素都是一个tuple，对应一行记录。

如果SQL语句带有参数，那么需要把参数按照位置传递给execute()方法，有几个?占位符就必须对应几个参数，例如：

cursor.execute('select * from user where name=? and pwd=?', ('abc', 'password'))

SQLite支持常见的标准SQL语句以及几种常见的数据类型。具体文档请参阅SQLite官方网站。

小结

在Python中操作数据库时，要先导入数据库对应的驱动，然后，通过Connection对象和Cursor对象操作数据。

要确保打开的Connection对象和Cursor对象都正确地被关闭，否则，资源就会泄露。

如何才能确保出错的情况下也关闭掉Connection对象和Cursor对象呢？请回忆try:…except:…finally:…的用法。

练习

请编写函数，在Sqlite中根据分数段查找指定的名字：

# -*- coding: utf-8 -*-

import os, sqlite3

db_file = os.path.join(os.path.dirname(__file__), 'test.db')
if os.path.isfile(db_file):
    os.remove(db_file)

# 初始数据:
conn = sqlite3.connect(db_file)
cursor = conn.cursor()
cursor.execute('create table user(id varchar(20) primary key, name varchar(20), score int)')
cursor.execute(r"insert into user values ('A-001', 'Adam', 95)")
cursor.execute(r"insert into user values ('A-002', 'Bart', 62)")
cursor.execute(r"insert into user values ('A-003', 'Lisa', 78)")
cursor.close()
conn.commit()
conn.close()

def get_score_in(low, high):
    ' 返回指定分数区间的名字，按分数从低到高排序 '
----
 def get_score_in(low, high):
  try: value = [] conn = sqlite3.connect(db_file)
  cursor = conn.cursor()
  cursor.execute("select name from user where score>=? and score<=? order by score ASC", (low,high)) values = cursor.fetchall() 
  for val in values: 
  value.append(val[0])
   return value
    finally: 
conn.close()
----
# 测试:
assert get_score_in(80, 95) == ['Adam'], get_score_in(80, 95)
assert get_score_in(60, 80) == ['Bart', 'Lisa'], get_score_in(60, 80)
assert get_score_in(60, 100) == ['Bart', 'Lisa', 'Adam'], get_score_in(60, 100)

print('Pass')