Python+MySQL資料庫操作(PyMySQL)


Python的資料庫介面標準是Python DB-API。大多數Python資料庫介面遵循這個標準。
可以為應用程式選擇正確的資料庫。Python資料庫API支援廣泛的資料庫伺服器,如 -

以下是可用的Python資料庫介面 - Python資料庫介面和API的列表。需要為要存取的每種資料庫下載一個單獨的DB API模組。 例如,如果需要存取Oracle資料庫和MySQL資料庫,則必須同時下載Oracle和MySQL資料庫模組。

DB API為盡可能使用Python結構和語法處理資料庫提供了最低標準。API包括以下內容:

  • 匯入API模組。
  • 獲取與資料庫的連線。
  • 發出SQL語句和儲存過程。
  • 關閉連線

Python具有內建的SQLite支援。 在本節中,我們將學習使用MySQL的相關概念和知識。 在早期Python版本一般都使用MySQLdb模組,但這個MySQL的流行介面與Python 3不相容。因此,在教學中將使用PyMySQL模組

1.什麼是PyMySQL?

PyMySQL是從Python連線到MySQL資料庫伺服器的介面。 它實現了Python資料庫API v2.0,並包含一個純Python的MySQL用戶端庫。 PyMySQL的目標是成為MySQLdb的替代品。

PyMySQL參考文件:http://pymysql.readthedocs.io/

2.如何安裝PyMySQL?

在使用PyMySQL之前,請確保您的機器上安裝了PyMySQL。只需在Python指令碼中輸入以下內容即可執行它 -

#!/usr/bin/python3

import PyMySQL

在 Windows 系統上,開啟命令提示字元 -

