Python – Thư viện PyMySQL

Bài hướng dẫn này chỉ ra cách lập trình MySQL bằng Python với thư viện PyMySQL.

PyMySQL

PyMySQL là thư viện MySQL client được tích hợp trong Python. Hầu hết các API tương thích với mysqlclient và MySQLdb.
yMySQL làm việc với MySQL 5.5+ và MariaDB 5.5+.

MySQL là một hệ quản trị cơ sở dữ liệu mã nguồn mở hàng đầu, là một hệ quản trị cơ sở dữ liệu đa người dùng, đa luồng. MySQL đặc biệt phổ biến trên web.

Cài đặt PyMySQL

Trong folder cài Python, tích hợp module pip. Trên Windows, bạn chạy command pip để cài đặt PyMySQL như sau

C:\Programs\Python\Python38-32\Scripts>pip install pymysql
Collecting pymysql
  Downloading PyMySQL-0.10.0-py2.py3-none-any.whl (47 kB)
     |████████████████████████████████| 47 kB 227 kB/s
Installing collected packages: pymysql
Successfully installed pymysql-0.10.0

Ví dụ: PyMySQL version

Trong ví dụ này, chúng ta truy vấn version MySQL. Để thực hiện, chúng ta cần làm 2 bước
– Tạo database ‘testdb’ với username ‘user3’ và password ‘1qazse4’
– Viết script Python truy vấn version MySQL

Tạo database trên MySQL

0. Download và install MySQL

1. Tạo database với tên ‘testdb’
$ mysql> CREATE DATABASE testdb;

2. Tạo user ‘user3’ với password là ‘1qazse4’
$ mysql> CREATE USER ‘testdb’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘1qazse4’;

3. Grant quyền read/write cho user3
$ mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, INDEX, DROP, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES ON testdb.* TO ‘user3’@’localhost’;

Script truy vấn version MySQL

version.py

import pymysql

con = pymysql.connect('localhost', 'user3',
    '1qazse4', 'testdb')

try:

    with con.cursor() as cur:

        cur.execute('SELECT VERSION()')

        version = cur.fetchone()

        print(f'Database version: {version[0]}')

finally:

    con.close()

Trong MySQL, chúng ta sử dụng SELECT VERSION() để lấy version của MySQL

import pymysql

Import thư viện pymysql

con = pymysql.connect('localhost', 'user3',
    '1qazse4', 'testdb')

Chúng ta kết nối database ‘testdb’ với hàm pymysql.connect() với 4 tham số
– hostname: ‘localhost’
– MySQL username: ‘user3’
– password: ‘1qazse4’
– database name: testdb

with con.cursor() as cur:

Với việc sử dụng keyword with, trình thông dịch Python giải phóng resource. Nó cũng cung cấp cơ chế xử lí error. Chúng ta lấy con trỏ object ‘cur’, được sử dụng để truy xuất bản ghi.

cur.execute('SELECT VERSION()')

Chúng ta gọi hàm execute thông qua con trỏ object ‘cur’ và thực thi câu lệnh SQL.

version = cur.fetchone() 

Hàm fetchone() lấy hàng kế tiếp trong set kết quả truy vấn database, trả về giá trị hàng đó, hoặc không có giá trị khi không có data.

print(f'Database version: {version[0]}')

In version MySQL

finally:

    con.close()

Module pymysql không implement việc tự động xử lý việc giải phóng resource, chúng ta cần phải close kết nối database bằng hàm close().
Kết quả:

C:\Programs\Python\Python38-32>python.exe version.py
Database version: 8.0.21

Ví dụ: PyMySQL fetchAll

Phương thức fetchAll truy xuất tất cả các hàng của một kết quả truy vấn, trả về chúng dưới dạng mảng các chuỗi.
Chúng ta cần làm 2 bước:
– Tạo table city trong database ‘testdb’ được tạo ở trên, gồm 3 trường: no (số thứ tự), city (tên thành phố), area (diện tích) trên MySQL Server
– Viết script Python truy vấn MySQL Server và hiển thị ra màn hình console

Tạo table trên MySQL

1. Để tạo table, các bạn chạy command sau
mysql> CREATE TABLE city (no INT, city VARCHAR(20), area FLOAT);

