Python的數(shù)據(jù)庫(kù)接口標(biāo)準(zhǔn)是Python DB-API。大多數(shù)Python數(shù)據(jù)庫(kù)接口遵循這個(gè)標(biāo)準(zhǔn)。
可以為應(yīng)用程序選擇正確的數(shù)據(jù)庫(kù)。Python數(shù)據(jù)庫(kù)API支持廣泛的數(shù)據(jù)庫(kù)服務(wù)器，如 -

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

以下是可用的Python數(shù)據(jù)庫(kù)接口 - Python數(shù)據(jù)庫(kù)接口和API的列表。需要為要訪問(wèn)的每種數(shù)據(jù)庫(kù)下載一個(gè)單獨(dú)的DB API模塊。 例如，如果需要訪問(wèn)Oracle數(shù)據(jù)庫(kù)和MySQL數(shù)據(jù)庫(kù)，則必須同時(shí)下載Oracle和MySQL數(shù)據(jù)庫(kù)模塊。

DB API為盡可能使用Python結(jié)構(gòu)和語(yǔ)法處理數(shù)據(jù)庫(kù)提供了最低標(biāo)準(zhǔn)。API包括以下內(nèi)容：

• 導(dǎo)入API模塊。
• 獲取與數(shù)據(jù)庫(kù)的連接。
• 發(fā)出SQL語(yǔ)句和存儲(chǔ)過(guò)程。
• 關(guān)閉連接

Python具有內(nèi)置的SQLite支持。 在本節(jié)中，我們將學(xué)習(xí)使用MySQL的相關(guān)概念和知識(shí)。 在早期Python版本一般都使用MySQLdb模塊，但這個(gè)MySQL的流行接口與Python 3不兼容。因此，在教程中將使用PyMySQL模塊。

1.什么是PyMySQL？

PyMySQL是從Python連接到MySQL數(shù)據(jù)庫(kù)服務(wù)器的接口。 它實(shí)現(xiàn)了Python數(shù)據(jù)庫(kù)API v2.0，并包含一個(gè)純Python的MySQL客戶端庫(kù)。 PyMySQL的目標(biāo)是成為MySQLdb的替代品。

PyMySQL參考文檔：http://pymysql.readthedocs.io/

2.如何安裝PyMySQL？

在使用PyMySQL之前，請(qǐng)確保您的機(jī)器上安裝了PyMySQL。只需在Python腳本中輸入以下內(nèi)容即可執(zhí)行它 -

#!/usr/bin/python3

import PyMySQL

在 Windows 系統(tǒng)上，打開(kāi)命令提示符 -

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'
>>>

如果產(chǎn)生如上結(jié)果，則表示PyMySQL模塊尚未安裝。

最后一個(gè)穩(wěn)定版本可以在PyPI上使用，可以通過(guò)pip命令來(lái)安裝-

:\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權(quán)限來(lái)安裝上述模塊。

3.數(shù)據(jù)庫(kù)連接

在連接到MySQL數(shù)據(jù)庫(kù)之前，請(qǐng)確保以下幾點(diǎn)：

• 已經(jīng)創(chuàng)建了一個(gè)數(shù)據(jù)庫(kù)：test。
• 已經(jīng)在test中創(chuàng)建了一個(gè)表:employee。
• employee表格包含:fist_name，last_name，age，sex和income字段。
• MySQL用戶“root”和密碼“123456”可以訪問(wèn)：test。
• Python模塊PyMySQL已正確安裝在您的計(jì)算機(jī)上。
• 已經(jīng)通過(guò)MySQL教程了解MySQL基礎(chǔ)知識(shí)。

創(chuàng)建表employee的語(yǔ)句為：

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;

實(shí)例

以下是Python通過(guò)PyMySQL模塊接口連接MySQL數(shù)據(jù)庫(kù)“test”的示例 -

注意：在 Windows 系統(tǒng)上，import PyMySQL 和 import pymysql 有區(qū)別。

#!/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()

運(yùn)行此腳本時(shí)，會(huì)產(chǎn)生以下結(jié)果 -

Database version : 5.7.14-log

如果使用數(shù)據(jù)源建立連接，則會(huì)返回連接對(duì)象并將其保存到db中以供進(jìn)一步使用，否則將db設(shè)置為None。 接下來(lái)，db對(duì)象用于創(chuàng)建一個(gè)游標(biāo)對(duì)象，用于執(zhí)行SQL查詢。 最后，在結(jié)果打印出來(lái)之前，它確保數(shù)據(jù)庫(kù)連接關(guān)閉并釋放資源。

4.創(chuàng)建數(shù)據(jù)庫(kù)表

建立數(shù)據(jù)庫(kù)連接后，可以使用創(chuàng)建的游標(biāo)的execute方法將數(shù)據(jù)庫(kù)表或記錄創(chuàng)建到數(shù)據(jù)庫(kù)表中。

示例

下面演示如何在數(shù)據(jù)庫(kù)：test中創(chuàng)建一張數(shù)據(jù)庫(kù)表：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()

