Bài 48: Tương tác Python với Cơ sở dữ liệu MySQL Server -Phần 1

Ở các bài học trước, các bạn đã biết cách cài đặt và sử dụng MySQL Server, MySQL WorkBench. Biết cách tạo Cơ sở dữ liệu bảng, thuộc tính, các thao tác trong bảng dữ liệu như Xem, thêm, sửa, xóa, sắp xếp… ngay trong công cụ MySQL Workbench.

Bài học này Chúng ta bắt đầu sử dụng ngôn ngữ lập trình Python để tương tác cơ sở dữ liệu MySQL Server “studentmanagement” đã thực hiện ở bài trước. Việc tương tác này có nhiều cấp độ, bài học này ta sẽ nghiên cứu các thư viện và mã lệnh để kết nối và tương tác dữ liệu trực tiếp bằng Python, cũng như phân trang dữ liệu. Các bài sau chúng ta sẽ nghiên cứu sâu hơn, chẳng hạn như mô hình hóa hướng đối tượng, cấu trúc các mã lệnh thành các thư viện để tối ưu hóa hệ thống phần mềm. Trong bài này, Tui hướng dẫn lập trình các chức năng sau:

(1) Lập trình Python kết nối MySQL Server

(2) Lập trình Python truy vấn dữ liệu MySQL Server

(3) Lập trình Python thêm mới dữ liệu MySQL Server

(4) Lập trình Python cập nhật dữ liệu MySQL Server

(5) Lập trình Python xóa dữ liệu MySQL Server

Trước tiên, chúng ta cần cài đặt thư viện kết nối và tương tác MySQL Server, có nhiều thư viện nhưng ở đây chúng ta thống nhất sử dụng thư viện mysql.connector và thư viện này sẽ được sử dụng xuyên suốt các bài học còn lại, cũng như ứng dụng trong thống kê và máy học.

Ta sử dụng lệnh sau để cài đặt:

python -m pip install mysql-connector-python

(1) Lập trình Python kết nối MySQL Server

Nếu bạn nào chưa có dữ liệu “studentmanagement” có thể dùng chức năng Import/Export được học ở bài trước, để import dữ liệu:

https://tranduythanh.com/datasets/studentmanagement.rar

Các thông số server, port, database, user, password… ta đã cài đặt ở các bài học trước, nên bài này ta sử dụng. Tùy vào máy tính của bạn lúc cài đặt phần mềm như thế nào thì khai báo các thông số cho chính xác. Tui đã chủ ý khai báo các biến riêng biệt để bạn chỉ cần thay thế giá trị theo máy tính mà mình đã cài đặt MySQL Server. Để kết nối tới cơ sở dữ liệu MySQL ta tạo file “TestQueryMySQL.py” và viết mã lệnh như sau:

import mysql.connector

server="localhost"
port=3306
database="studentmanagement"
username="root"
password="@Obama123"

conn = mysql.connector.connect(
                host=server,
                port=port,
                database=database,
                user=username,
                password=password)

Lệnh connect() ở trên trả về một MySQLConnection. Nếu kết nối thất bại chương trình sẽ thông báo lỗi. Trong giới hạn của bài học này, để cho nó đơn giản, dễ hiểu thì Tui bỏ qua hết các handle exception, tập trung vào các mã lệnh cốt lõi mà ở đó ta đã kết nối tới MySQL Server thành công. Các bài học sau Tui sẽ bổ sung thêm các checked erros này.

Bây giờ ta sử dụng đối tượng conn ở trên để thực hiện các tương tác CRUD dưới đây:

(2) Lập trình Python truy vấn dữ liệu MySQL Server

(2.1) Truy vấn toàn bộ Sinh viên:

cursor = conn.cursor()

sql="select * from student"
cursor.execute(sql)

