[Python] 學習筆記之MySQL資料庫操作

標籤：

1 Python標準資料庫介面DB-API介紹

　　Python標準資料庫介面為 Python DB-API，它為開發人員提供了資料庫應用編程介面。Python DB-API支援很多種的資料庫，你可以選擇跟自己項目相關的資料庫。Python DB-API支援的資料庫如下所示：

• GadFly
• mSQL
• MySQL
• PostgreSQL
• Microsoft SQL Server 2000
• Informix
• Interbase
• Oracle
• Sybase

　　不同的資料庫你需要下載不同的DB API模組，例如你需要訪問Oracle資料庫和Mysql資料，你需要下載Oracle和MySQL資料庫模組。Python的DB-API，為大多數的資料庫實現了介面，使用它串連各資料庫後，就可以用相同的方式操作各資料庫。

　　Python DB-API使用流程：

　　1）引入 API 模組。

　　2）擷取與資料庫的串連。

　　3）執行SQL語句和預存程序。

　　4）關閉資料庫連接。

　　Python的資料庫學習，主要在於學習DB-API的學習流程，記住這四步，你就已經成功了一大半了。

2 MySQL的安裝

　　因為博主目前只關注MySQL的使用，所以本文中只限於介紹如何利用Python來操作MySQL資料庫。下面簡單介紹一下如何為自己的Python IDE安裝MySQL：

　　Step1 確認是否已經安裝有MySQL，執行以下代碼：

# encoding: utf-8#!/usr/bin/python
import MySQLdb

　　如果執行後報錯就說明沒有安裝MySQL，否則則證明你已經無需安裝MySQL。

　　Step2 下載MySQL並安裝：

　　一：請點這裡；

　　二：請點這裡。

　　具體的安裝步驟和Python的其他庫安裝方式類似，不在贅述。你可以用Step1的方法來確認你是否已經成功安裝MySQL。

3 資料庫連接

　　串連資料庫前，請先確認以下事項：串連資料庫TESTDB使用的使用者名稱為 "testuser" ，密碼為 "test123",你可以可以自己設定或者直接使用root使用者名稱及其密碼。

　　以下執行個體連結Mysql的TESTDB資料庫，對應了DB-API的四個步驟：

# encoding: utf-8#!/usr/bin/python
# 1 引入API模組import MySQLdb

# 2 擷取與資料庫的串連

# 開啟資料庫連接 
db = MySQLdb.connect("localhost","testuser","test123","TESTDB" )

# 3 執行SQL語句和預存程序
# 使用cursor()方法擷取操作遊標 cursor = db.cursor()# 使用execute方法執行SQL語句cursor.execute("SELECT VERSION()")# 使用 fetchone() 方法擷取一條資料庫。data = cursor.fetchone()print "Database version : %s " % data

# 4 關閉資料庫連接# 關閉資料庫連接db.close()

　　執行結果顯示：

Database version : 5.0.45

4 建立資料庫表

　　我們嘗試建立一個自訂的資料庫表：

　　資料庫表名稱：EMPLOYEEEMPLOYEE

　　資料表欄位為：FIRST_NAME，LAST_NAME，AGE，SEX 和 INCOME。

# encoding: utf-8#!/usr/bin/pythonimport 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()

5 插入資料操作

　　使用SQL INSERT 語句向資料庫表 EMPLOYEE 插入記錄：

# encoding: utf-8#!/usr/bin/pythonimport 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()

　　在這裡提幾個重要的點：

　　1）請注意一定要有conn.commit()這句來提交事務，要不然不能真正的插入資料。

　　2）dollback()是復原操作，復原（Rollback）指的是程式或資料處理錯誤，將程式或資料恢複到上一次正確狀態的行為。復原包括程式復原和資料復原等類型。

6 資料庫查詢操作

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

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

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

# encoding: utf-8#!/usr/bin/pythonimport 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

7 資料庫更新操作

　　更新操作用於更新資料表的的資料，以下執行個體將 TESTDB表中的 SEX 欄位全部修改為 ‘M‘，AGE 欄位遞增1：

# encoding: utf-8#!/usr/bin/pythonimport 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()

8 資料庫的事務機制

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

　　事務應該具有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（）方法復原當前遊標的所有操作。每一個方法都開始了一個新的事務。

9 常用函數

　　下面總結一下常用的函數：

　　1）Connect() 方法用於建立資料庫的串連，裡面可以指定參數：使用者名稱，密碼，主機等資訊。這隻是串連到了資料庫，要想操作資料庫需要建立遊標。

　　2）commit() 提交
　　3）rollback() 復原

　　4）cursor用來執行命令的方法:
　　　　callproc(self, procname, args):用來執行預存程序,接收的參數為預存程序名和參數列表,傳回值為受影響的行數
　　　　execute(self, query, args):執行單條sql語句,接收的參數為sql語句本身和使用的參數列表,傳回值為受影響的行數
　　　　executemany(self, query, args):執行單挑sql語句,但是重複執行參數列表裡的參數,傳回值為受影響的行數
　　　　nextset(self):移動到下一個結果集
　　5）cursor用來接收傳回值的方法:
　　　　fetchall(self):接收全部的返回結果行.
　　　　fetchmany(self, size=None):接收size條返回結果行.如果size的值大於返回的結果行的數量,則會返回cursor.arraysize條資料.
　　　　fetchone(self):返回一條結果行.
　　6）scroll(self, value, mode=‘relative‘):移動指標到某一行.如果mode=‘relative‘,則表示從當前所在行移動value條,如果 mode=‘absolute‘,則表示從結果集的第一行移動value條.

10 參考內容

　　[1] W3CSCHOOL.CC python操作mysql資料庫

　　[2] 一個人的旅行

　　[3] 蟲師

[Python] 學習筆記之MySQL資料庫操作