Cộng đồng chia sẻ tri thức Lib24.vn

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

Gửi bởi: Phạm Thọ Thái Dương 20 tháng 2 2020 lúc 10:42:56


Mục lục
* * * * *

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 tuân thủ 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 bạn. API cơ sở dữ liệu Python hỗ trợ một loạt các máy chủ cơ sở dữ liệu như -

  1. Tiện ích
  2. mQuery
  3. MySQL
  4. PostgreSQL
  5. Máy chủ Microsoft SQL 2000
  6. Thông tin
  7. Trung tâm
  8. Oracle
  9. Sybase

Dưới đây là danh sách các giao diện cơ sở dữ liệu Python có sẵn: Giao diện cơ sở dữ liệu Python và API . Bạn phải tải xuống một mô-đun DB DB riêng cho mỗi cơ sở dữ liệu 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ả hai 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 bằng cách sử dụng các cấu trúc và cú pháp Python bất cứ khi nào có thể. API này bao gồm các mục sau -

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

Chúng ta sẽ tìm hiểu tất cả các khái niệm bằng MySQL, vì vậy chúng ta hãy nói về mô-đun MySQLdb.

MySQLdb là gì?

MySQLdb 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à được xây dựng dựa trên API C của MySQL.

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

Trước khi tiếp tục, bạn đảm bảo rằng bạn đã cài đặt MySQLdb 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/python

import MySQLdb

Nếu nó tạo ra kết quả như 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 MySQLdb
ImportError: No module named MySQLdb

Để cài đặt mô-đun MySQLdb, sử dụng lệnh sau -

For Ubuntu, use the following command -
$ sudo apt-get install python-pip python-dev libmysqlclient-dev
For Fedora, use the following command -
$ sudo dnf install python python-devel mysql-devel redhat-rpm-config gcc
For Python command prompt, use the following command -
pip install MySQL-python

Lưu ý - Đảm bảo 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 nội dung sau -

  1. Bạn đã tạo một cơ sở dữ liệu TESTDB.
  2. Bạn đã tạo một bảng NHÂN VIÊN trong TESTDB.
  3. Bảng này có các trường FIRST_NAME, LAST_NAME, AGE, SEX và THU NHẬP.
  4. ID người dùng "người kiểm tra" và mật khẩu "test123" được đặt để truy cập TESTDB.
  5. Mô-đun Python MySQLdb được cài đặt đúng trên máy của bạn.
  6. Bạn đã trải qua hướng dẫn MySQL để hiểu cơ bản về MySQL.

Thí dụ

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

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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ó đang tạo ra kết quả sau trong máy Linux của tôi.

Database version : 5.0.45

Nếu một kết nối được thiết lập với nguồn dữ liệu, thì Đối tượng kết nối được trả về và lưu vào db để sử dụng tiếp, nếu không db được đặt thành Không có. Tiếp theo, đối tượng db được sử dụng để tạo một đối tượng con trỏ , lần lượt được sử dụng để thực hiện các truy vấn SQL. Cuối cùng, trước khi ra mắt, nó đảm bảo rằng kết nối cơ sở dữ liệu được đóng lại 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 các bảng hoặc bản ghi vào các bảng cơ sở dữ liệu bằng phương thức thực hiện của con trỏ đã tạo.

Thí dụ

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

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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()

Hoạt động INSERT

Nó được yêu cầu khi bạn muốn tạo hồ sơ của bạn 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 vào bảng NHÂN VIÊN -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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 một cách linh hoạt -

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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 hình thức thực thi khác, nơi bạn có thể truyền tham số trực tiếp -

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

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

Thao tác đọc

Hoạt động READ 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ủa chúng tôi đượ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ụng phương thức fetchone () để tìm nạp bản ghi đơn hoặc phương thức fetchall () để tìm nhiều giá trị từ bảng cơ sở dữ liệu.

  1. 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 sử dụng đối tượng con trỏ để truy vấn bảng.
  2. fetchall () - Nó tìm nạp tất cả các hàng trong 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ẽ lấy các hàng còn lại từ tập kết quả.
  3. rowcount - Đây là thuộc tính chỉ đọc và trả về số lượng hàng bị ảnh hưởng bởi phương thức exec ().

Thí dụ

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

#!/usr/bin/python

import MySQLdb

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

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

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 fecth data"

# disconnect from server
db.close()

Đ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 đây cập nhật tất cả các bản ghi có SEX là 'M' . Ở đây, chúng tôi tăng AGE của tất cả nam giới thêm một năm.

Thí dụ

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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 XÓA

Thao tác XÓA được yêu cầu khi bạn muốn xóa một số bản ghi khỏi cơ sở dữ liệu của bạn. Sau đây là quy trình xóa tất cả các hồ sơ khỏi NHÂN VIÊN trong đó AGE lớn hơn 20 -

Thí dụ

#!/usr/bin/python

import MySQLdb

# Open database connection
db = MySQLdb.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. Giao dịch có bốn thuộc tính sau -

  1. Tính nguyên tử - Một giao dịch hoàn thành hoặc không có gì xảy ra cả.
  2. Tính nhất quán - Một giao dịch phải bắt đầu ở trạng thái nhất quán và rời khỏi hệ thống ở trạng thái nhất quán.
  3. Cách ly - Kết quả trung gian của giao dịch không hiển thị bên ngoài giao dịch hiện tại.
  4. Độ bền - Một khi giao dịch được thực hiện, các hiệu ứng vẫn tồn tại, ngay cả sau khi lỗi hệ thống.

Python DB API 2.0 cung cấp hai phương thức để cam kết hoặc khôi phục 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()

CAM KẾT hoạt động

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

Dưới đây là một ví dụ đơn giản để gọi phương thức commit .

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 lại những thay đổi đó, thì hãy sử dụng phương thức rollback () .

Dưới đây là một ví dụ đơn giản để gọi phương thức rollback () .

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 phương thức kết nối với cơ sở dữ liệu bằng phương thức close (), mọi giao dịch chưa xử lý sẽ được DB quay lại. Tuy nhiên, thay vì phụ thuộc vào bất kỳ chi tiết triển khai DB nào ở cấp thấp hơn, ứng dụng của bạn sẽ tốt hơn khi gọi cam kết hoặc khôi phục rõ ràng.

Xử lý lỗi

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

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ê những ngoại lệ này.

Các tập lệnh Python của bạn sẽ xử lý các lỗi này, nhưng trước khi sử dụng bất kỳ trường hợp 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ể nhận thêm thông tin về chúng bằng cách đọc đặc tả DB API 2.0.


Được cập nhật: hôm kia lúc 18:17:48 | Lượt xem: 1090