運(yùn)行此腳本時(shí)，會(huì)產(chǎn)生以下結(jié)果 -

Created table Successfull.

5.插入操作

當(dāng)要將記錄創(chuàng)建到數(shù)據(jù)庫(kù)表中時(shí)，需要執(zhí)行INSERT操作。

示例

以下示例執(zhí)行SQL的INSERT語(yǔ)句以在EMPLOYEE表中創(chuàng)建一條(多條)記錄 -

#!/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()

運(yùn)行此腳本時(shí)，會(huì)產(chǎn)生以下結(jié)果 -

Yes, Insert Successfull.

上述插入示例可以寫(xiě)成如下動(dòng)態(tài)創(chuàng)建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()

示例

以下代碼段是另一種執(zhí)行方式，可以直接傳遞參數(shù) -

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

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

6.讀取操作

任何數(shù)據(jù)庫(kù)上的讀操作表示要從數(shù)據(jù)庫(kù)中讀取獲取一些有用的信息。

在建立數(shù)據(jù)庫(kù)連接后，就可以對(duì)此數(shù)據(jù)庫(kù)進(jìn)行查詢了。 可以使用fetchone()方法獲取單條記錄或fetchall()方法從數(shù)據(jù)庫(kù)表中獲取多個(gè)值。

• fetchone() - 它獲取查詢結(jié)果集的下一行。 結(jié)果集是當(dāng)使用游標(biāo)對(duì)象來(lái)查詢表時(shí)返回的對(duì)象。

• fetchall() - 它獲取結(jié)果集中的所有行。 如果已經(jīng)從結(jié)果集中提取了一些行，則從結(jié)果集中檢索剩余的行。

• rowcount - 這是一個(gè)只讀屬性，并返回受execute()方法影響的行數(shù)。

示例

以下過(guò)程查詢EMPLOYEE表中所有記錄的工資超過(guò)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語(yǔ)句可對(duì)任何數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行更新操作，它可用于更新數(shù)據(jù)庫(kù)中已有的一個(gè)或多個(gè)記錄。

以下程序?qū)⑺?code>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.刪除操作

當(dāng)要從數(shù)據(jù)庫(kù)中刪除一些記錄時(shí)，那么可以執(zhí)行DELETE操作。 以下是刪除EMPLOYEE中AGE超過(guò)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.執(zhí)行事務(wù)

事務(wù)是確保數(shù)據(jù)一致性的一種機(jī)制。事務(wù)具有以下四個(gè)屬性 -

• 原子性 - 事務(wù)要么完成，要么完全沒(méi)有發(fā)生。
• 一致性 - 事務(wù)必須以一致的狀態(tài)開(kāi)始，并使系統(tǒng)保持一致?tīng)顟B(tài)。
• 隔離性 - 事務(wù)的中間結(jié)果在當(dāng)前事務(wù)外部不可見(jiàn)。
• 持久性 - 當(dāng)提交了一個(gè)事務(wù)，即使系統(tǒng)出現(xiàn)故障，效果也是持久的。

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

示例

已經(jīng)知道如何執(zhí)行事務(wù)。 這是一個(gè)類似的例子 -

# 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操作

提交是一種操作，它向數(shù)據(jù)庫(kù)發(fā)出信號(hào)以完成更改，并且在此操作之后，不會(huì)更改任何更改。

下面是一個(gè)簡(jiǎn)單的例子演示如何調(diào)用commit()方法。

db.commit()

9.2.回滾操作

如果您對(duì)一個(gè)或多個(gè)更改不滿意，并且要完全還原這些更改，請(qǐng)使用rollback()方法。

下面是一個(gè)簡(jiǎn)單的例子演示如何調(diào)用rollback()方法。

db.rollback()

10.斷開(kāi)數(shù)據(jù)庫(kù)連接

要斷開(kāi)數(shù)據(jù)庫(kù)連接，請(qǐng)使用close()方法。

db.close()

如果用戶使用close()方法關(guān)閉與數(shù)據(jù)庫(kù)的連接，則任何未完成的事務(wù)都將被數(shù)據(jù)庫(kù)回滾。 但是，您的應(yīng)用程序不會(huì)依賴于數(shù)據(jù)級(jí)別的實(shí)現(xiàn)細(xì)節(jié)，而是明確地調(diào)用提交或回滾。

11.處理錯(cuò)誤

錯(cuò)誤有很多來(lái)源。一些示例是執(zhí)行的SQL語(yǔ)句中的語(yǔ)法錯(cuò)誤，連接失敗或?yàn)橐讶∠蛞淹瓿烧Z(yǔ)句句柄調(diào)用fetch方法等等。

DB API定義了每個(gè)數(shù)據(jù)庫(kù)模塊中必須存在的許多錯(cuò)誤。下表列出了這些異常和錯(cuò)誤 -

Python腳本應(yīng)該處理這些錯(cuò)誤，但在使用任何上述異常之前，請(qǐng)確保您的PyMySQL支持該異常。 可以通過(guò)閱讀DB API 2.0規(guī)范獲得更多關(guān)于它們的信息。