Xử lý MySQL trong Python (insert / update / delete / select)

Trong bài này mình sẽ hướng dẫn cách dùng module mysql-connector-python để xử lý kết nối và truy vấn MySQL. Các thao tác trong MySQL cơ bản như kết nối / insert / update / delete và select.

Đây là module phổ biến nhất khi làm việc với MySQL trong Python. Và trước khi bắt đầu thì mình xin giới thiệu một chút về module này đã nhé.

1. MySQL Python Connector là gì?

Để xử lý dữ liệu trong MySQL từ Python thì bạn cần một database driver để kết nối, và MySQL Connector/Python là đối tượng kết nối chuẩn được MySQL cung cấp dành riêng cho Python.

Một số đặc điểm của module này:

Bài viết này được đăng tại [free tuts .net]

• Hỗ trợ hầu hết tất cả các tính năng được cung cấp bởi MySQL phiên bản 5.7. Nó cho phép bạn chuyển đổi giá trị tham số của bộ phân loại giữa các loại dữ liệu Python và MySQL.
• Được thiết kế dành riêng cho MySQL, nó hỗ trợ tất cả các phần mở rộng của MySQL như mệnh đề LIMIT.
• Cho phép bạn nén luồng dữ liệu giữa Python và máy chủ cơ sở dữ liệu MySQL bằng cách sử dụng protocol compression. Nó hỗ trợ các kết nối bằng cách sử dụng TCP / IP Socket và kết nối TCP / IP an toàn bằng SSL.
• Là một API được triển khai bằng Python thuần túy. Điều đó có nghĩa là bạn không cần cài đặt bất kỳ thư viện máy khách MySQL hoặc bất kỳ mô-đun Python nào ngoại trừ thư viện chuẩn.

Các phiên bản (version)

Bạn sẽ phải cài đặt các phiên bản chính xác của Python, MySQL và Connect / Python. Bảng sau đây minh họa các phiên bản tương thích của Connect / Python, MySQL và Python:

Cài đặt MySQL Connector/Python

Điều kiện cài đặt:

Trước khi cài đặt MySQL Connector/Python thì bạn phải cần làm những thứ sau:

• Bạn phải có quyền Root hoặc Administrator thì mới có thể cài đặt.
• Bạn phải cài đặt Python trên máy mình trước.

Lưu ý rằng quá trình cài đặt MySQL Connector / Python yêu cầu Python phải có trong system’s PATH.

Các bước cài đặt:

MySQL Python Connector có sẵn trên pypi.org, do đó, bạn có thể cài đặt nó bằng lệnh pip.

Lệnh pip cho phép bạn cài đặt trình kết nối Python Python trên bất kỳ Hệ điều hành nào, bao gồm Windows, macOS, Linux và Unix:

pip install mysql-connector-python

Kết quả:

Nếu bạn tìm thấy bất kỳ vấn đề nào trong khi cài đặt thì có thể chỉ rõ phiên bản chính xác cần cài đặt bằng lệnh sau.

pip install mysql-connector-python==8.0.17

Lưu ý rằng để gỡ cài đặt MySQL Connector / Python hiện tại thì bạn sử dụng lệnh sau:

pip uninstall mysql-connector-python

Một lệnh nhắc lại sẽ xuất hiện, nhập Y để đồng ý và N để hủy bỏ.

Proceed (y/n)? y

Kiểm tra cài đặt thành công chưa

Sau khi cài đặt MySQL Connector/Python bạn cần phải kiểm tra để đảm bảo rằng nó hoạt động chính xác, và bạn có thể kết nối với máy chủ cơ sở dữ liệu MySQL mà không gặp sự cố nào.

Để kiểm tra thì bạn thực hiện các bước sau:

Bước 1: Mở Python command line

Bước 2: Nhập đoạn code dưới đây vào.

>>> import mysql.connector
>>> mysql.connector.connect(host='localhost',database='mysql',user='root',password='your pass')

Nếu bạn thấy kết quả xuất hiện như dưới đây thì bạn đã cài đặt thành công, và MySQL đã được kết nối bởi Python Connector.

<mysql.connector.connection.MySQLConnection object at 0x0187AE50>

2. Tải database mẫu để học xử lý MySQL trong Python

Để các bạn dễ thực hành hơn thì mình sẽ gửi một database mẫu, đây là db mà ta sẽ thực hành trong suốt quá trình làm việc với MySQL trong Python.

Trước tiên hãy tải file python_mysql.zip này về, sau đó giải nén và đặt vào thư mục MySQL có dạng như sau: C:\mysql\python_mysql.sql.

