Truy vấn dữ liệu trong python

Question

Nội dung chính Show

** I. Hết bộ nhớ**
1. Vấn đề
2. Cách khắc phục
II. Hết giờ (timeout)
1. Vấn đề
2. Cách khắc phục

Đã đăng vào thg 11 28, 2015 5:39 CH 5 phút đọc

Khi chúng ta truy xuất nhiều bản ghi trong cơ sở dữ liệu cùng 1 lúc sẽ sảy ra một số vấn đề sau.

I. Hết bộ nhớ

1. Vấn đề

Một câu truy vấn có thể trả về một lượng dữ liệu khổng lồ. Ví dụ câu truy vấn SELECT * FROM users có thể trả về hơn chục triệu bản ghi ở các ứng dụng lớn. Trong điều kiện bình thường, có lẽ lượng bản ghi lớn như vậy sẽ khiến cho chương trình của chúng ta sử dụng hết bộ nhớ và bị buộc phải chấm dứt giữa chừng, cho dù chúng ta có sử dụng fetchone, fetchmany hay fetchall để lấy dữ liệu.

Lý do là phần giao tiếp giữa Python và MySQL mặc định sẽ lấy tất cả các bản ghi của câu truy vấn về trước, chứa chúng trong bộ nhớ, rồi sau đó trả về cho Python một bản ghi, nhiều bản ghi, hay tất cả các bản ghi đó tùy thuộc vào hàm nào được gọi.

Điều này cũng dễ hiểu vì giao thức mạng của MySQL là mô hình yêu cầu/đáp trả (request/response). Một truy vấn là một yêu cầu. Và các bản ghi của truy vấn đó là một đáp trả. Cho nên trình điều khiển (driver) cần phải đọc hết toàn bộ đáp trả để kết thúc chu trình yêu cầu/đáp trả trước khi trả lời các lời gọi hàm fetchone, fetchmany hay fetchall. Nói một cách khác, các hàm fetchone hay fetchmany trả về kết quả đã có trong bộ nhớ.

Đó cũng chính là lý do vì sao chúng ta có thể gọi fetchone hay fetchmany nhiều lần. Các hàm này không tạo một chu trình yêu cầu/đáp trả mới mà chỉ đơn giản là tiếp tục trả về các bản ghi đã chứa trong bộ nhớ.

2. Cách khắc phục

Cách khắc phục là sử dụng SSCursor khi tạo con trỏ từ kết nối MySQL. Lớp SSCursor nằm trong mô-đun MySQLdb.

conn = MySQLdb.connect(...)
cursor = MySQLdb.SSCursor(conn)

Sau đó khi gọi fetchone hoặc fetchmany thì trình điều khiển sẽ không đọc toàn bộ đáp trả vào bộ nhớ nữa mà sẽ chỉ đọc vừa đủ để trả về bấy nhiêu bản ghi cho ta.

3. Lưu ý

Khi sử dụng SSCursor, ta nhất định phải đảm bảo chu trình yêu cầu/đáp trả được hoàn tất. Ví dụ câu truy vấn trả về 10 bản ghi, thì ta phải đảm bảo đọc hết 10 bản ghi này. Nếu ta chỉ gọi fetchone 5 lần, thì sẽ còn 5 bản ghi vẫn chưa được đọc hết, và do đó ta sẽ không thể gửi truy vấn khác trong cùng kết nối hiện tại.

Vì SSCursor không giữ kết quả trong bộ nhớ nên ta sẽ không thể di chuyển con trỏ tới, hoặc lùi để truy xuất bản ghi ta cần. Điều duy nhất chúng ta có thể làm với SSCursor là đọc tuần tự tất cả các bản ghi.

II. Hết giờ (timeout)

1. Vấn đề

Với SSCursor ta có thể sẽ viết mã như sau để đọc lần lượt các bản ghi trong bộ nhớ đệm:

row = cursor.fetchone()
while row:
    # xử lý row
    row = cursor.fetchone()

Đoạn mã này đôi khi sẽ gây ra lỗi đệm Lost connection to MySQL server during query.

Lý do là việc xử lý từng bản ghi sẽ rất tốn thời gian, và ta sẽ không thể đọc đáp trả đủ nhanh, khiến cho máy chủ MySQL phải hoãn việc gửi tiếp các bản ghi về cho ta. Máy chủ MySQL chỉ có thể hoãn việc gửi thông tin trong một thời gian ngắn. Quá thời gian này, máy chủ sẽ tự ngắt kết nối.

