Python cho người Việt

Python cho người Việt

Entries tagged “mysql”

Truy vấn triệu bản ghi với MySQL

written by Nguyễn Thành Nam, on Jul 25, 2012 11:56:00 AM.

Trong bài viết của bạn Phạm Thị Minh Hoài, chúng ta đã được hướng dẫn cách truy vấn MySQL với DB-API của Python. Phần này chúng ta sẽ bàn về một số điểm cần chú ý nếu câu truy vấn của chúng ta trả về một lượng lớn bản ghi từ MySQL.

Hết bộ nhớ

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ớ.

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.

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.

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.

Hết giờ (timeout)

Vấn đề

Với SSCursor ta có thể sẽ viết mã như sau:

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

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

Lý do là việc xử lý từng bản ghi sẽ làm ta 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.

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ờ.

Lưu ý

Với việc ghi bản tin ra dĩa (hoặc một nơi nội bộ nào khác), chúng ta cần đảm bảo rằng nơi đó có đủ chỗ để lưu toàn bộ các bản ghi đọc được.

MySQLdb - Thiết lập kết nối

written by Phạm Thị Minh Hoài, on May 31, 2010 11:55:00 PM.

MySQLdb là một wrapper của thư viện MySQL C API: _mysql. Nó cho phép bạn viết các ứng dụng Python khả chuyển (portable) chạy trên nhiều môi trường khác nhau để truy cập vào máy chủ có hệ quản trị CSDL MySQL.

Bài viết sau đây đề cập một số khía cạnh liên quan tới thiết lập kết nối CSDL. Đó là vấn đề quan trọng đầu tiên khi làm việc với bất kỳ hệ quản trị CSDL nào.

Bản cài đặt MySQLdb có thể download trên trang: http://sourceforge.net/projects/mysql-python/. Download có sẵn cho các nền tảng windows 32 bit và các họ unix. Đáng tiếc là MySQLdb hiện chỉ sử dụng cho các phiên bản python từ 2.3 đến 2.6.

Hello World

Giả thiết trên máy của bạn đã cài MySQL với cổng mặc định (3306) và account root không cần password. Đoạn chương trình sau minh họa một kết nối đơn giản nhất có thể. Nó in ra các db có trong CSDL này:

>>> db = MySQLdb.connect(user="root")
>>> cursor = db.cursor()
>>> query = "SHOW DATABASES"
>>> cursor.execute(query)
6L
>>> cursor.fetchall()
(('information_schema',), ('cdcol',), ('mysql',), ('phpmyadmin',), ('test',))

Kết nối theo IP

Truy cập từ xa đến một hệ thống phức tạp hơn, bạn cần biết các tham số:

  • Máy chủ chứa MySQL (mặc định: localhost hay 127.0.0.1)

  • Cổng truy cập vào MySQL (mặc định: 3306)

  • User, Password mà bạn được cấp

  • Tên CSDL bạn muốn truy cập

Ví dụ sau in ra các bảng của CSDL mydb trên máy 192.168.90.31 với cổng 3310:

>>> db = MySQLdb.connect(user="you", db="mydb", passwd="mypw", \
      host="192.168.90.31", port=3310)
>>> cursor = db.cursor()
>>> query = "SHOW TABLES"
>>> cursor.execute(query)
>>> cursor.fetchall()

Kết nối theo UNIX socket

Trên các localhost cho phép dùng UNIX socket bạn có thể viết:

db = MySQLdb.connect(db="test", user="hoaiptm", passwd="hoaiptm", \
                     unix_socket="/opt/hoaiptm/mysql/mysql.sock")

Truy cập MySQL qua các socket trên unix tương tự như named pipe trên windows. Nó cho phép các ứng dụng localhost tạo các kết nối cực kỳ nhanh đến CSDL. Nếu bạn chưa hiểu rõ về socket bạn có thể đọc: http://beej.us/guide/bgipc/output/html/multipage/unixsock.html.

Giấu password

Các ví dụ trên đây đều yêu cầu phải có password đặt trong mã nguồn Python. MySQLdb cho phép bạn giấu chúng trong một nơi mà chỉ bạn được phép truy cập:

db = MySQLdb.connect(db="yourdb", read_default_file="/home/hoaiptm/mysql.ini")

tập tin options /home/hoaiptm/mysql.ini có thể có nội dung như sau:

[client]
port=3310
socket=/opt/hoaiptm/mysql/mysql.sock
user=hoaiptm
password=hoaiptm

Hướng dẫn về các option này có thể đọc trong tài liệu: http://www.yellis.net/docs/mysql/manuel_Option_files.html#Option_files.

Thay đổi charset mặc định

Các kết nối trong các ví dụ trên sử dụng charset mặt định là latin1, do đó bạn không lấy được các dữ liệu tiếng Việt. Chẳng hạn bạn tạo một bảng user với chartset utf8 trong CSDL test như sau:

USE `test`;
CREATE TABLE `user` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(50) DEFAULT NULL,
  `fullname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 **DEFAULT CHARSET=utf8** ROW_FORMAT=DYNAMIC;
INSERT INTO `user` VALUES (1,'hoaiptm','Phạm Thị Minh Hoài');
INSERT INTO `user` VALUES (2,'trangnt','Ngô Thu Trang');

Sau đó cố gắng lấy dữ liệu từ bảng user:

>>> db = MySQLdb.connect(user="hoaiptm", db="test", passwd="hoaiptm", port=3310)
>>> cursor = db.cursor()
>>> query = "SELECT fullname FROM user"
>>> cursor.execute(query)
2L
>>> cursor.fetchall()
(('Ph?m Th? Minh Ho\xe0i',), ('Ng\xf4 Thu Trang',))

Các dấu ? cho thấy các ký tự utf8 đã không được truyền tải đúng. Để giải quyết vấn đề này chúng ta thêm tùy chọn charset = "utf8" trong connection:

>>> db = MySQLdb.connect(user="hoaiptm", db="test", passwd="hoaiptm", \
      port=3310, charset="utf8")
...
>>> cursor.fetchall()
((u'Ph\u1ea1m Th\u1ecb Minh Ho\xe0i',), (u'Ng\xf4 Thu Trang',))

Lúc này tất cả các dữ liệu trong trường text, char, varchar đều được trả về dạng unicode.

Yêu cầu trả về từ điển

MySQLdb cho phép trả về các kết quả dạng từ điển, để làm được điều đó bạn thêm tùy chọn cursorclass=MySQLdb.cursors.DictCursor. Ví dụ:

>>> db = MySQLdb.connect(user="hoaiptm", db="test", passwd="hoaiptm", port=3310, \
      charset="utf8", cursorclass=MySQLdb.cursors.DictCursor)
>>> cursor = db.cursor()
>>> query = "select * from user"
>>> cursor.execute(query)
2L
>>> cursor.fetchall()
({'account': u'hoaiptm', 'fullname': u'Ph\u1ea1m Th\u1ecb Minh Ho\xe0i', 'Id': 1L}, \
 {'account': u'trangnt', 'fullname': u'Ng\xf4 Thu Trang', 'Id': 2L})

Chương trình sẽ chạy chậm đi chút xíu, nhưng bù lại kết quả trả về rất dễ sử dụng.