Python之 操作 MySQL 資料庫

什麼是MySQLdb?

MySQLdb 是用於Python連結Mysql資料庫的介面，它實現了 Python 資料庫 API 規範 V2.0，基於 MySQL C API 上建立的。

---

安裝 Python MySQLdb 模組

linux版本:（配置好yum源）

pip install MySQL-python
yum install MySQL-python

如何載入MySQLdb?

為了用DB-API編寫MySQL指令碼，必須確保已經安裝了MySQL。複製以下程式碼，並執行：

#!/usr/bin/python


# -*- coding: UTF-8 -*-




import MySQLdb

如果執行後的輸出結果如下所示，意味著你沒有安裝 MySQLdb 模組：

Traceback (most recent call last):


  File "test.py", line 3, in <module>


    import MySQLdb


ImportError: No module named MySQLdb

 

---

資料庫連線

連線資料庫前，請先確認以下事項：

• 您已經建立了資料庫 TESTDB.
• 在TESTDB資料庫中您已經建立了表 EMPLOYEE
• EMPLOYEE表欄位為 FIRST_NAME, LAST_NAME, AGE, SEX 和 INCOME。
• 連線資料庫TESTDB使用的使用者名稱為 "testuser" ，密碼為 "test123",你可以可以自己設定或者直接使用root使用者名稱及其密碼，Mysql資料庫使用者授權請使用Grant命令。
• 在你的機子上已經安裝了 Python MySQLdb 模組。

例項：

以下例項連結Mysql的TESTDB資料庫：

#!/usr/bin/python


# -*- coding: UTF-8 -*-




import MySQLdb




# 開啟資料庫連線


db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )




# 使用cursor()方法獲取操作遊標 


cursor = db.cursor()




# 使用execute方法執行SQL語句


cursor.execute("SELECT VERSION()")




# 使用 fetchone() 方法獲取一條資料


data = cursor.fetchone()




print "Database version : %s " % data




# 關閉資料庫連線


db.close()

執行以上指令碼輸出結果如下：

Database version : 5.0.45

---

建立資料庫表

如果資料庫連線存在我們可以使用execute()方法來為資料庫建立表，如下所示建立表EMPLOYEE：

#!/usr/bin/python


# -*- coding: UTF-8 -*-




import MySQLdb




# 開啟資料庫連線


db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )




# 使用cursor()方法獲取操作遊標 


cursor = db.cursor()




# 如果資料表已經存在使用 execute() 方法刪除表。


cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")




# 建立資料表SQL語句


sql = """CREATE TABLE EMPLOYEE (


         FIRST_NAME  CHAR(20) NOT NULL,


         LAST_NAME  CHAR(20),


         AGE INT,  


         SEX CHAR(1),


         INCOME FLOAT )"""




cursor.execute(sql)




# 關閉資料庫連線


db.close()

---

資料庫插入操作

以下例項使用執行 SQL INSERT 語句向表 EMPLOYEE 插入記錄：

#!/usr/bin/python


# -*- coding: UTF-8 -*-




import MySQLdb




# 開啟資料庫連線


db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )




# 使用cursor()方法獲取操作遊標 


cursor = db.cursor()




# SQL 插入語句


