Cách làm việc với cơ sở dữ liệu SQLite và Pandas trong Python

Giới thiệu

Trong môi trường doanh nghiệp thực tế, dữ liệu thường không lưu trong file text hay Excel. Cơ sở dữ liệu quan hệ dựa trên SQL như Oracle, SQL Server, PostgreSQL, MySQL được sử dụng rộng rãi. Lựa chọn database phụ thuộc vào hiệu năng, tính toàn vẹn dữ liệu và nhu cầu mở rộng.

Cách thực hiện

Ví dụ này minh họa tạo database SQLite3 (có sẵn trong Python, không cần cài thêm) và sử dụng Pandas để truy vấn dữ liệu.

1. Kiểm tra phiên bản SQLite

import sqlite3
import pandas as pd

print(f"Output \n {sqlite3.version}")

Kết quả: 2.6.0

2. Tạo kết nối và dữ liệu mẫu

# Đối tượng kết nối
conn = sqlite3.connect("example.db")

# Dữ liệu khách hàng
customers = pd.DataFrame({
    "customerID": ["a1", "b1", "c1", "d1"],
    "firstName": ["Person1", "Person2", "Person3", "Person4"],
    "state": ["VIC", "NSW", "QLD", "WA"]
})
print(f"Output \n *** Thông tin khách hàng -\n {customers}")

3. Dữ liệu đơn hàng

orders = pd.DataFrame({
    "customerID": ["a1", "a1", "a1", "d1", "c1", "c1"],
    "productName": ["road bike", "mountain bike", "helmet", "gloves", "road bike", "glasses"]
})
print(f"Output \n *** Thông tin đơn hàng -\n {orders}")

4. Ghi dữ liệu vào database

customers.to_sql("customers", con=conn, if_exists="replace", index=False)
orders.to_sql("orders", conn, if_exists="replace", index=False)

5. Truy vấn SQL với JOIN

q = """
SELECT orders.customerID, customers.firstName, COUNT(*) as productQuantity
FROM orders
LEFT JOIN customers ON orders.customerID = customers.customerID
GROUP BY customers.firstName;
"""

6. Thực thi truy vấn và đọc vào DataFrame

result = pd.read_sql_query(q, con=conn)
print(result)

7. Chương trình hoàn chỉnh

import sqlite3
import pandas as pd

print(f"Output \n {sqlite3.version}")

conn = sqlite3.connect("example.db")

customers = pd.DataFrame({
    "customerID": ["a1", "b1", "c1", "d1"],
    "firstName": ["Person1", "Person2", "Person3", "Person4"],
    "state": ["VIC", "NSW", "QLD", "WA"]
})
print(f"*** Thông tin khách hàng -\n {customers}")

orders = pd.DataFrame({
    "customerID": ["a1", "a1", "a1", "d1", "c1", "c1"],
    "productName": ["road bike", "mountain bike", "helmet", "gloves", "road bike", "glasses"]
})
print(f"*** Thông tin đơn hàng -\n {orders}")

customers.to_sql("customers", con=conn, if_exists="replace", index=False)
orders.to_sql("orders", conn, if_exists="replace", index=False)

q = """
SELECT orders.customerID, customers.firstName, COUNT(*) as productQuantity
FROM orders
LEFT JOIN customers ON orders.customerID = customers.customerID
GROUP BY customers.firstName;
"""

result = pd.read_sql_query(q, con=conn)
print(result)

Kết quả:

customerID firstName productQuantity
0 a1        Person1   3
1 c1        Person3   2
2 d1        Person4   1