MySQL+Python連接和操作
Python的數據庫接口標準是Python DB-API。大多數Python數據庫接口遵循這個標準。
可以爲應用程序選擇正確的數據庫。Python數據庫API支持廣泛的數據庫服務器,如 -
- GadFly
- mSQL
- MySQL
- PostgreSQL
- Microsoft SQL Server 2000
- Informix
- Interbase
- Oracle
- Sybase
- SQLite
以下是可用的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_name
,last_name
,age
,sex
和income
字段。 - 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 PyMySQL
和import 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
操作。 以下是刪除EMPLOYEE
中AGE
超過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規範獲得更多關於它們的信息。