Tiếp theo hãy đăng nhập vào MySQL bằng cách sử dụng mysql tool để chạy lệnh sau:

mysql -u root -p

Nhập mật khẩu cho root user.

Enter password: ********

Hãy tạo một database có tên là python_mysql.

mysql>create database python_mysql;

Sau đó chọn db python_mysql vừa tạo.

mysql>use python_mysql;

Cuối cùng hãy dùng lệnh sau để load database mà bạn đã tải về.

mysql> source c:\mysql\python_mysql.sql

Hãy sử dụng lệnh sau để kiểm tra xem các table đã xuất hiện chưa nhé.

mysql> SHOW TABLES;

Kết quả:

+------------------------+
| Tables_in_python_mysql |
+------------------------+
| authors                |
| book_author            |
| books                  |
+------------------------+
3 rows in set (0.01 sec)

* Lưu ý: Thực ra vẫn còn một cách khác là bạn hãy sử dụng công cụ PHPMyAdmin nhé, đây là tool quản lý chuẩn của MySQL và bạn có thể thực hiện bằng thao tác giao diện rất trực quan.

3. Kết nối MySQL với Python

Để kết nối vào một hệ quản trị CSDL bất kì thì ta cần một chuỗi kết nối gồm các thông tin để đăng nhập như: host, database, username, password.

Lớp MySQLConnection yêu cầu chuỗi kết nối phải là một dict có cấu trúc như sau:

db_config = {
    'host': 'localhost',
    'database': 'db_name',
    'user': 'user_name',
    'password': 'user_pass'
}

Trong đó:

• db_name là tên của database mà chúng ta đã tạo
• user_name là tên tài khoản đăng nhập vào database
• user_pass là mật khẩu đăng nhập của user

Bây giờ chúng ta sẽ viết một hàm kết nối và trả về đối tượng connection nhé.

# Vị trí import package
from mysql.connector import MySQLConnection, Error

# Hàm kết nối
def connect():
    """ Kết nối MySQL bằng module MySQLConnection """
    db_config = {
        'host': 'localhost',
        'database': 'python_mysql',
        'user': 'root',
        'password': ''
    }

    # Biến lưu trữ kết nối
    conn = None

    try:
        conn = MySQLConnection(**db_config)

        if conn.is_connected():
            return conn

    except Error as error:
        print(error)

    return conn

# Test thử
conn = connect()
print(conn)

Chạy test thử nếu kết quả có dạng như sau là ta đã kết nối thành công:

<mysql.connector.connection.MySQLConnection object at 0x000001D3F3AB2670>

Những thao tác tiếp theo ta sẽ sử dụng kết quả return của hàm connect() để xử lý.

4. Lấy dữ liệu MySQL trong Python

Để lấy dữ liệu của một bảng hoặc nhiều bảng thì ta chạy lệnh select, và sau đây là hàm xử lý.

Cách 1: Dùng phương thức fetchone()

Phương thức fetchone() sẽ trả về row dữ liệu tiếp theo trong kết quả trả về, hoặc trả về None nếu kết quả là rỗng.

# Hàm hiển thị danh sách books
def show_books():
    try:
        conn = connect()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM books")

        row = cursor.fetchone()

        while row is not None:
            print(row)
            row = cursor.fetchone()

    except Error as e:
        print(e)

    finally:
        # Đóng kết nối
        cursor.close()
        conn.close()

Cách sử dụng:

show_books()

Kết quả:

Cách 2: Dùng phương thức fetchall()

Trong trường hợp dữ liệu trả về nhỏ thi ta có thể sử dụng phương thức fetchall(), nó sẽ trả về tất cả dữ liệu mà câu truy vấn thực hiện được.

def show_books():
    try:
        conn = connect()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM books")

        rows = cursor.fetchall()

        print('Total Row(s):', cursor.rowcount)
        for row in rows:
            print(row)

    except Error as e:
        print(e)

    finally:
        # Đóng kết nối
        cursor.close()
        conn.close()

Giải thích tương tự như ví dụ với phương thức fetchone(), ngoại trừ phần gọi phương thức fetchall (). Vì chúng ta đã nạp tất cả các rows từ bảng books vào bộ nhớ nên có thể lấy được tổng số hàng được trả về bằng cách sử dụng thuộc tính rowcount của đối tượng cursor.

* Lưu ý: Với dữ liệu lớn thì ta không nên sử dụng phương thức này bởi nó sẽ tốn nhiều tài nguyên để lưu trữ data trả về, trường hợp xấu nhất là làm ứng dụng bị chết.

