MySQL數(shù)據(jù)庫(kù)(2)

就數(shù)據(jù)庫(kù)而言，連接之后就要對(duì)其操作。但是，目前那個(gè)名字叫做qiwsirtest的數(shù)據(jù)僅僅是空架子，沒(méi)有什么可操作的，要操作它，就必須在里面建立“表”，什么是數(shù)據(jù)庫(kù)的表呢？下面摘抄自維基百科對(duì)數(shù)據(jù)庫(kù)表的簡(jiǎn)要解釋，要想詳細(xì)了解，需要看官在找一些有關(guān)數(shù)據(jù)庫(kù)的教程和書(shū)籍來(lái)看看。

在關(guān)系數(shù)據(jù)庫(kù)中，數(shù)據(jù)庫(kù)表是一系列二維數(shù)組的集合，用來(lái)代表和儲(chǔ)存數(shù)據(jù)對(duì)象之間的關(guān)系。它由縱向的列和橫向的行組成，例如一個(gè)有關(guān)作者信息的名為 authors 的表中，每個(gè)列包含的是所有作者的某個(gè)特定類型的信息，比如“姓氏”，而每行則包含了某個(gè)特定作者的所有信息：姓、名、住址等等。

對(duì)于特定的數(shù)據(jù)庫(kù)表，列的數(shù)目一般事先固定，各列之間可以由列名來(lái)識(shí)別。而行的數(shù)目可以隨時(shí)、動(dòng)態(tài)變化，每行通常都可以根據(jù)某個(gè)（或某幾個(gè)）列中的數(shù)據(jù)來(lái)識(shí)別，稱為候選鍵。

我打算在qiwsirtest中建立一個(gè)存儲(chǔ)用戶名、用戶密碼、用戶郵箱的表，其結(jié)構(gòu)用二維表格表現(xiàn)如下：

特別說(shuō)明，這里為了簡(jiǎn)化細(xì)節(jié)，突出重點(diǎn)，對(duì)密碼不加密，直接明文保存，雖然這種方式是很不安全的。但是，有不少網(wǎng)站還都這么做的，這么做的目的是比較可惡的。就讓我在這里，僅僅在這里可惡一次。

數(shù)據(jù)庫(kù)表

因?yàn)橹苯硬僮鲾?shù)據(jù)部分，不是本教重點(diǎn)，但是關(guān)聯(lián)到后面的操作，為了讓讀者在閱讀上連貫，也快速地說(shuō)明建立數(shù)據(jù)庫(kù)表并輸入內(nèi)容。

mysql> use qiwsirtest;
Database changed
mysql> show tables;
Empty set (0.00 sec)

用show tables命令顯示這個(gè)數(shù)據(jù)庫(kù)中是否有數(shù)據(jù)表了。查詢結(jié)果顯示為空。

下面就用如下命令建立一個(gè)數(shù)據(jù)表，這個(gè)數(shù)據(jù)表的內(nèi)容就是上面所說(shuō)明的。

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)

建立的這個(gè)數(shù)據(jù)表名稱是：users，其中包含上述字段，可以用下面的方式看一看這個(gè)數(shù)據(jù)表的結(jié)構(gòu)。

mysql> show tables;
+----------------------+
| Tables_in_qiwsirtest |
+----------------------+
| users                |
+----------------------+
1 row in set (0.00 sec)

查詢顯示，在qiwsirtest這個(gè)數(shù)據(jù)庫(kù)中，已經(jīng)有一個(gè)表，它的名字是：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的結(jié)構(gòu)：

特別提醒：上述所有字段設(shè)置僅為演示，在實(shí)際開(kāi)發(fā)中，要根據(jù)具體情況來(lái)確定字段的屬性。

如此就得到了一個(gè)空表?？梢圆樵兛纯矗?/p>

mysql> select * from users;
Empty set (0.01 sec)

向里面插入點(diǎn)信息，就只插入一條吧。

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;
+----+----------+----------+------------------+
| id | username | password | email            |
+----+----------+----------+------------------+
|  1 | qiwsir   | 123123   | qiwsir@gmail.com |
+----+----------+----------+------------------+
1 row in set (0.00 sec)