sql = """INSERT INTO EMPLOYEE(FIRST_NAME,


         LAST_NAME, AGE, SEX, INCOME)


         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""


try:


   # 執行sql語句


   cursor.execute(sql)


   # 提交到資料庫執行


   db.commit()


except:


   # Rollback in case there is any error


   db.rollback()




# 關閉資料庫連線


db.close()

以上例子也可以寫成如下形式：

#!/usr/bin/python


# -*- coding: UTF-8 -*-




import MySQLdb




# 開啟資料庫連線


db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )




# 使用cursor()方法獲取操作遊標 


cursor = db.cursor()




# SQL 插入語句


sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \


       LAST_NAME, AGE, SEX, INCOME) \


       VALUES (%s, %s, %s, %s, %s )" % \


       ('Mac', 'Mohan', 20, 'M', 2000)


try:


   # 執行sql語句


   cursor.execute(sql)


   # 提交到資料庫執行


   db.commit()


except:


   # 發生錯誤時回滾


   db.rollback()




# 關閉資料庫連線


db.close()

例項：

以下程式碼使用變數向SQL語句中傳遞引數:

..................................


user_id = "test123"


password = "password"




con.execute('insert into Login values(%s, %s)' % \


             (user_id, password))


..................................

---

資料庫查詢操作

Python查詢Mysql使用 fetchone() 方法獲取單條資料, 使用fetchall() 方法獲取多條資料。

• fetchone():  該方法獲取下一個查詢結果集。結果集是一個物件
• fetchall(): 接收全部的返回結果行.
• rowcount:  這是一個只讀屬性，並返回執行execute()方法後影響的行數。

例項：

查詢EMPLOYEE表中salary（工資）欄位大於1000的所有資料：

#!/usr/bin/python


# -*- coding: UTF-8 -*-




import MySQLdb




# 開啟資料庫連線


db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )




# 使用cursor()方法獲取操作遊標 


cursor = db.cursor()




# SQL 查詢語句


sql = "SELECT * FROM EMPLOYEE \


       WHERE INCOME > %s" % (1000)


try:


   # 執行SQL語句


   cursor.execute(sql)


   # 獲取所有記錄列表


   results = cursor.fetchall()


   for row in results:


      fname = row[0]


      lname = row[1]


      age = row[2]


      sex = row[3]


      income = row[4]


      # 列印結果


      print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \


             (fname, lname, age, sex, income )


except:


   print "Error: unable to fecth data"




# 關閉資料庫連線


db.close()

以上指令碼執行結果如下：

fname=Mac, lname=Mohan, age=20, sex=M, income=2000

---

資料庫更新操作

更新操作用於更新資料表的的資料，以下例項將 EMPLOYEE 表中的 SEX 欄位為 'M' 的 AGE 欄位遞增 1：

#!/usr/bin/python


# -*- coding: UTF-8 -*-




import MySQLdb




# 開啟資料庫連線


db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )




# 使用cursor()方法獲取操作遊標 


cursor = db.cursor()




# SQL 更新語句


sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = '%c'" % ('M')


try:


   # 執行SQL語句


   cursor.execute(sql)


   # 提交到資料庫執行


   db.commit()


except:


   # 發生錯誤時回滾


   db.rollback()




# 關閉資料庫連線


db.close()

---

刪除操作

刪除操作用於刪除資料表中的資料，以下例項演示了刪除資料表 EMPLOYEE 中 AGE 大於 20 的所有資料：

#!/usr/bin/python


# -*- coding: UTF-8 -*-




import MySQLdb




# 開啟資料庫連線


db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )




# 使用cursor()方法獲取操作遊標 


cursor = db.cursor()




# SQL 刪除語句


sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)


try:


   # 執行SQL語句


   cursor.execute(sql)


   # 提交修改


   db.commit()


except:


   # 發生錯誤時回滾


   db.rollback()




# 關閉連線


db.close()

執行事務

事務機制可以確保資料一致性。

事務應該具有4個屬性：原子性、一致性、隔離性、永續性。這四個屬性通常稱為ACID特性。

• 原子性（atomicity）。一個事務是一個不可分割的工作單位，事務中包括的諸操作要麼都做，要麼都不做。
• 一致性（consistency）。事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。
• 隔離性（isolation）。一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的資料對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾。
• 永續性（durability）。持續性也稱永久性（permanence），指一個事務一旦提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

Python DB API 2.0 的事務提供了兩個方法 commit 或 rollback。

例項：

# SQL刪除記錄語句


sql = "DELETE FROM EMPLOYEE WHERE AGE > %s" % (20)


try:


   # 執行SQL語句


   cursor.execute(sql)


   # 向資料庫提交


   db.commit()


except:


   # 發生錯誤時回滾


   db.rollback()

對於支援事務的資料庫， 在Python資料庫程式設計中，當遊標建立之時，就自動開始了一個隱形的資料庫事務。

commit()方法遊標的所有更新操作，rollback（）方法回滾當前遊標的所有操作。每一個方法都開始了一個新的事務。

---

錯誤處理

DB API中定義了一些資料庫操作的錯誤及異常，下表列出了這些錯誤和異常:

異常	描述
Warning	當有嚴重警告時觸發，例如插入資料是被截斷等等。必須是 StandardError 的子類。
Error	警告以外所有其他錯誤類。必須是 StandardError 的子類。
InterfaceError	當有資料庫介面模組本身的錯誤（而不是資料庫的錯誤）發生時觸發。 必須是Error的子類。
DatabaseError	和資料庫有關的錯誤發生時觸發。 必須是Error的子類。
DataError	當有資料處理時的錯誤發生時觸發，例如：除零錯誤，資料超範圍等等。 必須是DatabaseError的子類。
OperationalError	指非使用者控制的，而是運算元據庫時發生的錯誤。例如：連線意外斷開、 資料庫名未找到、事務處理失敗、記憶體分配錯誤等等運算元據庫是發生的錯誤。 必須是DatabaseError的子類。
IntegrityError	完整性相關的錯誤，例如外來鍵檢查失敗等。必須是DatabaseError子類。
InternalError	資料庫的內部錯誤，例如遊標（cursor）失效了、事務同步失敗等等。 必須是DatabaseError子類。
ProgrammingError	程式錯誤，例如資料表（table）沒找到或已存在、SQL語句語法錯誤、 引數數量錯誤等等。必須是DatabaseError的子類。
NotSupportedError	不支援錯誤，指使用了資料庫不支援的函式或API等。例如在連線物件上 使用.rollback()函式，然而資料庫並不支援事務或者事務已關閉。 必須是DatabaseError的子類。