2. Hiển thị các trường trong table city
mysql> describe city;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int         | YES  |     | NULL    |       |
| city  | varchar(20) | YES  |     | NULL    |       |
| area  | float       | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

3. Import data vào table city
mysql> INSERT INTO city VALUES (‘1′,’An Giang’,’3536.5′);
mysql> INSERT INTO city VALUES (‘2′,’Bac Lieu’,’2669.3′);
mysql> INSERT INTO city VALUES (‘3′,’Bac Giang’,’3851.7′);

Truy vấn data bằng PyMySQL

fetch_all.py

import pymysql

con = pymysql.connect('localhost', 'user3',
    '1qazse4', 'testdb')

try:

    with con.cursor() as cur:

        cur.execute('SELECT * FROM city')

        rows = cur.fetchall()

        for row in rows:
            print(f'{row[0]} {row[1]} {row[2]}')

finally:

    con.close()

Trong ví dụ, truy vấn tất cả các data từ table city

cur.execute('SELECT * FROM city')

Câu lệnh lựa chọn dữ liệu từ table city

rows = cur.fetchall()

Hàm fetchall lấy tất cả các record và trả về kế quả. Kết quả truy vấn được lưu vào mảng tuple. Mỗi tuple lưu record 1 hàng trong table.

for row in rows:
    print(f'{row[0]} {row[1]} {row[2]}')

Chúng ta in dữ liệu ra màn hình console
Kết quả chạy script

C:\Programs\Python\Python38-32>python.exe fetch_all.py
1 An Giang 3536.5
2 Bac Lieu 2669.3
3 Bac Giang 3851.7

PyMySQL – Con trỏ dictionary

Con trỏ mặc định trả về dữ liệu trong mảng tuple. Khi chúng ta sử dụng con trỏ dictionary, dữ liệu trả về lưu trong dictionary. Với các lưu trữ dữ liệu trong dictionary, chúng ta có thể tham chiếu dữ liệu bằng tên column.
Note: Trong ví dụ này, chúng ta vẫn sử dụng database ‘testdb’ và table ‘city’ trong ví dụ trên.
dictionary_cursor.py

import pymysql
import pymysql.cursors

con = pymysql.connect(host='localhost',
        user='user3',
        password='1qazse4',
        db='testdb',
        charset='utf8mb4',
        cursorclass=pymysql.cursors.DictCursor)

try:

    with con.cursor() as cur:

        cur.execute('SELECT * FROM city')

        rows = cur.fetchall()

        for row in rows:
            print(row['no'], row['city'])

finally:

    con.close()

Trong ví dụ này, chúng ta truy vấn hàng đầu tiên trong table ‘city’ bằng việc sử dụng con trỏ dictionary trong Python.

con = pymysql.connect(host='localhost',
    user='user3',
    password='1qazse4',
    db='testdb',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor)

Chúng ta truyền thêm giá trị pymysql.cursors.DictCursor cho tham số cursorclass.

for row in rows:
    print(row['no'], row['city'])

Truy vấn và in data ở 2 cột no(số thứ tự) và city (tên thành phố)
Kết quả chỉ có dữ liệu 2 cột no và city được in ra

C:\Programs\Python\Python38-32>python.exe dictionary_cursor.py
1 An Giang
2 Bac Lieu
3 Bac Giang

PyMySQL – Hiển thị header

Trong ví dụ này minh họa cách hiển hiện header no (số thứ tự), city (tên thành phố) và area (diện tích) cùng với dữ liệu trong table ‘city’
Note: Trong ví dụ này, chúng ta vẫn sử dụng database ‘testdb’ và table ‘city’ trong ví dụ trên.

column_headers.py

import pymysql

con = pymysql.connect('localhost', 'user3',
    '1qazse4', 'testdb')

try:

    with con.cursor() as cur:

        cur.execute('SELECT * FROM city')

        rows = cur.fetchall()

        desc = cur.description

        print(f'{desc[0][0]:<8} {desc[1][0]:<15} {desc[2][0]:>10}')

        for row in rows:
            print(f'{row[0]:<8} {row[1]:<15} {row[2]:>10}')