dataset=cursor.fetchall()
align='{0:<3} {1:<6} {2:<15} {3:<10}'
print(align.format('ID', 'Code','Name',"Age"))
for item in dataset:
    id=item[0]
    code=item[1]
    name=item[2]
    age=item[3]
    avatar=item[4]
    intro=item[5]
    print(align.format(id,code,name,age))

cursor.close()

Thực thi lệnh trên ta có kết quả:

(2.2) Truy vấn các Sinh viên có độ tuổi từ 22 tới 26:

cursor = conn.cursor()
sql="SELECT * FROM student where Age>=22 and Age<=26"
cursor.execute(sql)

dataset=cursor.fetchall()
align='{0:<3} {1:<6} {2:<15} {3:<10}'
print(align.format('ID', 'Code','Name',"Age"))
for item in dataset:
    id=item[0]
    code=item[1]
    name=item[2]
    age=item[3]
    avatar=item[4]
    intro=item[5]
    print(align.format(id,code,name,age))

cursor.close()

Thực thi lệnh trên ta có kết quả:

(2.3) Truy vấn toàn bộ sinh viên và sắp xếp theo tuổi tăng dần:

cursor = conn.cursor()
sql="SELECT * FROM student " \
    "order by Age asc"
cursor.execute(sql)

dataset=cursor.fetchall()
align='{0:<3} {1:<6} {2:<15} {3:<10}'
print(align.format('ID', 'Code','Name',"Age"))
for item in dataset:
    id=item[0]
    code=item[1]
    name=item[2]
    age=item[3]
    avatar=item[4]
    intro=item[5]
    print(align.format(id,code,name,age))

cursor.close()

Thực thi lệnh trên ta có kết quả:

(2.4) Truy vấn các Sinh viên có độ tuổi từ 22 tới 26 và sắp xếp theo tuổi giảm dần:

cursor = conn.cursor()
sql="SELECT * FROM student " \
    "where Age>=22 and Age<=26 " \
    "order by Age desc "
cursor.execute(sql)

dataset=cursor.fetchall()
align='{0:<3} {1:<6} {2:<15} {3:<10}'
print(align.format('ID', 'Code','Name',"Age"))
for item in dataset:
    id=item[0]
    code=item[1]
    name=item[2]
    age=item[3]
    avatar=item[4]
    intro=item[5]
    print(align.format(id,code,name,age))

cursor.close()

Thực thi lệnh trên ta có kết quả:

(2.5) Truy vấn chi tiết thông tin Sinh viên khi biết Id:

Ví dụ lấy chi tiết Sinh viên khi biết ID=1

cursor = conn.cursor()
sql="SELECT * FROM student " \
    "where ID=1 "

cursor.execute(sql)

dataset=cursor.fetchone()
if dataset!=None:
    id,code,name,age,avatar,intro=dataset
    print("Id=",id)
    print("code=",code)
    print("name=",name)
    print("age=",age)

cursor.close()

Thực thi lệnh trên ta có kết quả:

(2.6) Truy vấn dạng phân trang Student:

Trong trường hợp dữ liệu nhiều, ta có thể phân thành nhiều đợt truy vấn dữ liệu thông qua từ khóa limit và offset, nó còn được gọi là paging.

Ví dụ: Dữ liệu trong bảng Sinh viên có 6 phần tử, ta muốn truy vấn 2 lần, mỗi lần 3 phần tử:

Lần thứ nhất truy vấn 3 dòng dữ liệu đầu tiên (các ID 1, 2, 3) thì câu SQL viết như sau:

sql="SELECT * FROM student LIMIT 3 OFFSET 0"

Ta thử nghiệm chi tiết:

cursor = conn.cursor()
sql="SELECT * FROM student LIMIT 3 OFFSET 0"
cursor.execute(sql)

dataset=cursor.fetchall()
align='{0:<3} {1:<6} {2:<15} {3:<10}'
print(align.format('ID', 'Code','Name',"Age"))
for item in dataset:
    id=item[0]
    code=item[1]
    name=item[2]
    age=item[3]
    avatar=item[4]
    intro=item[5]
    print(align.format(id,code,name,age))

