在Python程序中操作MySQL的基本方法

最近在學(xué)習(xí)python，這種腳本語(yǔ)言毫無(wú)疑問(wèn)的會(huì)跟數(shù)據(jù)庫(kù)產(chǎn)生關(guān)聯(lián)，因此這里介紹一下如何使用python操作mysql數(shù)據(jù)庫(kù)。我python也是零基礎(chǔ)學(xué)起，所以本篇博客針對(duì)的是python初學(xué)者，大牛可以選擇繞道。
另外，本篇基于的環(huán)境是Ubuntu13.10，使用的python版本是2.7.5。
MYSQL數(shù)據(jù)庫(kù)
MYSQL是一個(gè)全球領(lǐng)先的開(kāi)源數(shù)據(jù)庫(kù)管理系統(tǒng)。它是一個(gè)支持多用戶、多線程的數(shù)據(jù)庫(kù)管理系統(tǒng)，與Apache、PHP、Linux共同組成LAMP平臺(tái)，在web應(yīng)用中廣泛使用，例如Wikipedia和YouTube。MYSQL包含兩個(gè)版本：服務(wù)器系統(tǒng)和嵌入式系統(tǒng)。
環(huán)境配置

在我們開(kāi)始語(yǔ)法學(xué)習(xí)之前，還需要按裝mysql和python對(duì)mysql操作的模塊。

安裝mysql:    
sudo apt-get install mysql-server

安裝過(guò)程中會(huì)提示你輸入root帳號(hào)的密碼，符合密碼規(guī)范即可。

接下來(lái)，需要安裝python對(duì)mysql的操作模塊：    
sudo apt-get install python-mysqldb

這里需要注意：安裝完python-mysqldb之后，我們默認(rèn)安裝了兩個(gè)python操作模塊，分別是支持C語(yǔ)言API的_mysql和支持Python API的MYSQLdb。稍后會(huì)重點(diǎn)講解MYSQLdb模塊的使用。

接下來(lái)，我們進(jìn)入MYSQL，創(chuàng)建一個(gè)測(cè)試數(shù)據(jù)庫(kù)叫testdb。創(chuàng)建命令為：    
create database testdb;
然后，我們創(chuàng)建一個(gè)測(cè)試賬戶來(lái)操作這個(gè)testdb數(shù)據(jù)庫(kù)，創(chuàng)建和授權(quán)命令如下：    
create user 'testuser'@'127.0.0.1' identified by 'test123';
grant all privileges on testdb.* to 'testuser'@'127.0.0.1';
 
_mysql module

_mysql模塊直接封裝了MYSQL的C語(yǔ)言API函數(shù)，它與python標(biāo)準(zhǔn)的數(shù)據(jù)庫(kù)API接口是不兼容的。我更推薦大家使用面向?qū)ο蟮腗YSQLdb模塊才操作mysql，這里只給出一個(gè)使用_mysql模塊的例子，這個(gè)模塊不是我們學(xué)習(xí)的重點(diǎn)，我們只需要了解有這個(gè)模塊就好了。    
#!/usr/bin/python
# -*- coding: utf-8 -*-
 
import _mysql
import sys
 
try:
 con = _mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb')
 con.query("SELECT VERSION()")
 result = con.use_result()
 
 print "MYSQL version : %s " % result.fetch_row()[0]
 
except _mysql.Error, e:
 print "Error %d: %s %s" % (e.args[0], e.args[1])
 sys.exit(1)
 
finally:
 if con:
  con.close()

這個(gè)代碼主要是獲取當(dāng)前mysql的版本，大家可以模擬敲一下這部分代碼然后運(yùn)行一下。
MYSQLdb module

MYSQLdb是在_mysql模塊的基礎(chǔ)上進(jìn)一步進(jìn)行封裝，并且與python標(biāo)準(zhǔn)數(shù)據(jù)庫(kù)API接口兼容，這使得代碼更容易被移植。Python更推薦使用這個(gè)MYSQLdb模塊來(lái)進(jìn)行MYSQL操作。    
#!/usr/bin/python
# -*- coding: utf-8 -*-
 
import MySQLdb as mysql
 
try:
 conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb')
 cur = conn.cursor()
 cur.execute("SELECT VERSION()")
 
 version = cur.fetchone()
 print "Database version : %s" % version
 
except mysql.Error, e:
 print "Error %d:%s" % (e.args[0], e.args[1])
 exit(1)
 
finally:
 if conn:
  conn.close()


我們導(dǎo)入了MySQLdb模塊并把它重命名為mysql，然后調(diào)用MySQLdb模塊的提供的API方法來(lái)操作數(shù)據(jù)庫(kù)。同樣也是獲取當(dāng)前主機(jī)的安裝的mysql版本號(hào)。
創(chuàng)建新表

接下來(lái)，我們通過(guò)MySQLdb模塊創(chuàng)建一個(gè)表，并在其中填充部分?jǐn)?shù)據(jù)。實(shí)現(xiàn)代碼如下：
    
#!/usr/bin/python
 
# -*- coding: utf-8 -*-
 
import MySQLdb as mysql
 
conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');
 