2. Cách khắc phục

Tốt nhất là chúng ta sử dụng SSCursor để đọc tất cả các bản ghi từ máy chủ ở xa và ghi chúng vào một tập tin trên máy hiện tại. Sau đó ta đọc lại từ tập tin này và xử lý từng bản ghi đã lưu. Khi làm như vậy, chúng ta tránh được lỗi hết bộ nhớ đã đề cập ở trên, và hy vọng rằng việc ghi bản tin ra dĩa xảy ra đủ nhanh để ta có thể đọc bản ghi khác gần như ngay lập tức, tránh được lỗi hết giờ.

_Note _: Hiện nay một số Frameworkày sử dụng ORM như Django hỗ trợ rất tốt vấn đề truy vấn này. Nên gần như lập trình viên ko còn phải qúa bận tâm. Nhưng nếu bạn thực sự muốn cấu hình Project của mình theo 1 cách riêng biệt thì hãy bắt tay vào học từ những điều cơ bản nhất.

Trong các bài trước, chúng ta thấy rằng số lượng các bản ghi của cơ sở dữ liệu được đề cập trong các ví dụ được giới hạn ở mức tương đối ít, và các dữ liệu được tổ chức theo kiểu khá đơn giản. Ở bài này chúng ta sẽ cùng tìm hiểu về cách để ghi (write) và tìm nạp (fetch) dữ liệu lớn từ cơ sở dữ liệu bằng cách sử dụng module SQLite3.

Cách tìm nạp dữ liệu đơn giản nhất chính là thực thi câu truy vấn bằng hàm execute() và sau đó sử dụng hàm fetchall(). Điều này đã được đề cập trong bài “Thao tác với cơ sở dữ liệu trong Python – Phần 1”.

Hàm executescript(): Đây là một phương thức tiện lợi giúp thực thi nhiều câu lệnh SQL cùng một lúc. Phương thức này sẽ thực thi các câu lệnh SQL mà nó nhận được dưới dạng tham số truyền vào.

+ Cú pháp:

sqlite3.connect.executescript(script)

+ Ví dụ:

# -----------------------------------------------------------
#Cafedev.vn - Kênh thông tin IT hàng đầu Việt Nam
#@author cafedevn
#Contact: 
#Fanpage: https://www.facebook.com/cafedevn
#Group: https://www.facebook.com/groups/cafedev.vn/
#Instagram: https://instagram.com/cafedevn
#Twitter: https://twitter.com/CafedeVn
#Linkedin: https://www.linkedin.com/in/cafe-dev-407054199/
#Pinterest: https://www.pinterest.com/cafedevvn/
#YouTube: https://www.youtube.com/channel/UCE7zpY_SlHGEgo67pHxqIoA/
# -----------------------------------------------------------

import sqlite3 
  
# Connection with the DataBase 
# 'library.db' 
connection = sqlite3.connect("library.db") 
cursor = connection.cursor() 
  
# SQL piece of code Executed 
# SQL piece of code Executed 
cursor.executescript(""" 
    CREATE TABLE people( 
        firstname, 
        lastname, 
        age 
    ); 
   
    CREATE TABLE book( 
        title, 
        author, 
        published 
    ); 
   
    INSERT INTO 
    book(title, author, published) 
    VALUES ( 
        'Dan Clarke''s GFG Detective Agency', 
        'Sean Simpsons', 
        1987 
    ); 
    """) 
  
sql = """ 
SELECT COUNT(*) FROM book;"""
  
cursor.execute(sql) 
  
# The output in fetched and returned 
# as a List by fetchall() 
result = cursor.fetchall() 
print(result) 
  
sql = """ 
SELECT * FROM book;"""
  
cursor.execute(sql) 
  
result = cursor.fetchall() 
print(result) 
  
# Changes saved into database 
connection.commit() 
  
# Connection closed(broken)  
# with DataBase 
connection.close()

+ Kết quả in ra là:

[(1,)]
[("Dan Clarke's GFG Detective Agency", 'Sean Simpsons', 1987)]

+ Lưu ý: Đoạn code ví dụ này có thể sẽ không hoạt động trên các trình thông dịch trực tuyến (online interpreters), do gặp vấn đề về sự cho phép các đặc quyền để tạo và ghi vào cơ sở dữ liệu.