這樣就得到了一個(gè)有內(nèi)容的數(shù)據(jù)庫(kù)表。

python操作數(shù)據(jù)庫(kù)

連接數(shù)據(jù)庫(kù)，必須的。

>>> import MySQLdb
>>> conn = MySQLdb.connect(host="localhost",user="root",passwd="123123",db="qiwsirtest",charset="utf8")

Python建立了與數(shù)據(jù)的連接，其實(shí)是建立了一個(gè)MySQLdb.connect()的實(shí)例對(duì)象，或者泛泛地稱之為連接對(duì)象，python就是通過(guò)連接對(duì)象和數(shù)據(jù)庫(kù)對(duì)話。這個(gè)對(duì)象常用的方法有：

• commit()：如果數(shù)據(jù)庫(kù)表進(jìn)行了修改，提交保存當(dāng)前的數(shù)據(jù)。當(dāng)然，如果此用戶沒(méi)有權(quán)限就作罷了，什么也不會(huì)發(fā)生。
• rollback()：如果有權(quán)限，就取消當(dāng)前的操作，否則報(bào)錯(cuò)。
• cursor([cursorclass])：返回連接的游標(biāo)對(duì)象。通過(guò)游標(biāo)執(zhí)行SQL查詢并檢查結(jié)果。游標(biāo)比連接支持更多的方法，而且可能在程序中更好用。
• close()：關(guān)閉連接。此后，連接對(duì)象和游標(biāo)都不再可用了。

Python和數(shù)據(jù)之間的連接建立起來(lái)之后，要操作數(shù)據(jù)庫(kù)，就需要讓python對(duì)數(shù)據(jù)庫(kù)執(zhí)行SQL語(yǔ)句。Python是通過(guò)游標(biāo)執(zhí)行SQL語(yǔ)句的。所以，連接建立之后，就要利用連接對(duì)象得到游標(biāo)對(duì)象，方法如下：

>>> cur = conn.cursor()

此后，就可以利用游標(biāo)對(duì)象的方法對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作。那么還得了解游標(biāo)對(duì)象的常用方法：

插入

例如，要在數(shù)據(jù)表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

沒(méi)有報(bào)錯(cuò)，并且返回一個(gè)"1L"結(jié)果，說(shuō)明有一行記錄操作成功。不妨用"mysql>"交互方式查看一下：

mysql> select * from users;
+----+----------+----------+------------------+
| id | username | password | email            |
+----+----------+----------+------------------+
|  1 | qiwsir   | 123123   | qiwsir@gmail.com |
+----+----------+----------+------------------+
1 row in set (0.00 sec)

咦，奇怪呀。怎么沒(méi)有看到增加的那一條呢？哪里錯(cuò)了？可是上面也沒(méi)有報(bào)錯(cuò)呀。

特別注意，通過(guò)"cur.execute()"對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作之后，沒(méi)有報(bào)錯(cuò)，完全正確，但是不等于數(shù)據(jù)就已經(jīng)提交到數(shù)據(jù)庫(kù)中了，還必須要用到"MySQLdb.connect"的一個(gè)屬性：commit()，將數(shù)據(jù)提交上去，也就是進(jìn)行了"cur.execute()"操作，要將數(shù)據(jù)提交，必須執(zhí)行：

>>> conn.commit()

再到"mysql>"中運(yùn)行"select * from users"試一試：

mysql> select * from users;
+----+----------+----------+------------------+
| id | username | password | email            |
+----+----------+----------+------------------+
|  1 | qiwsir   | 123123   | qiwsir@gmail.com |
|  2 | python   | 123456   | python@gmail.com |
+----+----------+----------+------------------+
2 rows in set (0.00 sec)

果然如此。這就如同編寫(xiě)一個(gè)文本一樣，將文字寫(xiě)到文本上，并不等于文字已經(jīng)保留在文本文件中了，必須執(zhí)行"CTRL-S"才能保存。也就是在通過(guò)python操作數(shù)據(jù)庫(kù)的時(shí)候，以"execute()"執(zhí)行各種sql語(yǔ)句之后，要讓已經(jīng)執(zhí)行的效果保存，必須運(yùn)行連接對(duì)象的"commit()"方法。

