>"So do not worry about tomorrow, for tomorrow will bring worries of its own. Today's trouble is enought for today." (MATTHEW 7:34) #MySQL数据库(2) 就数据库而言，连接之后就要对其操作。但是，目前名字叫作qiwsirtest的数据仅仅是空架子，没有什么可操作的，要操作它，就必须在里面建立“表”，什么是数据库的表呢？下面摘抄维基百科对数据库表的简要解释。 >在关系数据库中，数据库表是一系列二维数组的集合，用来代表和储存数据对象之间的关系。它由纵向的列和横向的行组成，例如一个有关作者信息的名为 authors 的表中，每个列包含的是所有作者的某个特定类型的信息，比如“姓氏”，而每行则包含了某个特定作者的所有信息：姓、名、住址等等。 >对于特定的数据库表，列的数目一般事先固定，各列之间可以由列名来识别。而行的数目可以随时、动态变化，每行通常都可以根据某个（或某几个）列中的数据来识别，称为候选键。 在qiwsirtest中建立一个存储用户名、用户密码、用户邮箱的表，其结构用二维表格表现如下： |username|password|email| |--------|--------|-----| |qiwsir|123123|[email protected]| 特别说明，这里为了简化细节，突出重点，对密码不加密，直接明文保存，虽然这种方式是很不安全的。据小道消息，有的网站居然用明文保存密码，这么做是比较可恶的。就让我在这里，仅仅在这里可恶一次。 ##数据库表 因为直接操作数据库不是本书重点，但是关联到后面的操作，为了让读者在阅读上连贯，快速地说明建立数据库表并输入内容。 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，其中包含上述字段，可以用下面的方式看一看这个数据表的结构。 mysql> show tables; +----------------------+ | Tables_in_qiwsirtest | +----------------------+ | users | +----------------------+ 1 row in set (0.00 sec) 查询显示，在qiwsirtest这个数据库中，已经有一个表，它的名字是：users。 mysql> desc users; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(2) | NO | PRI | NULL | auto_increment | | username | varchar(40) | YES | | NULL | | | password | text | YES | | NULL | | | email | text | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) 显示表users的结构： 特别提醒：上述所有字段设置仅为演示，在实际开发中，要根据具体情况来确定字段的属性。 如此就得到了一个空表。可以查询看看： mysql> select * from users; Empty set (0.01 sec) 向里面插入一条信息。 mysql> insert into users(username,password,email) values("qiwsir","123123","[email protected]"); Query OK, 1 row affected (0.05 sec) mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | [email protected] | +----+----------+----------+------------------+ 1 row in set (0.00 sec) 这样就得到了一个有内容的数据库表。 ##操作数据库 首先要连接数据库。 >>> 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() 此后，就可以利用游标对象的方法对数据库进行操作。那么还得了解游标对象的常用方法： |名称|描述| |----|----| |close()|关闭游标。之后游标不可用| |execute(query[,args])|执行一条SQL语句，可以带参数| |executemany(query, pseq)|对序列pseq中的每个参数执行sql语句| |fetchone()|返回一条查询结果| |fetchall()|返回所有查询结果| |fetchmany([size])|返回size条结果| |nextset()|移动到下一个结果| |scroll(value,mode='relative')|移动游标到指定行，如果mode='relative',则表示从当前所在行移动value条,如果mode='absolute',则表示从结果集的第一行移动value条.| ###插入 例如，要在数据表users中插入一条记录，使得username="python",password="123456",email="[email protected]"，这样做： >>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)",("python","123456","[email protected]")) 1L 没有报错，并且返回一个"1L"结果，说明有一行记录操作成功。不妨进入到"mysql>"交互方式查看。 mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | [email protected] | +----+----------+----------+------------------+ 1 row in set (0.00 sec) 咦，奇怪呀。怎么没有看到增加的那一条呢？哪里错了？可是上面也没有报错呀。 特别注意，通过`cur.execute()`对数据库进行操作之后，没有报错，完全正确，但是不等于数据就已经提交到数据库中了，还必须要用到"MySQLdb.connect"的一个方法：commit()，将数据提交上去，也就是进行了`cur.execute()`操作，要将数据提交才能有效改变数据库的内容。 >>> conn.commit() 再到"mysql>"中运行`"select * from users"`试一试： mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | [email protected] | | 2 | python | 123456 | [email protected] | +----+----------+----------+------------------+ 2 rows in set (0.00 sec) 果然如此。 这就如同编写一个文本一样，将文字写到文本上，并不等于文字已经保留在文本文件中了，必须执行`CTRL-S`才能保存。所有以`execute()`执行各种sql语句之后，要让已经执行的效果保存，必须运行连接对象的`commit()`方法。 再尝试一下插入多条的那个命令`executemany(query,args)`。 >>> cur.executemany("insert into users (username,password,email) values (%s,%s,%s)",(("google","111222","[email protected]"),("facebook","222333","[email protected]"),("github","333444","[email protected]"),("docker","444555","[email protected]"))) 4L >>> conn.commit() 到"mysql>"里面看结果： mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | [email protected] | | 2 | python | 123456 | [email protected] | | 3 | google | 111222 | [email protected] | | 4 | facebook | 222333 | [email protected] | | 5 | github | 333444 | [email protected] | | 6 | docker | 444555 | [email protected] | +----+----------+----------+------------------+ 6 rows in set (0.00 sec) 成功插入了多条记录。在"executemany(query, pseq)"中，query还是一条sql语句，但是`pseq`这时候是一个元组，特别注意括号——一环套一环的括号，这个元组里面的元素也是元组，每个元组分别对应sql语句中的字段列表。 除了插入命令，其它对数据操作的命令都可用类似上面的方式，比如删除、修改等。 ###查询 如果要从数据库中查询数据，也用游标方法来操作了。 >>> 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 #Python 3: print(line) ... (1L, u'qiwsir', u'123123', u'[email protected]') (2L, u'python', u'123456', u'[email protected]') (3L, u'google', u'111222', u'[email protected]') (4L, u'facebook', u'222333', u'[email protected]') (5L, u'github', u'333444', u'[email protected]') (6L, u'docker', u'444555', u'[email protected]') (7L, u'\u8001\u9f50', u'9988', u'[email protected]') 很好，果然逐条显示出来了。请读者注意，第七条中的`u'\u8001\u95f5'`是汉字，只不过由于我的shell不能显示罢了，不必惊慌，不必搭理它。 只想查出第一条，可以吗？当然可以，再看下面： >>> cur.execute("select * from users where id=1") 1L >>> line_first = cur.fetchone() #只返回一条 >>> print line_first #Python 3: print(line_first) (1L, u'qiwsir', u'123123', u'[email protected]') 为了对上述过程了解深入，做下面实验： >>> cur.execute("select * from users") 7L >>> print cur.fetchall() #Python 3: print(cur.fetchall()) ((1L, u'qiwsir', u'123123', u'[email protected]'), (2L, u'python', u'123456', u'[email protected]'), (3L, u'google', u'111222', u'[email protected]'), (4L, u'facebook', u'222333', u'[email protected]'), (5L, u'github', u'333444', u'[email protected]'), (6L, u'docker', u'444555', u'[email protected]'), (7L, u'\u8001\u9f50', u'9988', u'[email protected]')) 原来，用`cur.execute()`从数据库查询出来的东西，被“保存在了cur所能找到的某个地方”，要找出这些被保存的东西，需要用`cur.fetchall()`（或者`fechone`等），并且找出来之后，作为对象存在。从上面的实验探讨发现，返回值是一个元组对象，里面的每个元素，都是一个一个的元组。因此，用`for`循环就可以一个一个拿出来了。 继续，可以看到神奇。接着上面的操作，再打印一遍。 >>> print cur.fetchall() #Python 3: print(cur.fetchall()) () 晕了！怎么什么是空？不是说作为对象已经存在了内存中了吗？难道这个内存中的对象是一次有效吗？ 不要着急，这就是神奇所在。 通过游标找出来的对象，在读取的时候有一个特点，就是那个游标会移动。在第一次操作了`print cur.fetchall()`后，因为是将所有的都打印出来，游标就从第一条移动到最后一条。当`print`结束之后，游标已经在最后一条的后面了。接下来如果再次打印，就空了，最后一条后面没有东西了。这如同什么？能不能与本书前面已经有的知识关联起来？ 下面还要实验，检验上面所说： >>> cur.execute('select * from users') 7L >>> print cur.fetchone() #Python 3: print(cur.fetchone() ) (1L, u'qiwsir', u'123123', u'[email protected]') >>> print cur.fetchone() #Python 3: print(cur.fetchone() ) (2L, u'python', u'123456', u'[email protected]') >>> print cur.fetchone() #Python 3: print(cur.fetchone() ) (3L, u'google', u'111222', u'[email protected]') 这次不再一次性全部打印出来了，而是一次打印一条，从结果中看出来，果然那个游标在一条一条向下移动呢。注意，这次实验中重新运行了查询语句。 那么，既然操作存储在内存中的对象时游标会移动，能不能让游标向上移动，或者移动到指定位置呢？这就是那个`scroll()`。 >>> cur.scroll(1) >>> print cur.fetchone() (5L, u'github', u'333444', u'[email protected]') >>> cur.scroll(-2) >>> print cur.fetchone() (4L, u'facebook', u'222333', u'[email protected]') 果然，能够移动游标，不过请仔细观察，上面的方式是让游标相对与当前位置向上或者向下移动。即`cur.scroll(n)`或者`cur.scroll(n,"relative")`，意思是相对当前位置向上或者向下移动，n为正数，表示向下（向前），n为负数，表示向上（向后）。 还有一种方式可以实现“绝对”移动，不是“相对”移动——增加一个参数"absolute"。 但在Python中，序列对象是的顺序是从0开始的。 >>> cur.scroll(2, "absolute") #回到序号是2,但指向第三条 >>> print cur.fetchone() #Python 3: print(cur.fetchone() ) (3L, u'google', u'111222', u'[email protected]') >>> cur.scroll(1, "absolute") >>> print cur.fetchone() #Python 3: print(cur.fetchone() ) (2L, u'python', u'123456', u'[email protected]') >>> cur.scroll(0, "absolute") #回到序号是0,即指向tuple的第一条 >>> print cur.fetchone() #Python 3: print(cur.fetchone() ) (1L, u'qiwsir', u'123123', u'[email protected]') 至此，已经熟悉了`cur.fetchall()`å’Œ`cur.fetchone()`以及`cur.scroll()`几个方法，还有另外一个——`cur.fetchmany()`，在前面操作的基础上继续。 >>> cur.fetchmany(3) ((2L, u'python', u'123456', u'[email protected]'), (3L, u'google', u'111222', u'[email protected]'), (4L, u'facebook', u'222333', u'[email protected]')) 上面这个操作，就是实现了从当前位置（游标指向序号为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'[email protected]'}, {'username': u'mypython', 'password': u'123456', 'id': 2L, 'email': u'[email protected]'}, {'username': u'google', 'password': u'111222', 'id': 3L, 'email': u'[email protected]'}, {'username': u'facebook', 'password': u'222333', 'id': 4L, 'email': u'[email protected]'}, {'username': u'github', 'password': u'333444', 'id': 5L, 'email': u'[email protected]'}, {'username': u'docker', 'password': u'444555', 'id': 6L, 'email': u'[email protected]'}, {'username': u'\u8001\u9f50', 'password': u'9988', 'id': 7L, 'email': u'[email protected]'}) 这样，在元组里面的元素就是一个一个字典： >>> cur.scroll(0,"absolute") >>> for line in cur.fetchall(): ... print line["username"] #Python 3: print(line["username"]) ... qiwsir mypython google ... 根据字典对象的特点来读取了“键-值”。 ###更新数据 熟悉了前面的操作，再到这里，一切都显得简单了，但仍要提醒的是，如果更新完毕，和插入数据一样，都需要`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'[email protected]') 从操作中看出来了，已经将数据库中第二条的用户名修改为mypython了，用的就是update语句。 不过，要真的实现在数据库中的更新，还要运行： >>> conn.commit() 还有个小尾巴，当你操作数据完毕，不要忘记关门： >>> cur.close() >>> conn.close() 门锁好了，放心离开。 ------ [总目录](./index.md)   |   [上节：mysql数据库(1)](./230.md)   |   [下节：mongodb数据库](./232.md) 如果你认为有必要打赏我，请通过支付宝：**[email protected]**,不胜感激。