Hàm executemany(): Chúng ta sẽ gặp nhiều trường hợp mà trong đó, lượng dữ liệu lớn cần được thêm vào trong cơ sở dữ liệu thì nằm ở trong các Data Files (là các tập tin chứa dữ liệu, ngoài ra còn một số trường hợp đơn giản hơn có thể nhắc đến như List, array). Phương thức executemany() sẽ thực thi một câu lệnh SQL với tất cả các chuỗi tham số hoặc các ánh xạ tham số được tìm thấy trong chuỗi sql. Module sqlite3 còn cho phép sử dụng một iterator để cho phép sử dụng phương thức này với các tham số, thay vì một chuỗi các tham số. Nhưng việc sử dụng vòng lặp sẽ không phù hợp trong trường hợp này, ví dụ dưới dây sẽ cho thấy tại sao. Cú pháp và cách sử dụng phương thức executemany() sẽ được giải thích bên dưới, ngoài ra cách sử dụng nó như một vòng lặp cũng sẽ được trình bày.

+ Ví dụ:

# -----------------------------------------------------------
#Cafedev.vn - Kênh thông tin IT hàng đầu Việt Nam
#@author cafedevn
#Contact: 
#Fanpage: https://www.facebook.com/cafedevn
#Group: https://www.facebook.com/groups/cafedev.vn/
#Instagram: https://instagram.com/cafedevn
#Twitter: https://twitter.com/CafedeVn
#Linkedin: https://www.linkedin.com/in/cafe-dev-407054199/
#Pinterest: https://www.pinterest.com/cafedevvn/
#YouTube: https://www.youtube.com/channel/UCE7zpY_SlHGEgo67pHxqIoA/
# -----------------------------------------------------------

import sqlite3 
  
# Connection with the DataBase 
# 'library.db' 
connection = sqlite3.connect("library.db") 
cursor = connection.cursor() 
  
# SQL piece of code Executed 
cursor.execute(""" 
      
    CREATE TABLE book( 
        title, 
        author, 
        published);""") 
   
List = [('A', 'B', 2008), ('C', 'D', 2008), 
                          ('E', 'F', 2010)] 
   
connection. executemany(""" 
                   
    INSERT INTO  
    book(title, author, published)  
    VALUES (?, ?, ?)""", List) 
  
sql = """ 
      SELECT * FROM book;"""
cursor.execute(sql) 
result = cursor.fetchall() 
for x in result: 
    print(x) 
  
# Changes saved into database 
connection.commit() 
  
# Connection closed(broken)  
# with DataBase 
connection.close()

+ Kết quả in ra là:

Traceback (most recent call last):
  File "C:/Users/GFG/Desktop/SQLITE3.py", line 16, in 
    List[2][3] =[['A', 'B', 2008], ['C', 'D', 2008], ['E', 'F', 2010]]
NameError: name 'List' is not defined

+ Việc sử dụng hàm executemany() có thể làm cho đoạn code sau hoạt động:


import sqlite3 
   
# Connection with the DataBase 
# 'library.db' 
connection = sqlite3.connect("library.db") 
cursor = connection.cursor() 
   
# SQL piece of code Executed 
cursor.execute(""" 
    CREATE TABLE book( 
        title, 
        author, 
        published);""") 
   
List = [('A', 'B', 2008), ('C', 'D', 2008),  
                          ('E', 'F', 2010)] 
   
connection. executemany(""" 
    INSERT INTO  
    book(title, author, published)  
    VALUES (?, ?, ?)""", List) 
   
sql = """ 
SELECT * FROM book;"""
cursor.execute(sql) 
result = cursor.fetchall() 
for x in result: 
    print(x) 
   
# Changes saved into database 
connection.commit() 
   
# Connection closed(broken) 
# with DataBase 
connection.close()

+ Kết quả in ra là:

('A', 'B', 2008)
('C', 'D', 2008)
('E', 'F', 2010)

Tìm nạp lượng dữ liệu lớn

+ Ví dụ:

# -----------------------------------------------------------
#Cafedev.vn - Kênh thông tin IT hàng đầu Việt Nam
#@author cafedevn
#Contact: 
#Fanpage: https://www.facebook.com/cafedevn
#Group: https://www.facebook.com/groups/cafedev.vn/
#Instagram: https://instagram.com/cafedevn
#Twitter: https://twitter.com/CafedeVn
#Linkedin: https://www.linkedin.com/in/cafe-dev-407054199/
#Pinterest: https://www.pinterest.com/cafedevvn/
#YouTube: https://www.youtube.com/channel/UCE7zpY_SlHGEgo67pHxqIoA/
# -----------------------------------------------------------

import sqlite3 
  
# Connection created with the 
# database using sqlite3.connect() 
connection = sqlite3.connect("company.db") 
cursor = connection.cursor() 
  
# Create Table command executed 
sql = """ 
        CREATE TABLE employee (  
        ID INTEGER PRIMARY KEY,  
        fname VARCHAR(20),  
        lname VARCHAR(30),  
        gender CHAR(1),  
        dob DATE);"""
cursor.execute(sql) 
  
# Single Tuple inserted 
sql = """ 
        INSERT INTO employee 
        VALUES (1007, "Will", "Olsen", "M", "24-SEP-1865");"""
cursor.execute(sql) 
  
# Multiple Rows inserted 
List = [(1008, 'Rkb', 'Boss', 'M', "27-NOV-1864"), 
        (1098, 'Sak', 'Rose', 'F', "27-DEC-1864"), 
        (1908, 'Royal', 'Bassen', "F", "17-NOV-1894")] 
  
connection. executemany( 
        "INSERT INTO employee VALUES (?, ?, ?, ?, ?)", List) 
  
print("Method-1\n") 
  
# Multiple Rows fetched from 
# the Database 
for row in connection.execute('SELECT * FROM employee ORDER BY ID'): 
        print (row) 
  
print("\nMethod-2\n") 
  
# Method-2 to fetch multiple 
# rows 
sql = """ 
        SELECT * FROM employee ORDER BY ID;"""
  
cursor.execute(sql) 
result = cursor.fetchall() 
  
for x in result: 
    print(x) 
  
connection.commit() 
connection.close()

+ Kết quả in ra là:

Method-1

(1007, 'Will', 'Olsen', 'M', '24-SEP-1865')
(1008, 'Rkb', 'Boss', 'M', '27-NOV-1864')
(1098, 'Sak', 'Rose', 'F', '27-DEC-1864')
(1908, 'Royal', 'Bassen', 'F', '17-NOV-1894')

Method-2

(1007, 'Will', 'Olsen', 'M', '24-SEP-1865')
(1008, 'Rkb', 'Boss', 'M', '27-NOV-1864')
(1098, 'Sak', 'Rose', 'F', '27-DEC-1864')
(1908, 'Royal', 'Bassen', 'F', '17-NOV-1894')

+ Lưu ý: Đoạn code ví dụ này có thể sẽ không hoạt động trên các trình thông dịch trực tuyến (online interpreters), do gặp vấn đề về sự cho phép các đặc quyền để tạo và ghi vào cơ sở dữ liệu.

Nguồn và Tài liệu tiếng anh tham khảo:

w3school
python.org
geeksforgeeks

Tài liệu từ cafedev:

Full series tự học Python từ cơ bản tới nâng cao tại đây nha.
Ebook về python tại đây.
Các series tự học lập trình khác

Nếu bạn thấy hay và hữu ích, bạn có thể tham gia các kênh sau của cafedev để nhận được nhiều hơn nữa:

Group Facebook
Fanpage
Youtube
Instagram
Twitter
Linkedin
Pinterest
Trang chủ

Chào thân ái và quyết thắng!

Đăng ký kênh youtube để ủng hộ Cafedev nha các bạn, Thanks you!

programming python Fetch trong Python Cursor trong Python Fetchall trong Python

Truy vấn dữ liệu trong python

** I. Hết bộ nhớ**

1. Vấn đề

2. Cách khắc phục

3. Lưu ý

II. Hết giờ (timeout)

1. Vấn đề

2. Cách khắc phục

Đăng ký kênh youtube để ủng hộ Cafedev nha các bạn, Thanks you!

Bài Viết Liên Quan

Quảng Cáo

Có thể bạn quan tâm

Toplist được quan tâm

Quảng cáo

Xem Nhiều

Quảng cáo

Chúng tôi

Điều khoản

Trợ giúp

Mạng xã hội

I. Hết bộ nhớ