再嘗試一下插入多條的那個(gè)命令"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>"里面看結(jié)果：

mysql> select * from users;
+----+----------+----------+------------------+
| id | username | password | email            |
+----+----------+----------+------------------+
|  1 | qiwsir   | 123123   | qiwsir@gmail.com |
|  2 | python   | 123456   | python@gmail.com |
|  3 | google   | 111222   | g@gmail.com      |
|  4 | facebook | 222333   | f@face.book      |
|  5 | github   | 333444   | git@hub.com      |
|  6 | docker   | 444555   | doc@ker.com      |
+----+----------+----------+------------------+
6 rows in set (0.00 sec)

成功插入了多條記錄。在"executemany(query, pseq)"中，query還是一條sql語(yǔ)句，但是pseq這時(shí)候是一個(gè)tuple，這個(gè)tuple里面的元素也是tuple，每個(gè)tuple分別對(duì)應(yīng)sql語(yǔ)句中的字段列表。這句話其實(shí)被執(zhí)行多次。只不過(guò)執(zhí)行過(guò)程不顯示給我們看罷了。

除了插入命令，其它對(duì)數(shù)據(jù)操作的命了都可用類似上面的方式，比如刪除、修改等。

查詢

如果要從數(shù)據(jù)庫(kù)中查詢數(shù)據(jù)，也用游標(biāo)方法來(lái)操作了。

>>> cur.execute("select * from users")    
7L

這說(shuō)明從users表匯總查詢出來(lái)了7條記錄。但是，這似乎有點(diǎn)不友好，告訴我7條記錄查出來(lái)了，但是在哪里呢，如果在'mysql>'下操作查詢命令，一下就把7條記錄列出來(lái)了。怎么顯示python在這里的查詢結(jié)果呢？

要用到游標(biāo)對(duì)象的fetchall()、fetchmany(size=None)、fetchone()、scroll(value, mode='relative')等方法。

>>> cur.execute("select * from users")    
7L
>>> lines = cur.fetchall()

到這里，已經(jīng)將查詢到的記錄賦值給變量lines了。如果要把它們顯示出來(lái)，就要用到曾經(jīng)學(xué)習(xí)過(guò)的循環(huán)語(yǔ)句了。

>>> 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')

很好。果然是逐條顯示出來(lái)了。列位注意，第七條中的u'\u8001\u95f5',這里是漢字，只不過(guò)由于我的shell不能顯示罷了，不必驚慌，不必搭理它。

只想查出第一條，可以嗎？當(dāng)然可以！看下面的：

>>> 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')

為了對(duì)上述過(guò)程了解深入，做下面實(shí)驗(yàn)：

>>> 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'))

原來(lái)，用cur.execute()從數(shù)據(jù)庫(kù)查詢出來(lái)的東西，被“保存在了cur所能找到的某個(gè)地方”，要找出這些被保存的東西，需要用cur.fetchall()（或者fechone等），并且找出來(lái)之后，做為對(duì)象存在。從上面的實(shí)驗(yàn)探討發(fā)現(xiàn)，被保存的對(duì)象是一個(gè)tuple中，里面的每個(gè)元素，都是一個(gè)一個(gè)的tuple。因此，用for循環(huán)就可以一個(gè)一個(gè)拿出來(lái)了。

接著看，還有神奇的呢。

接著上面的操作，再打印一遍

>>> print cur.fetchall()
()

暈了！怎么什么是空？不是說(shuō)做為對(duì)象已經(jīng)存在了內(nèi)存中了嗎？難道這個(gè)內(nèi)存中的對(duì)象是一次有效嗎？

不要著急。

通過(guò)游標(biāo)找出來(lái)的對(duì)象，在讀取的時(shí)候有一個(gè)特點(diǎn)，就是那個(gè)游標(biāo)會(huì)移動(dòng)。在第一次操作了print cur.fetchall()后，因?yàn)槭菍⑺械亩即蛴〕鰜?lái)，游標(biāo)就從第一條移動(dòng)到最后一條。當(dāng)print結(jié)束之后，游標(biāo)已經(jīng)在最后一條的后面了。接下來(lái)如果再次打印，就空了，最后一條后面沒(méi)有東西了。

