再Python程式中操作MySQL的基本方法

Python操作Mysql

最近在學習python，這種指令碼語言毫無疑問的會跟資料庫產生關聯，因此這裡介紹一下如何使用python操作mysql資料庫。我python也是零基礎學起，所以本篇部落格針對的是python初學者，大牛可以選擇繞道。

另外，本篇基於的環境是Ubuntu13.10，使用的python版本是2.7.5。
MYSQL資料庫

MYSQL是一個全球領先的開來源資料庫管理系統。它是一個支援多使用者、多線程的資料庫管理系統，與Apache、PHP、Linux共同組成LAMP平台，在web應用中廣泛使用，例如Wikipedia和YouTube。MYSQL包含兩個版本：伺服器系統和嵌入式系統。
環境配置

在我們開始文法學習之前，還需要按裝mysql和python對mysql操作的模組。

安裝mysql:

sudo apt-get install mysql-server

安裝過程中會提示你輸入root帳號的密碼，符合密碼規範即可。

接下來，需要安裝python對mysql的操作模組：

sudo apt-get install python-mysqldb

這裡需要注意：安裝完python-mysqldb之後，我們預設安裝了兩個python操作模組，分別是支援C語言API的_mysql和支援Python API的MYSQLdb。稍後會重點講解MYSQLdb模組的使用。

接下來，我們進入MYSQL，建立一個測試資料庫叫testdb。建立命令為：

create database testdb;

然後，我們建立一個測試賬戶來操作這個testdb資料庫，建立和授權命令如下：

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語言API函數，它與python標準的資料庫API介面是不相容的。我更推薦大家使用物件導向的MYSQLdb模組才操作mysql，這裡只給出一個使用_mysql模組的例子，這個模組不是我們學習的重點，我們只需要瞭解有這個模組就好了。

#!/usr/bin/python# -*- coding: utf-8 -*-import _mysqlimport systry:  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()

這個代碼主要是擷取當前mysql的版本，大家可以類比敲一下這部分代碼然後運行一下。
MYSQLdb module

MYSQLdb是在_mysql模組的基礎上進一步進行封裝，並且與python標準資料庫API介面相容，這使得代碼更容易被移植。Python更推薦使用這個MYSQLdb模組來進行MYSQL操作。

#!/usr/bin/python# -*- coding: utf-8 -*-import MySQLdb as mysqltry:  conn = mysql.connect('127.0.0.1', 'testuser', 'test123', 'testdb')  cur = conn.cursor()  cur.execute("SELECT VERSION()")  version = cur.fetchone()  print "Database version : %s" % versionexcept mysql.Error, e:  print "Error %d:%s" % (e.args[0], e.args[1])  exit(1)finally:  if conn:    conn.close()

我們匯入了MySQLdb模組並把它重新命名為mysql，然後調用MySQLdb模組的提供的API方法來操作資料庫。同樣也是擷取當前主機的安裝的mysql版本號碼。
建立新表

接下來，我們通過MySQLdb模組建立一個表，並在其中填充部分資料。實現代碼如下：

#!/usr/bin/python# -*- coding: utf-8 -*-import MySQLdb as mysqlconn = 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語句。with語句會執行conn對象的enter()和__exit()方法，省去了自己寫try/catch/finally了。

執行完成後，我們可以通過mysql-client用戶端查看是否插入成功，查詢語句：

select * from writers;

查詢結果如下：

查詢資料

剛才往表裡插入了部分資料，接下來，我們從表中取出插入的資料，代碼如下：

#!/usr/bin/pythonimport MySQLdb as mysqlconn = 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

查詢結果如下：

(1L, 'wangzhengyi')(2L, 'bululu')(3L, 'chenshan')

dictionary cursor

我們剛才不論是建立資料庫還是查詢資料庫，都用到了cursor。在MySQLdb模組有許多種cursor類型，預設的cursor是以元組的元組形式返回資料的。當我們使用dictionary cursor時，資料是以python字典形式返回的。這樣我們就可以通過列名擷取查詢資料了。

還是剛才查詢資料的代碼，改為dictionary cursor只需要修改一行代碼即可，如下所示：

#!/usr/bin/pythonimport MySQLdb as mysqlconn = 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，查詢結果如下：

id is 1, name is wangzhengyiid is 2, name is bululuid is 3, name is chenshan

先行編譯

之前寫過php的同學應該對先行編譯很瞭解，先行編譯可以協助我們防止sql注入等web攻擊還能協助提高效能。當然，python肯定也是支援先行編譯的。先行編譯的實現也比較簡單，就是用%等預留位置來替換真正的變數。例如查詢id為3的使用者的資訊，使用先行編譯的代碼如下：

#!/usr/bin/pythonimport MySQLdb as mysqlconn = 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'])

我這裡使用了一個%s的預留位置來替換“3”，代表需要傳入的是一個字串類型。如果傳入的不是string類型，則會運行報錯。
事務

事務是指在一個或者多個資料庫中對資料的原子操作。在一個事務中，所有的SQL語句的影響要不就全部提交到資料庫，要不就全部都復原。

對於支援事務機制的資料庫，python介面在建立cursor的時候就開始了一個事務。可以通過cursor對象的commit()方法來提交所有的改動，也可以使用cursor對象的rollback方法來復原所有的改動。

我這裡寫一個代碼，對不存在的表進行插入操作，當拋出異常的時候，調用rollback進行復原，實現代碼如下：

#!/usr/bin/python# -*- coding: utf-8 -*-import MySQLdb as mysqltry:  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()

執行結果如下：

Error happens, rollback is call

因為前兩條資料是正確的插入操作，但是因為整體復原，所以資料庫裡也沒有wangzhengyi4和bululu5這兩個資料的存在。

