Python 3 - Truy cập cơ sở dữ liệu MySQL

Tiêu chuẩn Python cho giao diện cơ sở dữ liệu là Python DB-API. Hầu hết các giao diện cơ sở dữ liệu Python đều tuân theo tiêu chuẩn này.

Bạn có thể chọn cơ sở dữ liệu phù hợp cho ứng dụng của mình. API cơ sở dữ liệu Python hỗ trợ một loạt các máy chủ cơ sở dữ liệu như -

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

Đây là danh sách các giao diện cơ sở dữ liệu Python có sẵn - Python Database Interfaces and APIs . Bạn phải tải xuống mô-đun API DB riêng biệt cho mỗi cơ sở dữ liệu mà bạn cần truy cập. Ví dụ: nếu bạn cần truy cập cơ sở dữ liệu Oracle cũng như cơ sở dữ liệu MySQL, bạn phải tải xuống cả mô-đun cơ sở dữ liệu Oracle và MySQL.

API DB cung cấp một tiêu chuẩn tối thiểu để làm việc với cơ sở dữ liệu sử dụng cấu trúc và cú pháp Python nếu có thể. API này bao gồm những điều sau:

Nhập mô-đun API.
Có được kết nối với cơ sở dữ liệu.
Phát hành câu lệnh SQL và thủ tục được lưu trữ.
Đóng kết nối

Python có hỗ trợ sẵn cho SQLite. Trong phần này, chúng ta sẽ tìm hiểu tất cả các khái niệm sử dụng MySQL. Mô-đun MySQLdb, một giao diện phổ biến với MySQL không tương thích với Python 3. Thay vào đó, chúng tôi sẽ sử dụng mô-đun PyMySQL .

PyMySQL là gì?

PyMySQL là một giao diện để kết nối với máy chủ cơ sở dữ liệu MySQL từ Python. Nó triển khai API cơ sở dữ liệu Python v2.0 và chứa thư viện khách MySQL thuần Python. Mục tiêu của PyMySQL là để thay thế MySQLdb.

Làm cách nào để cài đặt PyMySQL?

Trước khi tiếp tục, bạn đảm bảo rằng bạn đã cài đặt PyMySQL trên máy của mình. Chỉ cần nhập đoạn mã sau vào tập lệnh Python của bạn và thực thi nó -

#!/usr/bin/python3

import pymysql

Nếu nó tạo ra kết quả sau, thì có nghĩa là mô-đun MySQLdb chưa được cài đặt -

Traceback (most recent call last):
   File "test.py", line 3, in <module>
      Import pymysql
ImportError: No module named pymysql

Bản phát hành ổn định cuối cùng có sẵn trên PyPI và có thể được cài đặt bằng pip -

pip install pymysql

Ngoài ra (ví dụ: nếu không có pip), có thể tải xuống tarball từ GitHub và cài đặt với Setuptools như sau:

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

Note - Đảm bảo rằng bạn có quyền root để cài đặt mô-đun trên.

Kết nối cơ sở dữ liệu

Trước khi kết nối với cơ sở dữ liệu MySQL, hãy đảm bảo các điểm sau:

Bạn đã tạo một cơ sở dữ liệu TESTDB.
Bạn đã tạo một bảng EMPLOYEE trong TESTDB.
Bảng này có các trường FIRST_NAME, LAST_NAME, AGE, SEX và THU NHẬP.
ID người dùng "testuser" và mật khẩu "test123" được đặt để truy cập TESTDB.
Mô-đun Python PyMySQL được cài đặt đúng cách trên máy của bạn.
Bạn đã xem qua hướng dẫn MySQL để hiểu Cơ bản về MySQL.

Thí dụ

Sau đây là một ví dụ về kết nối với cơ sở dữ liệu MySQL "TESTDB" -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

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

Trong khi chạy tập lệnh này, nó tạo ra kết quả sau.

Database version : 5.5.20-log

Nếu kết nối được thiết lập với nguồn dữ liệu, thì một Đối tượng kết nối sẽ được trả về và lưu vào db để sử dụng thêm, nếu không dbđược đặt thành Không. Kế tiếp,db đối tượng được sử dụng để tạo ra một cursor, đến lượt nó, được sử dụng để thực thi các truy vấn SQL. Cuối cùng, trước khi xuất hiện, nó đảm bảo rằng kết nối cơ sở dữ liệu được đóng và tài nguyên được giải phóng.