cursor.close()

Thực thi coding ở trên ta có kết quả:

Lần thứ nhì truy vấn 3 dòng dữ liệu còn lại (các ID 4, 5, 6) thì câu SQL viết như sau:

sql="SELECT * FROM student LIMIT 3 OFFSET 3"

Ta thử nghiệm chi tiết:

cursor = conn.cursor()
sql="SELECT * FROM student LIMIT 3 OFFSET 3"
cursor.execute(sql)

dataset=cursor.fetchall()
align='{0:<3} {1:<6} {2:<15} {3:<10}'
print(align.format('ID', 'Code','Name',"Age"))
for item in dataset:
    id=item[0]
    code=item[1]
    name=item[2]
    age=item[3]
    avatar=item[4]
    intro=item[5]
    print(align.format(id,code,name,age))

cursor.close()

Thực thi coding ở trên ta có kết quả:

Tức là LIMIT là số phần tử mà ta muốn truy vấn.

OFFSET là vị trí ta bắt đầu truy vấn.

Giả sử ta có N dòng Sinh viên, mỗi lần truy vấn là 3 sinh viên, hãy viết lệnh SQL để chương trình Paging toàn bộ dữ liệu N dòng này:

print("PAGING!!!!!")
cursor = conn.cursor()
sql="SELECT count(*) FROM student"
cursor.execute(sql)
dataset=cursor.fetchone()
rowcount=dataset[0]

limit=3
step=3
for offset in range(0,rowcount,step):
    sql=f"SELECT * FROM student LIMIT {limit} OFFSET {offset}"
    cursor.execute(sql)

    dataset=cursor.fetchall()
    align='{0:<3} {1:<6} {2:<15} {3:<10}'
    print(align.format('ID', 'Code','Name',"Age"))
    for item in dataset:
        id=item[0]
        code=item[1]
        name=item[2]
        age=item[3]
        avatar=item[4]
        intro=item[5]
        print(align.format(id,code,name,age))

cursor.close()

Thực thi lệnh PAGING trên ta có kết quả:

Mã lệnh ở trên hay ở chỗ nào?

Hay ở chỗ nếu như ta muốn phân trang bao nhiêu phần tử thì chỉ cần đổi limit và offset là được. Ví dụ như muốn phân trang mỗi lần chạy truy vấn 50 Sinh viên thì đổi limit=50 và step=50 (Bạn thấy Gmail không? email rất nhiều, nhưng mỗi lần họ cho truy vấn xem 50 email, muốn xem trước sau thì bấm nút. Đó là minh họa PAGING)

Source code dầy đủ cho phần kết nối và truy vấn dữ liệu:

https://www.mediafire.com/file/zf7qlx2rm4tgqri/TestQueryMySQL.py/file

(3) Lập trình Python thêm mới dữ liệu MySQL Server:

(3.1) Thêm mới 1 Student

Chúng ta sẽ viết mã lệnh để thêm mới 1 Student theo cú pháp như dưới đây:

cursor = conn.cursor()

sql="insert into student (code,name,age) values (%s,%s,%s)"

val=("sv07","Trần Duy Thanh",45)

cursor.execute(sql,val)

conn.commit()

print(cursor.rowcount," record inserted")

cursor.close()

Ở trên ta thấy, val là 1 tuple chưa thông tin của Student. Lưu ý rằng câu lệnh insert ở trên chỉ thêm code, name, age (không thấy ID vì nó tự động tăng, đồng thời avatar và intro Tui cũng không thêm mới tức là nó sẽ có giá trị null tự động)

Câu lệnh insert SQL dùng 3 biến, thì val sẽ cần 1 tuple có 3 thành phần như code minh họa.

Khi gọi lệnh cursor.execute(sql,val) thì chương trình sẽ tự động mapping giá trị trong tuple cho các %s

lệnh conn.commit() để xác thực là sẽ lưu mới dữ liệu

