2020-12-6 16:15:29

访问数据库

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

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

名字    成绩
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中用一个表格画出来:

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

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

也就是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语句,推荐Coursera课程:

英文:https://www.coursera.org/course/db

中文:http://c.open.163.com/coursera/courseIntro.htm?cid=12

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()
C:\Users\Administrator\Desktop>python
Python 2.7.12 (v2.7.12:d33e0cf91556, Jun 27 2016, 15:24:40) [MSC v.1500 64 bit (
AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import sqlite3
>>> conn = sqlite3.connect('test.db')
>>> cursor = conn.cursor()
>>> cursor.execute('create table user (id varchar(20) primary key, name varchar(
20))')
<sqlite3.Cursor object at 0x000000000247EC70>
>>> cursor.execute('insert into user (id, name) values (\'1\', \'Michael\')')
<sqlite3.Cursor object at 0x000000000247EC70>
>>> cursor.rowcount
1
>>> cursor.close()
>>> conn.commit()
>>> 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
[(u'1', u'Michael')]
>>> cursor.close()
>>> conn.close()
>>> conn = sqlite3.connect('test.db')
>>> cursor = conn.cursor()
>>> cursor.execute('select * from user where id=?', '1')
<sqlite3.Cursor object at 0x0000000002570490>
>>> values = cursor.fetchall()
>>> values
[(u'1', u'Michael')]
>>> cursor.close()
>>> conn.close()
>>>

使用Python的DB­API时,只要搞清楚Connection和Cursor对象,打开后一定记得关闭,就可以放心地使用。

使用Cursor对象执行 insert , update , delete 语句时,执行结果由 rowcount 返回影响的行数,就可以拿到执行 结果。

使用Cursor对象执行 select 语句时,通过 featchall() 可以拿到结果集。结果集是一个list,每个元素都是一个tupl e,对应一行记录。

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

cursor.execute('select * from user where id=?', '1')

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


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

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

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

By wanglei

发表评论