下面還要實(shí)驗(yàn)，檢驗(yàn)上面所說(shuō)：

>>> 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')

這次我不一次全部打印出來(lái)了，而是一次打印一條，看官可以從結(jié)果中看出來(lái)，果然那個(gè)游標(biāo)在一條一條向下移動(dòng)呢。注意，我在這次實(shí)驗(yàn)中，是重新運(yùn)行了查詢語(yǔ)句。

那么，既然在操作存儲(chǔ)在內(nèi)存中的對(duì)象時(shí)候，游標(biāo)會(huì)移動(dòng)，能不能讓游標(biāo)向上移動(dòng)，或者移動(dòng)到指定位置呢？這就是那個(gè)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')

果然，這個(gè)函數(shù)能夠移動(dòng)游標(biāo)，不過(guò)請(qǐng)仔細(xì)觀察，上面的方式是讓游標(biāo)相對(duì)與當(dāng)前位置向上或者向下移動(dòng)。即：

cur.scroll(n)，或者，cur.scroll(n,"relative")：意思是相對(duì)當(dāng)前位置向上或者向下移動(dòng)，n為正數(shù)，表示向下（向前），n為負(fù)數(shù)，表示向上（向后）

還有一種方式，可以實(shí)現(xiàn)“絕對(duì)”移動(dòng)，不是“相對(duì)”移動(dòng)：增加一個(gè)參數(shù)"absolute"

特別提醒看官注意的是，在python中，序列對(duì)象是的順序是從0開(kāi)始的。

>>> cur.scroll(2,"absolute")    #回到序號(hào)是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")    #回到序號(hào)是0,即指向tuple的第一條
>>> print cur.fetchone()
(1L, u'qiwsir', u'123123', u'qiwsir@gmail.com')

至此，已經(jīng)熟悉了cur.fetchall()和cur.fetchone()以及cur.scroll()幾個(gè)方法，還有另外一個(gè)，接這上邊的操作，也就是游標(biāo)在序號(hào)是1的位置，指向了tuple的第二條

>>> 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'))

上面這個(gè)操作，就是實(shí)現(xiàn)了從當(dāng)前位置（游標(biāo)指向tuple的序號(hào)為1的位置，即第二條記錄）開(kāi)始，含當(dāng)前位置，向下列出3條記錄。

讀取數(shù)據(jù)，好像有點(diǎn)啰嗦呀。細(xì)細(xì)琢磨，還是有道理的。你覺(jué)得呢？

不過(guò)，python總是能夠?yàn)槲覀冎氲?，在連接對(duì)象的游標(biāo)方法中提供了一個(gè)參數(shù)，可以實(shí)現(xiàn)將讀取到的數(shù)據(jù)變成字典形式，這樣就提供了另外一種讀取方式了。

>>> 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'})     

這樣，在元組里面的元素就是一個(gè)一個(gè)字典：

>>> cur.scroll(0,"absolute")
>>> for line in cur.fetchall():
...     print line["username"]
... 
qiwsir
mypython
google
facebook
github
docker
老齊

根據(jù)字典對(duì)象的特點(diǎn)來(lái)讀取了“鍵-值”。

更新數(shù)據(jù)

經(jīng)過(guò)前面的操作，這個(gè)就比較簡(jiǎn)單了，不過(guò)需要提醒的是，如果更新完畢，和插入數(shù)據(jù)一樣，都需要commit()來(lái)提交保存。

>>> 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')

從操作中看出來(lái)了，已經(jīng)將數(shù)據(jù)庫(kù)中第二條的用戶名修改為mypython了，用的就是update語(yǔ)句。

不過(guò)，要真的實(shí)現(xiàn)在數(shù)據(jù)庫(kù)中更新，還要運(yùn)行：

>>> conn.commit()

這就大事完吉了。

應(yīng)該還有個(gè)小尾巴，那就是當(dāng)你操作數(shù)據(jù)完畢，不要忘記關(guān)門(mén)：

>>> cur.close()
>>> conn.close()

門(mén)鎖好了，放心離開(kāi)。