finally:

    con.close()

Tên cột trong table được gọi là metadata, được lấy từ con trỏ object ‘cur’

desc = cur.description

Thuộc tính description của con trỏ ‘cur’ trả về thông tin các column trong table.

print(f'{desc[0][0]:<8} {desc[1][0]:<15} {desc[2][0]:>10}')

In tên column theo định dạng sử dụng f-string.

for row in rows:
    print(f'{row[0]:<8} {row[1]:<15} {row[2]:>10}')

Duyệt dữ liệu từng hàng trong table và in ra màn hình console
Kết quả như sau:

C:\Programs\Python\Python38-32>python.exe column_headers.py
no       city                  area
1        An Giang            3536.5
2        Bac Lieu            2669.3
3        Bac Giang           3851.7

PyMySQL – Truy vấn database theo giá trị

Ở các ví dụ trên, chúng ta truy vấn table trong database, không đảm bảo security và performance. Ví dụ này minh họa cách truy vấn hàng dữ liệu trong table dựa vào giá trị truyền vào.
Note: Trong ví dụ này, chúng ta vẫn sử dụng database ‘testdb’ và table ‘city’ trong ví dụ trên.

prepared.py

import pymysql

con = pymysql.connect('localhost', 'user3', 
    '1qazse4', 'testdb')

# user input
no = 3

try: 

    with con.cursor() as cur:

            
        cur.execute('SELECT * FROM city WHERE no=%s', no) 
        
        no, city, area  = cur.fetchone()
        print(no, city, area)

finally:

    con.close()

Trong ví dụ này, chúng ta truy vấn row dữ liệu với ‘no’=3

cur.execute('SELECT * FROM city WHERE no=%s', no)

Câu lệnh thực hiện truy vấn row dữ liệu có ‘no’=3
Kết quả:

C:\Programs\Python\Python38-32>python.exe prepared.py
3 Bac Giang 3851.7

PyMySQL – Đếm cố lượng hàng

Thuộc tính cur.rowcount chỉ ra số lượng hàng trong table được tạo ra bằng command SQL: SELECT, UPDATE, hoặc INSERT
Note: Trong ví dụ này, chúng ta vẫn sử dụng database ‘testdb’ và table ‘city’ trong ví dụ trên.
affected_rows.py

import pymysql

con = pymysql.connect('localhost', 'user3',
   '1qazse4', 'testdb')

try:

    with con.cursor() as cur:

        cur.execute('SELECT * FROM city WHERE no IN (1, 2, 3)')

        print(f'The query affected {cur.rowcount} rows')

finally:

    con.close()

Trong ví dụ truy vấn 3 hàng 1,2,3
Kết quả:

C:\Programs\Python\Python38-32>python.exe affected_rows.py
The query affected 3 rows

PyMySQL – Chèn row table

Hàng mới được chèn vào table database bằng lệnh INSERT INTO
Note: Trong ví dụ này, chúng ta vẫn sử dụng database ‘testdb’ và table ‘city’ trong ví dụ trên.

insert_row.py

import pymysql

con = pymysql.connect('localhost', 'user3', 
    '1qazse4', 'testdb')

city = (4, 'Thai Nguyen', 3536.4)

try: 

    with con.cursor() as cur:

        cur.execute('INSERT INTO city VALUES(%s, %s, %s)', 
            (city[0], city[1], city[2])) 
        con.commit()

        print('new city inserted')

finally:

    con.close()

Ví dụ minh họa cách chèn hàng dữ liệu mới (4, ‘Thai Nguyen’, 3536.4) vào table database

cur.execute('INSERT INTO city VALUES(%s, %s, %s)', 
    (city[0], city[1], city[2])) 
con.commit()

Trong pymysql, mặc định tính năng autocomit bị disable. Do vậy, cần gọi hàm commit() để thực thi việc thay đổi.
Kết quả:

C:\Programs\Python\Python38-32>python.exe insert_row.py
new city inserted

Các bạn chạy lại script ở trên để in ra dữ liệu table ‘city’ gồm 4 hàng

1 An Giang 3536.5
2 Bac Lieu 2669.3
3 Bac Giang 3851.7
4 Thai Nguyen 3536.4