with conn:
 cur = conn.cursor()
 cur.execute("DROP TABLE IF EXISTS writers");
 cur.execute("CREATE TABLE writers(id INT PRIMARY KEY AUTO_INCREMENT, name varchar(25))")
 cur.execute("insert into writers(name) values('wangzhengyi')")
 cur.execute("insert into writers(name) values('bululu')")
 cur.execute("insert into writers(name) values('chenshan')")

這里使用了with語(yǔ)句。with語(yǔ)句會(huì)執(zhí)行conn對(duì)象的enter()和__exit()方法，省去了自己寫(xiě)try/catch/finally了。

執(zhí)行完成后，我們可以通過(guò)mysql-client客戶端查看是否插入成功，查詢語(yǔ)句：    
select * from writers;

查詢結(jié)果如下：

查詢數(shù)據(jù)
剛才往表里插入了部分?jǐn)?shù)據(jù)，接下來(lái)，我們從表中取出插入的數(shù)據(jù)，代碼如下：    
#!/usr/bin/python
 
import MySQLdb as mysql
 
conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');
 
with conn:
 cursor = conn.cursor()
 cursor.execute("select * from writers")
 rows = cursor.fetchall()
 
 for row in rows:
  print row

查詢結(jié)果如下：
    
(1L, 'wangzhengyi')
(2L, 'bululu')
(3L, 'chenshan')

dictionary cursor

我們剛才不論是創(chuàng)建數(shù)據(jù)庫(kù)還是查詢數(shù)據(jù)庫(kù)，都用到了cursor。在MySQLdb模塊有許多種cursor類型，默認(rèn)的cursor是以元組的元組形式返回?cái)?shù)據(jù)的。當(dāng)我們使用dictionary cursor時(shí)，數(shù)據(jù)是以python字典形式返回的。這樣我們就可以通過(guò)列名獲取查詢數(shù)據(jù)了。

還是剛才查詢數(shù)據(jù)的代碼，改為dictionary cursor只需要修改一行代碼即可，如下所示：    
#!/usr/bin/python
 
import MySQLdb as mysql
 
conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');
 
with conn:
 cursor = conn.cursor(mysql.cursors.DictCursor)
 cursor.execute("select * from writers")
 rows = cursor.fetchall()
 
 for row in rows:
  print "id is %s, name is %s" % (row['id'], row['name'])

使用dictionary cursor，查詢結(jié)果如下：    
id is 1, name is wangzhengyi
id is 2, name is bululu
id is 3, name is chenshan
預(yù)編譯
之前寫(xiě)過(guò)php的同學(xué)應(yīng)該對(duì)預(yù)編譯很了解，預(yù)編譯可以幫助我們防止sql注入等web攻擊還能幫助提高性能。當(dāng)然，python肯定也是支持預(yù)編譯的。預(yù)編譯的實(shí)現(xiàn)也比較簡(jiǎn)單，就是用%等占位符來(lái)替換真正的變量。例如查詢id為3的用戶的信息，使用預(yù)編譯的代碼如下：    
#!/usr/bin/python
 
import MySQLdb as mysql
 
conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');
 
with conn:
 cursor = conn.cursor(mysql.cursors.DictCursor)
 cursor.execute("select * from writers where id = %s", "3")
 rows = cursor.fetchone()
 print "id is %d, name is %s" % (rows['id'], rows['name'])

我這里使用了一個(gè)%s的占位符來(lái)替換“3”，代表需要傳入的是一個(gè)字符串類型。如果傳入的不是string類型，則會(huì)運(yùn)行報(bào)錯(cuò)。
事務(wù)
事務(wù)是指在一個(gè)或者多個(gè)數(shù)據(jù)庫(kù)中對(duì)數(shù)據(jù)的原子操作。在一個(gè)事務(wù)中，所有的SQL語(yǔ)句的影響要不就全部提交到數(shù)據(jù)庫(kù)，要不就全部都回滾。
對(duì)于支持事務(wù)機(jī)制的數(shù)據(jù)庫(kù)，python接口在創(chuàng)建cursor的時(shí)候就開(kāi)始了一個(gè)事務(wù)。可以通過(guò)cursor對(duì)象的commit()方法來(lái)提交所有的改動(dòng)，也可以使用cursor對(duì)象的rollback方法來(lái)回滾所有的改動(dòng)。
我這里寫(xiě)一個(gè)代碼，對(duì)不存在的表進(jìn)行插入操作，當(dāng)拋出異常的時(shí)候，調(diào)用rollback進(jìn)行回滾，實(shí)現(xiàn)代碼如下：    
#!/usr/bin/python
 
# -*- coding: utf-8 -*-
 
import MySQLdb as mysql
 
 
try:
 conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb');
 cur = conn.cursor()
 cur.execute("insert into writers(name) values('wangzhengyi4')")
 cur.execute("insert into writers(name) values('bululu5')")
 cur.execute("insert into writerss(name) values('chenshan6')")
 
 conn.commit()
 
except mysql.Error, e:
 if conn:
  conn.rollback()
  print "Error happens, rollback is call"
 
finally:
 if conn:
  conn.close()

執(zhí)行結(jié)果如下：    
Error happens, rollback is call
因?yàn)榍皟蓷l數(shù)據(jù)是正確的插入操作，但是因?yàn)檎w回滾，所以數(shù)據(jù)庫(kù)里也沒(méi)有wangzhengyi4和bululu5這兩個(gè)數(shù)據(jù)的存在。