C:\Users\Administrator>python
Python 3.6.1 (v3.6.1:69c0db5, Mar 21 2017, 18:41:36) [MSC v.1900 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import PyMySQL
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ModuleNotFoundError: No module named 'PyMySQL'
>>>

如果產生如上結果,則表示PyMySQL模組尚未安裝。

最後一個穩定版本可以在PyPI上使用,可以通過pip命令來安裝-

:\Users\Administrator> pip install PyMySQL
Collecting PyMySQL
  Downloading PyMySQL-0.7.11-py2.py3-none-any.whl (78kB)
    51% |████████████████▋               | 
    40kB 109kB/s eta 0:0    64% |████████████████████▊       
    | 51kB 112kB/s eta    77% |█████████████████████████      | 61kB 135kB/s    90% |█████████████████████████████
    | 71kB 152    100% |████████████████████████████████| 81kB 163kB/s

Installing collected packages: PyMySQL
Successfully installed PyMySQL-0.7.11

C:\Users\Administrator>

或者(例如,如果pip不可用),可以從GitHub下載tarball,並按照以下方式安裝:

$ # X.X is the desired PyMySQL version (e.g. 0.5 or 0.6).
$ curl -L http://github.com/PyMySQL/PyMySQL/tarball/pymysql-X.X | tar xz
$ cd PyMySQL*
$ python setup.py install
$ # The folder PyMySQL* can be safely removed now.

注意 - 確保具有root許可權來安裝上述模組。

3.資料庫連線

在連線到MySQL資料庫之前,請確保以下幾點:

  • 已經建立了一個資料庫:test
  • 已經在test中建立了一個表:employee
  • employee表格包含:fist_namelast_nameagesexincome欄位。
  • MySQL使用者「root」和密碼「123456」可以存取:test
  • Python模組PyMySQL已正確安裝在您的計算機上。
  • 已經通過MySQL教學了解MySQL基礎知識。

建立表employee的語句為:

CREATE TABLE `employee` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` char(20) NOT NULL,
  `last_name` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `income` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

範例

以下是Python通過PyMySQL模組介面連線MySQL資料庫「test」的範例 -

注意:在 Windows 系統上,import PyMySQLimport pymysql 有區別。

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# execute SQL query using execute() method.
cursor.execute("SELECT VERSION()")

# Fetch a single row using fetchone() method.
data = cursor.fetchone()

print ("Database version : %s " % data)

# disconnect from server
db.close()

執行此指令碼時,會產生以下結果 -

Database version : 5.7.14-log

如果使用資料源建立連線,則會返回連線物件並將其儲存到db中以供進一步使用,否則將db設定為None。 接下來,db物件用於建立一個游標物件,用於執行SQL查詢。 最後,在結果列印出來之前,它確保資料庫連線關閉並釋放資源。

4.建立資料庫表

建立資料庫連線後,可以使用建立的游標的execute方法將資料庫表或記錄建立到資料庫表中。

範例

下面演示如何在資料庫:test中建立一張資料庫表:employee -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Drop table if it already exist using execute() method.
cursor.execute("DROP TABLE IF EXISTS employee")

# Create table as per requirement
sql = """CREATE TABLE `employee` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `first_name` char(20) NOT NULL,
  `last_name` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `income` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""

cursor.execute(sql)
print("Created table Successfull.")
# disconnect from server
db.close()

執行此指令碼時,會產生以下結果 -

Created table Successfull.

5.插入操作

當要將記錄建立到資料庫表中時,需要執行INSERT操作。

範例

以下範例執行SQL的INSERT語句以在EMPLOYEE表中建立一條(多條)記錄 -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Mac', 'Su', 20, 'M', 5000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

## 再次插入一條記錄
# Prepare SQL query to INSERT a record into the database.
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
   LAST_NAME, AGE, SEX, INCOME)
   VALUES ('Kobe', 'Bryant', 40, 'M', 8000)"""
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
print (sql)
print('Yes, Insert Successfull.')
# disconnect from server
db.close()

執行此指令碼時,會產生以下結果 -

Yes, Insert Successfull.

上述插入範例可以寫成如下動態建立SQL查詢 -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to INSERT a record into the database.
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, \
   LAST_NAME, AGE, SEX, INCOME) \
   VALUES ('%s', '%s', '%d', '%c', '%d' )" % \
   ('Max', 'Su', 25, 'F', 2800)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

範例

以下程式碼段是另一種執行方式,可以直接傳遞引數 -

..................................
user_id = "test123"
password = "password"

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

6.讀取操作

任何資料庫上的讀操作表示要從資料庫中讀取獲取一些有用的資訊。

在建立資料庫連線後,就可以對此資料庫進行查詢了。 可以使用fetchone()方法獲取單條記錄或fetchall()方法從資料庫表中獲取多個值。

  • fetchone() - 它獲取查詢結果集的下一行。 結果集是當使用游標物件來查詢表時返回的物件。

  • fetchall() - 它獲取結果集中的所有行。 如果已經從結果集中提取了一些行,則從結果集中檢索剩餘的行。

  • rowcount - 這是一個唯讀屬性,並返回受execute()方法影響的行數。

範例

以下過程查詢EMPLOYEE表中所有記錄的工資超過1000員工記錄資訊 -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()
# 按字典返回 
# cursor = db.cursor(pymysql.cursors.DictCursor)

# Prepare SQL query to select a record from the table.
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > %d" % (1000)
#print (sql)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      #print (row)
      fname = row[1]
      lname = row[2]
      age = row[3]
      sex = row[4]
      income = row[5]
      # Now print fetched result
      print ("name = %s %s,age = %s,sex = %s,income = %s" % \
             (fname, lname, age, sex, income ))
except:
   import traceback
   traceback.print_exc()

   print ("Error: unable to fetch data")

# disconnect from server
db.close()
name = Mac Su,age = 20,sex = M,income = 5000.0
name = Kobe Bryant,age = 40,sex = M,income = 8000.0

7.更新操作

UPDATE語句可對任何資料庫中的資料進行更新操作,它可用於更新資料庫中已有的一個或多個記錄。

以下程式將所有SEX欄位的值為「M」的記錄的年齡(age欄位)更新為增加一年。

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
#cursor = db.cursor()
cursor = db.cursor(pymysql.cursors.DictCursor)
# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to UPDATE required records
sql = "UPDATE EMPLOYEE SET AGE = AGE + 1 \
                          WHERE SEX = '%c'" % ('M')
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

8.刪除操作

當要從資料庫中刪除一些記錄時,那麼可以執行DELETE操作。 以下是刪除EMPLOYEEAGE超過40的所有記錄的程式 -

#!/usr/bin/python3
#coding=utf-8

import pymysql

# Open database connection
db = pymysql.connect("localhost","root","123456","test" )

# prepare a cursor object using cursor() method
cursor = db.cursor()

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (40)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()

9.執行事務

事務是確保資料一致性的一種機制。事務具有以下四個屬性 -

  • 原子性 - 事務要麼完成,要麼完全沒有發生。
  • 一致性 - 事務必須以一致的狀態開始,並使系統保持一致狀態。
  • 隔離性 - 事務的中間結果在當前事務外部不可見。
  • 永續性 - 當提交了一個事務,即使系統出現故障,效果也是持久的。

Python DB API 2.0提供了兩種提交或回滾事務的方法。

範例

已經知道如何執行事務。 這是一個類似的例子 -

# Prepare SQL query to DELETE required records
sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

9.1.COMMIT操作

提交是一種操作,它向資料庫發出信號以完成更改,並且在此操作之後,不會更改任何更改。

下面是一個簡單的例子演示如何呼叫commit()方法。

db.commit()

9.2.回滾操作

如果您對一個或多個更改不滿意,並且要完全還原這些更改,請使用rollback()方法。

下面是一個簡單的例子演示如何呼叫rollback()方法。

db.rollback()

10.斷開資料庫連線

要斷開資料庫連線,請使用close()方法。

db.close()

如果使用者使用close()方法關閉與資料庫的連線,則任何未完成的事務都將被資料庫回滾。 但是,您的應用程式不會依賴於資料級別的實現細節,而是明確地呼叫提交或回滾。

11.處理錯誤

錯誤有很多來源。一些範例是執行的SQL語句中的語法錯誤,連線失敗或為已取消或已完成語句控制代碼呼叫fetch方法等等。

DB API定義了每個資料庫模組中必須存在的許多錯誤。下表列出了這些異常和錯誤 -

編號 異常 描述
1 Warning 用於非致命問題,是StandardError的子類。
2 Error 錯誤的基礎類別,是StandardError的子類。
3 InterfaceError 用於資料庫模組中的錯誤,但不是資料庫本身,是Error的子類。
4 DatabaseError 用於資料庫中的錯誤,是Error的子類。
5 DataError DatabaseError的子類參照資料中的錯誤。
6 OperationalError DatabaseError的子類,涉及如丟失與資料庫的連線等錯誤。 這些錯誤通常不在Python指令碼程式的控制之內。
7 IntegrityError DatabaseError 子類錯誤,可能會損害關係完整性,例如唯一性約束和外來鍵。
8 InternalError DatabaseError的子類,指的是資料庫模組內部的錯誤,例如游標不再活動。
9 ProgrammingError DatabaseError的子類,它參照錯誤,如錯誤的表名和其他安全。
10 NotSupportedError DatabaseError的子類,用於嘗試呼叫不支援的功能。

Python指令碼應該處理這些錯誤,但在使用任何上述異常之前,請確保您的PyMySQL支援該異常。 可以通過閱讀DB API 2.0規範獲得更多關於它們的資訊。