Tạo bảng cơ sở dữ liệu

Khi kết nối cơ sở dữ liệu được thiết lập, chúng tôi sẵn sàng tạo bảng hoặc bản ghi vào bảng cơ sở dữ liệu bằng cách sử dụng execute phương thức của con trỏ đã tạo.

Thí dụ

Hãy để chúng tôi tạo một bảng Cơ sở dữ liệu NHÂN VIÊN -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 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 (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )"""

cursor.execute(sql)

# disconnect from server
db.close()

INSERT hoạt động

Thao tác INSERT là bắt buộc khi bạn muốn tạo bản ghi của mình vào một bảng cơ sở dữ liệu.

Thí dụ

Ví dụ sau, thực thi câu lệnh SQL INSERT để tạo bản ghi trong bảng EMPLOYEE -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 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', 'Mohan', 20, 'M', 2000)"""
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()

Ví dụ trên có thể được viết như sau để tạo các truy vấn SQL động:

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

# 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' )" % \
   ('Mac', 'Mohan', 20, 'M', 2000)
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()

Thí dụ

Đoạn mã sau là một dạng thực thi khác mà bạn có thể truyền trực tiếp các tham số:

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

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

ĐỌC hoạt động

ĐỌC Hoạt động trên bất kỳ cơ sở dữ liệu nào có nghĩa là lấy một số thông tin hữu ích từ cơ sở dữ liệu.

Khi kết nối cơ sở dữ liệu được thiết lập, bạn đã sẵn sàng thực hiện truy vấn vào cơ sở dữ liệu này. Bạn có thể sử dụngfetchone() phương pháp để tìm nạp một bản ghi hoặc fetchall() phương thức để tìm nạp nhiều giá trị từ một bảng cơ sở dữ liệu.

fetchone()- Nó tìm nạp hàng tiếp theo của tập kết quả truy vấn. Tập kết quả là một đối tượng được trả về khi một đối tượng con trỏ được sử dụng để truy vấn một bảng.
fetchall()- Nó tìm nạp tất cả các hàng trong một tập kết quả. Nếu một số hàng đã được trích xuất từ tập kết quả, thì nó sẽ truy xuất các hàng còn lại từ tập kết quả.
rowcount - Đây là một thuộc tính chỉ đọc và trả về số hàng bị ảnh hưởng bởi phương thức execute ().

Thí dụ

Quy trình sau đây truy vấn tất cả các bản ghi từ bảng EMPLOYEE có mức lương hơn 1000 -

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

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

# Prepare SQL query to INSERT a record into the database.
sql = "SELECT * FROM EMPLOYEE \
      WHERE INCOME > '%d'" % (1000)
try:
   # Execute the SQL command
   cursor.execute(sql)
   # Fetch all the rows in a list of lists.
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # Now print fetched result
      print ("fname = %s,lname = %s,age = %d,sex = %s,income = %d" % \
         (fname, lname, age, sex, income ))
except:
   print ("Error: unable to fetch data")

# disconnect from server
db.close()

Đầu ra

Điều này sẽ tạo ra kết quả sau:

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

Cập nhật hoạt động

CẬP NHẬT Hoạt động trên bất kỳ cơ sở dữ liệu nào có nghĩa là cập nhật một hoặc nhiều bản ghi đã có sẵn trong cơ sở dữ liệu.

Quy trình sau cập nhật tất cả các bản ghi có SEX là 'M'. Ở đây, chúng tôi tăng TUỔI của tất cả nam giới thêm một năm.

Thí dụ

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

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

Thao tác DELETE

Thao tác DELETE là bắt buộc khi bạn muốn xóa một số bản ghi khỏi cơ sở dữ liệu của mình. Sau đây là quy trình để xóa tất cả các bản ghi khỏi NHÂN VIÊN có TUỔI trên 20 -

Thí dụ

#!/usr/bin/python3

import pymysql

# Open database connection
db = pymysql.connect("localhost","testuser","test123","TESTDB" )

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

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

# disconnect from server
db.close()

Thực hiện giao dịch

Giao dịch là một cơ chế đảm bảo tính nhất quán của dữ liệu. Các giao dịch có bốn thuộc tính sau:

Atomicity - Giao dịch hoàn tất hoặc không có gì xảy ra.
Consistency - Một giao dịch phải bắt đầu ở trạng thái nhất quán và để hệ thống ở trạng thái nhất quán.
Isolation - Kết quả trung gian của một giao dịch không được hiển thị bên ngoài giao dịch hiện tại.
Durability - Sau khi giao dịch được cam kết, các tác động sẽ tồn tại dai dẳng, ngay cả sau khi hệ thống bị lỗi.

Python DB API 2.0 cung cấp hai phương thức để cam kết hoặc khôi phục một giao dịch.

Thí dụ

Bạn đã biết cách thực hiện các giao dịch. Đây là một ví dụ tương tự -

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

Hoạt động CAM KẾT

Cam kết là một hoạt động đưa ra tín hiệu xanh cho cơ sở dữ liệu để hoàn tất các thay đổi và sau thao tác này, không thể hoàn nguyên thay đổi nào.

Đây là một ví dụ đơn giản để gọi commit phương pháp.

db.commit()

Hoạt động ROLLBACK

Nếu bạn không hài lòng với một hoặc nhiều thay đổi và bạn muốn hoàn nguyên những thay đổi đó hoàn toàn, thì hãy sử dụng rollback() phương pháp.

Đây là một ví dụ đơn giản để gọi rollback() phương pháp.

db.rollback()

Ngắt kết nối cơ sở dữ liệu

Để ngắt kết nối Cơ sở dữ liệu, hãy sử dụng phương thức close ().

db.close()

Nếu người dùng đóng kết nối đến cơ sở dữ liệu bằng phương thức close (), thì bất kỳ giao dịch nào chưa hoàn thành sẽ được DB khôi phục. Tuy nhiên, thay vì phụ thuộc vào bất kỳ chi tiết triển khai cấp thấp hơn nào của DB, ứng dụng của bạn tốt hơn nên gọi cam kết hoặc khôi phục một cách rõ ràng.

Xử lý lỗi

Có nhiều nguồn sai sót. Một vài ví dụ là lỗi cú pháp trong câu lệnh SQL được thực thi, lỗi kết nối hoặc gọi phương thức tìm nạp cho một xử lý câu lệnh đã bị hủy hoặc đã hoàn thành.

API DB xác định một số lỗi phải tồn tại trong mỗi mô-đun cơ sở dữ liệu. Bảng sau liệt kê các ngoại lệ này.

Sr.No.	Ngoại lệ & Mô tả
1	Warning Được sử dụng cho các vấn đề không nghiêm trọng. Phải phân lớp StandardError.
2	Error Lớp cơ sở cho các lỗi. Phải phân lớp StandardError.
3	InterfaceError Được sử dụng cho các lỗi trong mô-đun cơ sở dữ liệu, không phải chính cơ sở dữ liệu. Phải phân lớp lỗi.
4	DatabaseError Được sử dụng cho các lỗi trong cơ sở dữ liệu. Phải phân lớp lỗi.
5	DataError Lớp con của DatabaseError đề cập đến lỗi trong dữ liệu.
6	OperationalError Lớp con của DatabaseError đề cập đến các lỗi như mất kết nối với cơ sở dữ liệu. Những lỗi này thường nằm ngoài tầm kiểm soát của trình mã Python.
7	IntegrityError Lớp con của DatabaseError đối với các tình huống có thể làm hỏng tính toàn vẹn của quan hệ, chẳng hạn như các ràng buộc về tính duy nhất hoặc khóa ngoại.
số 8	InternalError Lớp con của DatabaseError đề cập đến các lỗi bên trong mô-đun cơ sở dữ liệu, chẳng hạn như con trỏ không còn hoạt động.
9	ProgrammingError Lớp con của DatabaseError đề cập đến các lỗi như tên bảng không hợp lệ và những thứ khác có thể đổ lỗi cho bạn một cách an toàn.
10	NotSupportedError Lớp con của DatabaseError đề cập đến việc cố gắng gọi chức năng không được hỗ trợ.

Các tập lệnh Python của bạn phải xử lý các lỗi này, nhưng trước khi sử dụng bất kỳ ngoại lệ nào ở trên, hãy đảm bảo MySQLdb của bạn có hỗ trợ cho ngoại lệ đó. Bạn có thể biết thêm thông tin về chúng bằng cách đọc thông số kỹ thuật của DB API 2.0.