7.3 MySQL数据库
尽管用文件形式将数据保存到磁盘,已经是一种不错的方式。但是,人们还是发明了更具有格式化特点,并且写入和读取更快速便捷的东西——数据库。维基百科对数据库有比较详细的说明:
数据库指的是以一定方式储存在一起、能为多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合。
到目前为止,地球上有以下三种类型的数据。
- 关系型数据库:MySQL、Microsoft Access、SQL Server、Oracle……
- 非关系型数据库:MongoDB、BigTable(Google)……
- 键值数据库:Apache Cassandra(Facebook)、LevelDB(Google)……
7.3.1 MySQL概况
MySQL是一个使用非常广泛的数据库,很多网站都使用它。关于这个数据库有很多传说,例如维基百科上有这么一段:
MySQL原本是一个开放源代码的关系数据库管理系统,原开发者为瑞典的MySQL AB公司,该公司于2008年被太阳微系统(Sun Microsystems)收购。2009年,甲骨文公司(Oracle)收购太阳微系统公司,MySQL成为Oracle旗下产品。
MySQL在过去由于性能高、成本低、可靠性好,已经成为最流行的开源数据库,因此被广泛地应用在Internet上的中小型网站中。随着MySQL的不断成熟,也逐渐被用于更多大规模网站和应用,比如维基百科、Google和Facebook等网站。非常流行的开源软件组合LAMP中的“M”指的就是MySQL。
但被甲骨文公司收购后,Oracle大幅调涨MySQL商业版的售价,且甲骨文公司不再支持另一个自由软件项目OpenSolaris的发展,因此导致自由软件社区们对于Oracle是否还会持续支持MySQL社区版(MySQL之中唯一的免费版本)有所隐忧,因此原先一些使用MySQL的开源软件逐渐转向其他的数据库。例如维基百科已于2013年正式宣布将从MySQL迁移到MariaDB数据库。
不管怎样,MySQL依然是一个不错的数据库选择,足够支持读者完成一个不小的网站。
7.3.2 安装
你的电脑或许不会天生就有MySQL,它本质上也是一个程序,若有必要,需安装。
我用Ubuntu操作系统演示,因为我相信读者将来在真正的工程项目中,多数情况下要操作Linux系统的服务器,并且,我酷爱用Ubuntu。本书的目标是from beginner to master,不管是不是真的master,也要装得像,Linux能够给你撑门面,这也是推荐使用Ubuntu的原因。
第一步,在shell端运行如下命令:
- sudo apt-get install mysql-server
运行完毕就安装好了这个数据库,是不是很简单呢?当然,还要进行配置。
第二步,配置MySQL
安装之后,运行:
- service mysqld start
启动MySQL数据库,然后进行下面的操作,对其进行配置。
默认的MySQL安装之后根用户是没有密码的,注意,这里有一个名词“根用户”,其用户名是:root。运行:
- $mysql -u root
进入MySQL之后,会看到“>”符号开头,这就是MySQL的命令操作界面了。
下面设置MySQL中的root用户密码,否则,MySQL服务无安全可言了。
- mysql> GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY "123456";
用123456作为root用户的密码,应该是非常愚蠢的,在真正的项目中最好别这样做,要用大小写字母与数字混合的密码,且不少于8位。以后如果再登录数据库,就可以用刚才设置的密码了。
7.3.3 运行
安装完就要运行它,并操作这个数据库。
- $ mysql -u root -p
- Enter password:
输入数据库的密码,之后出现:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 373
- Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)
- Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
- Oracle is a registered trademark of Oracle Corporation and/or its
- affiliates. Other names may be trademarks of their respective
- owners.
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
- mysql>
恭喜你,已经进入到数据操作界面了,接下来就可以对这个数据进行操作了。例如:
- mysql> show databases;
- +--------------------+
- | Database |
- +--------------------+
- | information_schema |
- | carstore |
- | cutvideo |
- | itdiffer |
- | mysql |
- | performance_schema |
- | test |
- +--------------------+
“show databases;”(最后的半角分号别忘记了)命令表示列出当前已经有的数据库。
对数据库的操作,除了用命令之外,还可以使用一些可视化工具,比如phpmyadmin就是不错的。
更多数据库操作的知识,这里就不再介绍了,读者可以参考有关书籍。
MySQL数据库已经安装好,但是Python还不能操作它,还要继续安装Python操作数据库的模块——python-MySQLdb
7.3.4 安装python-MySQLdb
python-MySQLdb是一个接口程序,Python通过它对MySQL数据实现各种操作。
在编程中会遇到很多类似的接口程序,通过接口程序对另外一个对象进行操作。接口程序就好比钥匙,如果要开锁,直接用手指去捅肯定是不行的,必须借助工具插入到锁孔中,把锁打开,门开了,就可以操作门里面的东西了,那么打开锁的工具就是接口程序。谁都知道,用对应的钥匙开锁是最好的,如果用别的工具(比如锤子)或许不便利(当然,具有特殊开锁能力的人除外),也就是接口程序,编码水平等都是考虑因素。
python-MySQLdb就是打开MySQL数据库的钥匙。
如果要源码安装,其源码下载地址:https://pypi.python.org/pypi/MySQL-python/。下载之后就可以安装了。
在Ubuntu操作系统下还可以用软件仓库来安装。
- sudo apt-get install build-essential python-dev libmysqlclient-dev
- sudo apt-get install python-MySQLdb
也可以用pip来安装:
- pip install mysql-python
安装之后,在Python交互模式下:
- >>> import MySQLdb
如果不报错,那么恭喜你,已经安装好了。如果报错,那么恭喜你,可以借着错误信息提高自己的计算机水平。
7.3.5 连接数据库
连接数据库之前要先建立数据库。
- $ mysql -u root -p
- Enter password:
进入到数据库操作界面:
- mysql>
输入如下命令,建立一个数据库:
- mysql> create database qiwsirtest character set utf8;
- Query OK, 1 row affected (0.00 sec)
注意上面的指令,如果仅仅输入create database qiwsirtest也可以,但是我在后面增加了character set utf8,意思是所建立的数据库qiwsirtest,编码是utf-8,这样存入汉字就不是乱码了。
看到那一行提示:Query OK,1 row affected(0.00 sec),说明这个数据库已经建立好了,名字叫作qiwsirtest
数据库建立之后,就可以用Python通过已经安装的python-MySQLdb来连接这个名字叫作qiwsirtest的库了。
- >>> import MySQLdb
- >>> conn = MySQLdb.connect(host="localhost", user="root", passwd="123123", db="qiwsirtest", port=3306, charset="utf8")
下面逐个解释上述命令的含义。
- host:等号的后面应该填写MySQL数据库的地址,因为数据库就在本机上,所以使用localhost,注意引号。如果在其他的服务器上,这里应该填写IP地址。一般中小型的网站,数据库和程序都是在同一台服务器(计算机)上,就使用localhost了。
- user:登录数据库的用户名,这里一般填写“root”,还是要注意引号。当然,如果读者命名了别的用户名,就更改为相应用户。但是,不同用户的权限可能不同,所以,在程序中,如果要操作数据库,还要注意所拥有的权限。在这里用root,就什么权限都有了。不过,这种做法在大型系统中是应该避免的。
- passwd:user账户登录MySQL的密码。例子中用的密码是“123123”,不要忘记引号。
- db:就是刚刚通create命令建立的数据库,数据库名字是“qiwsirtest”,还是要注意引号。如果你建立的数据库名字不是这个,就写自己所建数据库的名字。
- port:一般情况,MySQL的默认端口是3306。当MySQL被安装到服务器之后,为了能够允许网络访问,服务器(计算机)要提供一个访问端口给它(服务器管理员可以进行配置端口)。
- charset:这个设置,在很多教程中都不写,结果在真正进行数据存储的时候,发现有乱码。这里将qiwsirtest这个数据库的编码设置为utf-8格式,这样就允许存入汉字而无乱码了。注意,在MySQL设置中,utf-8写成utf8,没有中间的横线,但是在Python文件开头和其他地方设置编码格式的时候要写成utf-8。注:connect中的所有参数,可以只按照顺序把值写入。但是,我推荐读者的写法还是上面的方式,以免出了乱子自己还糊涂。
其实,关于connect的参数还有别的,读者可以到MySQLdb官方查看文档,此处就不再赘述。特别提醒,官方文档是最好的教材,最值得反复阅读。
至此,已经完成了数据库的连接。
7.3.6 数据库表
就数据库而言,连接之后就要对其操作。但是,目前名字叫作qiwsirtest的数据库仅仅是空架子,没有什么可操作的,要操作它,就必须在里面建立“表”,什么是数据库的表呢?下面摘抄维基百科对数据库表的简要解释。
在关系数据库中,数据库表是一系列二维数组的集合,用来代表和储存数据对象之间的关系。它由纵向的列和横向的行组成,例如一个有关作者信息的名为authors的表中,每个列包含的是所有作者的某个特定类型的信息,比如“姓氏”,而每行则包含了某个特定作者的所有信息:姓、名、住址等。
对于特定的数据库表,列的数目一般事先固定,各列之间可以由列名来识别。而行的数目可以随时动态变化,通常每行都可以根据某个(或某几个)列中的数据来识别,称为候选键。
在qiwsirtest中建立一个存储用户名、用户密码、用户邮箱的表,其结构用二维表格表现如下:
特别说明,这里为了简化细节、突出重点,对密码不加密,直接明文保存,虽然这种方式是很不安全的。据小道消息,有的网站居然用明文保存密码,这么做的目的是比较可恶的。就让我在这里,仅仅在这里可恶一次。
因为直接操作数据不是本书重点,但是关联到后面的操作,为了让读者在阅读上连贯,快速地说明建立数据库表并输入内容。
- mysql> use qiwsirtest;
- Database changed
- mysql> show tables;
- Empty set (0.00 sec)
用show tables命令显示这个数据库中是否有数据表了,查询结果显示为空。
用如下命令建立一个数据表,这个数据表的内容就是上面所说明的。
mysql>create table users(id int(2) not null primary key auto_increment,username varchar(40),password text,email text)default charset=utf8;
- Query OK, 0 rows affected (0.12 sec)
建立的这个数据表名称是:users,其中包含上述字段,可以用下面的方式看一看这个数据表的结构。
查询显示,在qiwsirtest这个数据库中已经有一个表,它的名字是:users。
显示出来表users的结构。
特别提醒:上述所有字段设置仅为演示,在实际开发中,要根据具体情况来确定字段的属性。
如此就得到了一个空表,可以查询看看:
- mysql> select * from users;
- Empty set (0.01 sec)
向里面插入一条信息:
- mysql> insert into users(username,password,email)
- values("qiwsir","123123", "qiwsir@gmail.com");
- Query OK, 1 row affected (0.05 sec)
- mysql> select * from users;
这样就得到了一个有内容的数据库表。
7.3.7 操作数据库
连接数据库。
- >>> import MySQLdb
- >>> conn = MySQLdb.connect(host="localhost",user="root",passwd="123123",db= "qiwsirtest",charset="utf8")
Python建立了与数据的连接,其实是建立了一个MySQLdb.connect()的实例对象,或者泛泛地称之为连接对象,Python就是通过连接对象和数据库对话。这个对象常用的方法如下。
- commit():如果数据库表进行了修改,提交保存当前的数据。当然,如果此用户没有权限就作罢,什么也不会发生。
- rollback():如果有权限,就取消当前的操作,否则报错。
- cursor([cursorclass]):返回连接的游标对象。通过游标执行SQL查询并检查结果。游标比连接支持更多的方法,而且可能在程序中更好用。
- close():关闭连接。此后,连接对象和游标都不再可用了。Python和数据之间的连接建立起来之后,若要操作数据库,就需要让Python对数据库执行SQL语句。
Python是通过游标执行SQL语句的,所以,连接建立之后,就要利用连接对象得到游标对象,方法如下:
- >>> cur = conn.cursor()
此后,就可以利用游标对象的方法对数据库进行操作,那么还得了解游标对象的常用方法,如表7-1所示。
表7-1 游标对象的常用方法
1.插入
例如,要在数据表users中插入一条记录,使得:username="python",password="123456",email="python@gmail.com",这样做:
- >>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)", ("python","123456","python@gmail.com"))
- 1L
没有报错,并且返回一个"1L"结果,说明有一行记录操作成功。不妨进入到“mysql>”交互方式查看(读者可以在另外一个shell中进行操作):
- mysql> select * from users;
怎么没有看到增加的那一条呢?哪里错了?上面也没有报错。
特别注意,通过“cur.execute()”对数据库进行操作之后,没有报错,完全正确,但是不等于数据就已经提交到数据库中了,还必须要用到“MySQLdb.connect”的一个属性:commit(),将数据提交上去,也就是进行了“cur.execute()”操作之后,必须要将数据提交才能有效改变数据库表。
- >>> conn.commit()
再到“mysql>”中运行“select*from users”试一试:
- mysql> select * from users;
果然如此。
这就如同编写一个文本一样,将文字写到文本上,并不等于文字已经保留在文本文件中了,必须执行“CTRL-S”才能保存。所有以“execute()”执行的各种sql语句之后,要让已经执行的效果保存,都必须运行连接对象的“commit()”方法。
再尝试一下插入多条的那个命令“executemany(query,args)”。
- >>> cur.executemany("insert into users (username,password,email) values (%s,%s,%s)",(("google","111222","g@gmail.com"),("facebook","222333","f@face.book"),("github","333444","git@hub.com"),("docker","444555","doc@ker.com")))
- 4L
- >>> conn.commit()
到“mysql>”里面看结果:
- mysql> select * from users;
成功插入了多条记录。在“executemany(query,pseq)”中,query还是一条sql语句,但是pseq这时候是一个元组,特别注意括号——一环套一环的括号,这个元组里面的元素也是元组,每个元组分别对应sql语句中的字段列表。
除了插入命令,其他对数据操作的命令都可以用类似上面的方式,比如删除、修改等。
2.查询
如果要从数据库中查询数据,也用游标方法来操作。
- >>> cur.execute("select * from users")
- 7L
说明从users表汇总查询出来了7条记录。但是,这似乎有点不友好,7条记录在哪里呢?如果在“mysql>”下操作查询命令,一下子就把7条记录列出来了,在这里怎么显示Python的查询结果呢?
要用到游标对象的fetchall()、fetchmany(size=None)、fetchone()、scroll(value,mode='relative')等方法。
- >>> cur.execute("select * from users")
- 7L
- >>> lines = cur.fetchall()
至此已经将查询到的记录赋值给变量lines了,如果要把它们显示出来,就要用到曾经学习过的循环语句。
- >>> for line in lines:
- ... print line
- ...
- (1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')
- (2L, u'python', u'123456', u'python@gmail.com')
- (3L, u'google', u'111222', u'g@gmail.com')
- (4L, u'facebook', u'222333', u'f@face.book')
- (5L, u'github', u'333444', u'git@hub.com')
- (6L, u'docker', u'444555', u'doc@ker.com')
- (7L, u'\u8001\u9f50', u'9988', u'qiwsir@gmail.com')
很好,果然逐条显示出来了。请读者注意,第七条中的u'\u8001\u95f5',在这里是汉字,只是由于我的shell不能显示罢了,不必惊慌,也不必搭理它。
只想查出第一条,可以吗?当然可以,再看下面:
- >>> cur.execute("select * from users where id=1")
- 1L
- >>> line_first = cur.fetchone() #只返回一条
- >>> print line_first
- (1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')
为了对上述过程了解深入,做下面的实验:
- >>> cur.execute("select * from users")
- 7L
- >>> print cur.fetchall()
- ((1L, u'qiwsir', u'123123', u'qiwsir@gmail.com'), (2L, u'python', u'123456', u'python@gmail.com'), (3L, u'google', u'111222', u'g@gmail.com'), (4L, u'facebook', u'222333', u'f@face.book'), (5L, u'github', u'333444', u'git@hub.com'), (6L, u'docker', u'444555', u'doc@ker.com'), (7L, u'\u8001\u9f50', u'9988', u'qiwsir@gmail.com'))
原来,用cur.execute()从数据库查询出来的东西,被“保存在了cur所能找到的某个地方”,要找出这些被保存的东西,需要用cur.fetchall()(或者fechone等),并且找出来之后,作为对象存在。从上面的实验探讨发现,返回值是一个元组对象,里面的每个元素,又是一个一个的元组对象,因此,用for循环就可以一个一个拿出来了。
接着上面的操作,再打印一遍。
- >>> print cur.fetchall()
- ()
怎么是空?不是说作为对象已经存在于内存中了吗?难道这个内存中的对象仅一次有效吗?
不要着急,这就是神奇所在。
通过游标找出来的对象,在读取的时候有一个特点,就是那个游标会移动。在第一次操作了print cur.fetchall()后,因为是将所有的都打印出来,游标就从第一条移动到最后一条。当print结束之后,游标已经在最后一条的后面了。接下来如果再次打印,就空了,最后一条后面没有东西了。
下面还要进行实验,检验上面所说:
- >>> cur.execute('select * from users')
- 7L
- >>> print cur.fetchone()
- (1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')
- >>> print cur.fetchone()
- (2L, u'python', u'123456', u'python@gmail.com')
- >>> print cur.fetchone()
- (3L, u'google', u'111222', u'g@gmail.com')
这次不再一次性全部打印出来了,而是每次打印一条,从结果中可以看出来,那个游标果然在一条一条向下移动呢。注意,在这次实验中重新运行了查询语句。
那么,既然操作存储在内存中的对象时游标会移动,能不能让游标向上移动,或者移动到指定位置呢?这就是scroll()。
- >>> cur.scroll(1)
- >>> print cur.fetchone()
- (5L, u'github', u'333444', u'git@hub.com')
- >>> cur.scroll(-2)
- >>> print cur.fetchone()
- (4L, u'facebook', u'222333', u'f@face.book')
果然,这个函数能够移动游标,请仔细观察,上面的方式是让游标相对于当前位置向上或者向下移动。即cur.scroll(n)或者cur.scroll(n,"relative"),意思是相对于当前位置向上或者向下移动,若n为正数,则表示向下(向前),若n为负数,则表示向上(向后)
还有一种方式可以实现“绝对”移动,而不是“相对”移动:增加一个参数"absolute"。
但在Python中,序列对象的顺序是从0开始的。
- >>> cur.scroll(2, "absolute") #回到序号是2,但指向第三条
- >>> print cur.fetchone() #打印,果然是
- (3L, u'google', u'111222', u'g@gmail.com')
- >>> cur.scroll(1, "absolute")
- >>> print cur.fetchone()
- (2L, u'python', u'123456', u'python@gmail.com')
- >>> cur.scroll(0, "absolute") #回到序号是0,即指向第一条
- >>> print cur.fetchone()
- (1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')
至此,已经熟悉了cur.fetchall()和cur.fetchone()以及cur.scroll()的几个方法,还有一个方法,即游标在序号是1的位置,指向第二条。
- >>> cur.fetchmany(3)
- ((2L, u'python', u'123456', u'python@gmail.com'), (3L, u'google', u'111222', u'g@gmail.com'), (4L, u'facebook', u'222333', u'f@face.book'))
上面这个操作,就是实现了从当前位置(游标指向tuple序号为1的位置,即第二条记录)开始,含当前位置,向下列出3条记录。
读取数据,好像有点啰嗦,但细细琢磨,还是有道理的。
Python总是能够为我们着想的,在连接对象的游标方法中提供了一个参数,可以实现将读取到的数据变成字典形式,这样就提供了另外一种读取方式。
- >>> cur = conn.cursor(cursorclass=MySQLdb.cursors.DictCursor)
- >>> cur.execute("select * from users")
- 7L
- >>> cur.fetchall()
- ({'username': u'qiwsir', 'password': u'123123', 'id': 1L, 'email': u'qiwsir@gmail.com'}, {'username': u'mypython', 'password': u'123456', 'id': 2L, 'email': u'python@gmail.com'}, {'username': u'google', 'password': u'111222', 'id': 3L, 'email': u'g@gmail.com'}, {'username': u'facebook', 'password': u'222333', 'id': 4L, 'email': u'f@face.book'}, {'username': u'github', 'password': u'333444', 'id': 5L, 'email': u'git@hub.com'}, {'username': u'docker', 'password': u'444555', 'id': 6L, 'email': u'doc@ker.com'}, {'username': u'\u8001\u9f50', 'password': u'9988', 'id': 7L, 'email': u'qiwsir@gmail.com'})
这样,在元组里面的元素就是一个一个字典:
- >>> cur.scroll(0,"absolute")
- >>> for line in cur.fetchall():
- ... print line["username"]
- ...
- qiwsir
- mypython
- github
- docker
根据字典对象的特点来读取“键-值”。
7.3.8 更新数据
熟悉了前面的操作,再到这里一切都显得那么简单。但仍要提醒的是,如果更新完毕,和插入数据一样,都需要commit()来提交保存。
- >>> cur.execute("update users set username=%s where id=2",("mypython"))
- 1L
- >>> cur.execute("select * from users where id=2")
- 1L
- >>> cur.fetchone()
- (2L, u'mypython', u'123456', u'python@gmail.com')
从操作中可以看出,已经将数据库中第二条的用户名修改为mypython了,用的就是update语句。
不过,要真的实现在数据库中的更新,还要运行:
- >>> conn.commit()
还有个小尾巴,即当你操作数据完毕,不要忘记关门:
- >>> cur.close()
- >>> conn.close()
门锁好了,放心离开。