python 操作MySQL資料庫

Python 標準資料庫介面為 Python DB-API，Python DB-API為開發人員提供了資料庫應用程式介面。

Python 資料庫介面支援非常多的資料庫，你可以選擇適合你專案的資料庫：

• GadFly

• mSQL

• MySQL

• PostgreSQL

• Microsoft SQL Server 2000

• #Informix

• Interbase

• #Oracle

• Sybase

#你可以存取Python資料庫介面及API查看詳細的支援資料庫清單。

不同的資料庫你需要下載不同的DB API模組，例如你需要存取Oracle資料庫和Mysql數據，你需要下載Oracle和MySQL資料庫模組。

DB-API 是一個規範. 它定義了一系列必須的對象和資料庫訪問方式, 以便為各種各樣的底層數據庫系統和多種多樣的數據庫接口程序提供一致的訪問接口。

Python的DB-API，為大多數的資料庫實作了接口，使用它連接各資料庫後，就可以用相同的方式操作各資料庫。

Python DB-API使用流程：

• 引入 API 模組。

• 取得與資料庫的連線。

• 執行SQL語句和預存程序。

• 關閉資料庫連線。

---

什麼是MySQLdb?

MySQLdb 是用於Python連結Mysql資料庫的接口，它實作了Python 資料庫API 規格V2.0，基於MySQL C API 上建立的。

---

如何安裝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

安裝MySQLdb，請造訪http://sourceforge. net/projects/mysql-python ，(Linux平台可以存取：https://pypi.python.org/pypi/MySQL-python)從這裡可選擇適合您的平台的安裝包，分為預編譯的二進位檔案和原始碼安裝包。

如果您選擇二進位檔案發行版本的話，安裝過程基本上安裝提示即可完成。如果從原始程式碼進行安裝的話，則需要切換到MySQLdb發行版本的頂層目錄，並鍵入下列命令:

$ gunzip MySQL-python-1.2.2.tar.gz
$ tar -xvf MySQL-python-1.2.2.tar
$ cd MySQL-python-1.2.2
$ python setup.py build
$ python setup.py install

注意：請確保您有root權限來安裝上述模組。

---

資料庫連線

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

• 您已經建立了資料庫TESTDB.

• 在TESTDB資料庫中您已經建立了表EMPLOYEE

• #EMPLOYEE表欄位為FIRST_NAME, LAST_NAME, AGE, SEX 和INCOME。

• 連接資料庫TESTDB使用的使用者名稱為"testuser" ，密碼為"test123",你可以自己設定或直接使用root使用者名稱及其密碼，Mysql資料庫使用者授權請使用Grant指令。

• 在你的機上已經安裝了 Python  MySQLdb 模組。

• 如果您對sql語句不熟悉，可以存取我們的SQL基礎教學

實例：

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

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# 使用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" )

# 使用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" )

# 使用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" )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
       LAST_NAME, AGE, SEX, INCOME) \
       VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
       ('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" )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (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=%d,sex=%s,income=%d" % \
             (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"

# 关闭数据库连接
db.close()

以上腳本執行結果如下：

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

資料庫更新操作

更新作業用於更新資料表的的數據，以下實例將TESTDB表中的SEX 欄位全部修改為'M'，AGE 欄位遞增1：

• #!/usr/bin/python
  # -*- coding: UTF-8 -*-
  
  import MySQLdb
  
  # 打开数据库连接
  db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )
  
  # 使用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" )
  
  # 使用cursor()方法获取操作游标 
  cursor = db.cursor()
  
  # SQL 删除语句
  sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (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 > '%d'" % (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的子類別。