"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()

门锁好了,放心离开。


总目录   |   上节:mysql数据库(1)   |   下节:mongodb数据库

如果你认为有必要打赏我,请通过支付宝:[email protected],不胜感激。

results matching ""

    No results matching ""