MySQL數據庫(2) · 零基礎學python（第二版）

就數據庫而言，連接之后就要對其操作。但是，目前那個名字叫做qiwsirtest的數據僅僅是空架子，沒有什么可操作的，要操作它，就必須在里面建立“表”，什么是數據庫的表呢？下面摘抄自維基百科對數據庫表的簡要解釋，要想詳細了解，需要看官在找一些有關數據庫的教程和書籍來看看。 > 在關系數據庫中，數據庫表是一系列二維數組的集合，用來代表和儲存數據對象之間的關系。它由縱向的列和橫向的行組成，例如一個有關作者信息的名為 authors 的表中，每個列包含的是所有作者的某個特定類型的信息，比如“姓氏”，而每行則包含了某個特定作者的所有信息：姓、名、住址等等。 > > 對于特定的數據庫表，列的數目一般事先固定，各列之間可以由列名來識別。而行的數目可以隨時、動態變化，每行通常都可以根據某個（或某幾個）列中的數據來識別，稱為候選鍵。我打算在qiwsirtest中建立一個存儲用戶名、用戶密碼、用戶郵箱的表，其結構用二維表格表現如下： | username | password | email | | --- | --- | --- | | qiwsir | 123123 | [qiwsir@gmail.com](mailto:qiwsir@gmail.com) | 特別說明，這里為了簡化細節，突出重點，對密碼不加密，直接明文保存，雖然這種方式是很不安全的。但是，有不少網站還都這么做的，這么做的目的是比較可惡的。就讓我在這里，僅僅在這里可惡一次。 ## [](https://github.com/qiwsir/StarterLearningPython/blob/master/231.md#數據庫表)數據庫表因為直接操作數據部分，不是本教重點，但是關聯到后面的操作，為了讓讀者在閱讀上連貫，也快速地說明建立數據庫表并輸入內容。 ~~~ 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","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) ~~~ 這樣就得到了一個有內容的數據庫表。 ## [](https://github.com/qiwsir/StarterLearningPython/blob/master/231.md#python操作數據庫)python操作數據庫連接數據庫，必須的。 ~~~ >>> 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條. | ### [](https://github.com/qiwsir/StarterLearningPython/blob/master/231.md#插入)插入例如，要在數據表users中插入一條記錄，使得:username="python",password="123456",email="[python@gmail.com](mailto:python@gmail.com)"，這樣做： ~~~ >>> cur.execute("insert into users (username,password,email) values (%s,%s,%s)",("python","123456","python@gmail.com")) 1L ~~~ 沒有報錯，并且返回一個"1L"結果，說明有一行記錄操作成功。不妨用"mysql>"交互方式查看一下： ~~~ mysql> select * from users; +----+----------+----------+------------------+ | id | username | password | email | +----+----------+----------+------------------+ | 1 | qiwsir | 123123 | qiwsir@gmail.com | +----+----------+----------+------------------+ 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 | qiwsir@gmail.com | | 2 | python | 123456 | python@gmail.com | +----+----------+----------+------------------+ 2 rows in set (0.00 sec) ~~~ 果然如此。這就如同編寫一個文本一樣，將文字寫到文本上，并不等于文字已經保留在文本文件中了，必須執行"CTRL-S"才能保存。也就是在通過python操作數據庫的時候，以"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; +----+----------+----------+------------------+ | 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語句，但是pseq這時候是一個tuple，這個tuple里面的元素也是tuple，每個tuple分別對應sql語句中的字段列表。這句話其實被執行多次。只不過執行過程不顯示給我們看罷了。除了插入命令，其它對數據操作的命了都可用類似上面的方式，比如刪除、修改等。 ### [](https://github.com/qiwsir/StarterLearningPython/blob/master/231.md#查詢)查詢如果要從數據庫中查詢數據，也用游標方法來操作了。 ~~~ >>> 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等），并且找出來之后，做為對象存在。從上面的實驗探討發現，被保存的對象是一個tuple中，里面的每個元素，都是一個一個的tuple。因此，用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,即指向tuple的第一條 >>> print cur.fetchone() (1L, u'qiwsir', u'123123', u'qiwsir@gmail.com') ~~~ 至此，已經熟悉了cur.fetchall()和cur.fetchone()以及cur.scroll()幾個方法，還有另外一個，接這上邊的操作，也就是游標在序號是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')) ~~~ 上面這個操作，就是實現了從當前位置（游標指向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 google facebook github docker 老齊 ~~~ 根據字典對象的特點來讀取了“鍵-值”。 ### [](https://github.com/qiwsir/StarterLearningPython/blob/master/231.md#更新數據)更新數據經過前面的操作，這個就比較簡單了，不過需要提醒的是，如果更新完畢，和插入數據一樣，都需要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() ~~~ 門鎖好了，放心離開。