Cách 3: Dùng phương thức fetchmany()

Đối với một bảng tương đối lớn thì cần có thời gian để tìm nạp tất cả các hàng và trả về toàn bộ tập kết quả. Ngoài ra, fetchall() cần phân bổ đủ bộ nhớ để lưu trữ toàn bộ tập kết quả trong bộ nhớ, điều này không hiệu quả.

MySQL Connector / Python có phương thức fetchmany() trả về số hàng (n) tiếp theo của tập kết quả, cho phép bạn cân bằng giữa thời gian truy xuất và không gian bộ nhớ.

Đầu tiên hãy xây dựng một hàm có công dụng tra về số dòng dữ liệu như tham số truyền vào.

def iter_row(cursor, size=10):
    while True:
        rows = cursor.fetchmany(size)
        if not rows:
            break
        for row in rows:
            yield row

Trong hàm này mình sử dụng yield để trả kết quả về ở dạng iterator.

Tiếp theo ta có thể sử dụng ham fiter_row để lấy mỗi lần 10 rows cho mỗi lần fetch dữ liệu.

def show_books():
    try:
        conn = connect()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM books")

        for row in iter_row(cursor, 10):
            print(row)

    except Error as e:
        print(e)

    finally:
        # Đóng kết nối
        cursor.close()
        conn.close()

Quá đơn giản phải không các bạn? Thực tế thì fetchmany() chính là một hàm nâng cấp của fetchall() vì thực tế nó hữu hiệu hơn nhiều.

5. Insert dữ liệu vào MySQL trong Python

Ta chỉ cần thay đổi lệnh select thành lệnh insert là được.

Tuy nhiên chúng ta không sử dụng vòng lặp, tại vì kết quả trả về của lệnh insert không phải là danh sách. Thay vào đó ta sẽ check xem lastrowid la bao nhiêu, nếu có tức là insert thành công, ngược lại là insert thất bại.

def insert_book(title, isbn):
    query = "INSERT INTO books(title,isbn) " \
            "VALUES(%s,%s)"
    args = (title, isbn)

    try:

        conn = connect()

        cursor = conn.cursor()
        cursor.execute(query, args)

        if cursor.lastrowid:
            print('ID insert là:', cursor.lastrowid)
        else:
            print('Insert thất bại')

        conn.commit()
    except Error as error:
        print(error)

    finally:
        # Đóng kết nối
        cursor.close()
        conn.close()

Cách dùng:

insert_book("Tiêu đề sách", "12312321321")

Mình chạy lệnh này với database mẫu thì thu được kết quả là:

ID insert là: 84

6. Cập nhật dữ liệu MySQL bằng Python

Cập nhật dữ liệu thì ta chạy lệnh update trong MySQL, kết hợp với đối tượng connection để xử lý.

def update_book(book_id, title):
    # Câu lệnh update dữ liệu
    query = """ UPDATE books
                SET title = %s
                WHERE id = %s """

    data = (title, book_id)

    try:
        # Kết nối database
        conn = connect()

        # Cập nhật tiêu đề
        cursor = conn.cursor()
        cursor.execute(query, data)

        # Chấp nhận sự thay đổi
        conn.commit()

    except Error as error:
        print(error)

    finally:
        # Đóng kết nối
        cursor.close()
        conn.close()

Cách dùng:

update_book(1, "Tên sách mới")

7. Xóa dữ liệu MySQL bằng Python

Để xóa dữ liệu thì ta sẽ chạy lệnh delete trong MySQL.

def delete_book(book_id):
    query = "DELETE FROM books WHERE id = %s"

    try:
        conn = connect()

        # Thực thi câu truy vấn
        cursor = conn.cursor()
        cursor.execute(query, (book_id,))

        # Chấp nhận sự thay đổi
        conn.commit()

    except Error as error:
        print(error)

    finally:
        # Đóng kết nối
        cursor.close()
        conn.close()

Cách dùng:

delete_book(1)

Lời kết: Trên là bài hướng dẫn cách xử lý database MySQL bằng Python. Qua bài này bạn đã học được những kiến thức như sau:

• Cách cài đặt module mysql-connector-python
• Cách kết nối PyThon với MySQL
• Cách thực hiện các thao tác thêm / xóa / sửa / hiển thị danh sách dữ liệu MySQL bằng Python.

Chúc bạn học tốt nhé, hẹn gặp lại bài tiếp theo.