lệnh cursor.rowcount cho chúng ta biết có bao nhiêu dòng dữ liệu được thay đổi trong cơ sở dữ liệu.

Thực thi lệnh trên ta có kết quả:

(3.2) Thêm mới nhiều Student:

cursor = conn.cursor()

sql="insert into student (code,name,age) values (%s,%s,%s)"

val=[
    ("sv08","Trần Quyết Chiến",19),
    ("sv09","Hồ Thắng",22),
    ("sv10","Hoàng Hà",25),
     ]

cursor.executemany(sql,val)

conn.commit()

print(cursor.rowcount," record inserted")

cursor.close()

Mã lệnh trên có 2 chỗ khác biệt:

• Chỗ thứ nhất đó là val trở thành 1 mảng các Tuple
• chỗ thứ nhì là ta sử dụng hàm cursor.executemany(sql,val)

Thực thi lệnh ta có kết quả:

Source code toàn bộ phần Insert Student các bạn có thể tải ở đây:

https://www.mediafire.com/file/839moz9a83bjiau/TestInsertMySQL.py/file

(4) Lập trình Python cập nhật dữ liệu MySQL Server

(4.1) Cập nhật tên Sinh viên có Code=’sv09′ thành tên mới “Hoàng Lão Tà”

cursor = conn.cursor()
sql="update student set name='Hoàng Lão Tà' where Code='sv09'"
cursor.execute(sql)

conn.commit()

print(cursor.rowcount," record(s) affected")

Thực thi mã lệnh trên ta có kết quả:

(4.2) Cập nhật tên Sinh viên có Code=’sv09′ thành tên mới “Hoàng Lão Tà” như viết dạng SQL Injection:

cursor = conn.cursor()
sql="update student set name=%s where Code=%s"
val=('Hoàng Lão Tà','sv09')

cursor.execute(sql,val)

conn.commit()

print(cursor.rowcount," record(s) affected")

Source code toàn bộ phần Update Student các bạn có thể tải ở đây:

https://www.mediafire.com/file/5fes444erp2v4s3/TestUpdateMySQL.py/file

(5) Lập trình Python xóa dữ liệu MySQL Server

(5.1) Xóa Student có ID=14

conn = mysql.connector.connect(
                host=server,
                port=port,
                database=database,
                user=username,
                password=password)
cursor = conn.cursor()
sql="DELETE from student where ID=14"
cursor.execute(sql)

conn.commit()

print(cursor.rowcount," record(s) affected")

Thực hiện mã lệnh trên ta có kết quả:

(5.2) Xóa Student có ID=13 với SQL Injection

conn = mysql.connector.connect(
                host=server,
                port=port,
                database=database,
                user=username,
                password=password)
cursor = conn.cursor()
sql = "DELETE from student where ID=%s"
val = (13,)

cursor.execute(sql, val)

conn.commit()

print(cursor.rowcount," record(s) affected")

Sourecode đầy đủ phần xóa ở đây:

https://www.mediafire.com/file/qgr8ko351nhmho9/TestRemoveMySQL.py/file

Như vậy, tới đây Tui đã hướng dẫn đầy đủ và chi tiết các câu lệnh SQL liên quan tới CRUD để tương tác dữ liệu, sử dụng Python để triệu gọi lệnh.

Hầu hết các lệnh cốt lõi về tương tác Python với MySQL Server các bạn đã nắm, chỉ cần làm tốt cá lệnh này là hầu hết mọi yêu cầu liên quan tới viết phần mềm quản trị hệ thống là bạn có thể thực hiện được.

Bài học sau Tui sẽ nâng cấp bài này bằng cách mô hình hóa hướng đối tượng, sau đó thiết kế giao diện tương tác người dùng, rồi cuối cùng là tích hợp mô hình máy học, thống kê.

Các bạn chú ý theo dõi

Chúc các bạn thành công

Share on Facebook

Tweet

Follow us

Save