Truy xuất dữ liệu từ cơ sở dữ liệu
SELECT
re.tenant_id AS adm_div_code,
re.pa_year,
re.pa_bt_bt_no nt_pa_vo_id,
pi.created_date AS bi_date,
re.accept_bt_code,
'' AS exec_bt_name,
'' AS bt_fax_proj_code,
tt.bt_fax_pro_code AS bt_fax_code,
tt.bt_fax_pro_name AS bt_fax_name,
tt.bt_type AS bt_in_category_code,
'' AS bt_in_category_name,
tt.in_sort_code AS in_sort_code,
'' AS in_sort_name,
re.bi_no AS bt_fax_pa_no,
re.pa_bt_name AS act_payer_name,
'' AS charge_stand_name,
re.receivable_pb_am AS pb_am,
re.ac_pb_am AS pbd_am,
p.qty AS pa_number,
re.trade_time AS pa_date
FROM
bt_pa_red re
LEFT JOIN bt_pa_pro_be p ON re.bid = p.ref_pa_record_bid AND p.is_normal = '1'
LEFT JOIN nt_prd tt ON tt.bid = p.ref_project_bid AND tt.is_normal = '1'
LEFT JOIN bt_bi pi ON pi.bi_no = re.bi_no AND pi.is_normal = '1'
WHERE re.is_normal = '1';
Có thể thấy câu truy vấn kết hợp bảng bt_pa_red với ba bảng khác: bt_pa_pro_be, nt_prd và bt_bi. Quy mô dữ liệu của ba bảng: bt_bi: 2,900,461 bản ghi bt_pa_pro_be: 84,380 bản ghi bt_pa_red: 2,970,776 bản ghi nt_prd: 2,370 bản ghi
Trong quá trình thực thi truy vấn, máy tính với 64GB RAM vẫn không đủ để xử lý và trả về kết quả mong muốn.
Sử dụng Python để đọc từng bản ghi từ bảng bt_pa_red, sau đó lấy dữ liệu theo điều kiện join trong SQL Khi viết code để thực thi, phát hiện một số bản ghi bị lặp lại nhiều lần, số lần lặp lên đến 289,052. Sau khi tìm và kiểm tra bản ghi có vấn đề, phát hiện rằng cột bi_no trong bảng bt_pa_pro_be chứa giá trị rỗng, dẫn đến việc khớp dữ liệu bị trùng lặp. Thêm điều kiện kiểm tra trong Python: if bt_fax_pa_no is not None and len(bt_fax_pa_no) > 0: Python thực thi không còn dữ liệu trùng. Đồng thời sửa điều kiện trong SQL, thêm vào where điều kiện and length(bi_no) > 2, câu SQL cũng có thể thực thi được và cuối cùng truy xuất được 3 triệu bản ghi dữ liệu.
Code Python cuối cùng
#encoding: utf-8
#!/bin/python3
import sys
import pymysql
import time
# Kết nối cơ sở dữ liệu
conn1 = pymysql.connect(host="127.0.0.1", user="bt", password="123456", db="bt", port=3306)
conn2 = pymysql.connect(host="127.0.0.1", user="bt", password="123456", db="bt", port=3306)
conn3 = pymysql.connect(host="127.0.0.1", user="bt", password="123456", db="bt", port=3306)
conn4 = pymysql.connect(host="127.0.0.1", user="bt", password="123456", db="bt", port=3306)
if __name__ == "__main__":
print(time.asctime(time.localtime(time.time())))
cursor = pymysql.cursors.SSCursor(conn1)
cursor.execute("SELECT * FROM bt_pa_red WHERE is_normal='1' AND trade_time >='2021-01-01 00:00:00' AND trade_time <='2021-06-30 23:59:59'")
count = 0
while True:
count += 1 # Đếm số bản ghi đã xử lý
row = cursor.fetchone()
if not row:
break
# Trích xuất dữ liệu từ bảng chính
adm_div_code = row[9]
pa_year = row[10]
nt_pa_vo_id = row[17]
bi_date = None
accept_bt_code = row[12]
exec_bt_name = ""
bt_fax_proj_code = ""
bt_fax_code = None
bt_fax_name = None
bt_in_category_code = None
bt_in_category_name = ""
in_sort_code = None
in_sort_name = ""
bt_fax_pa_no = row[22]
act_payer_name = row[23]
charge_stand_name = ""
pb_am = row[18]
pbd_am = row[31]
pa_number = None
pa_date = row[33]
# Kết nối bảng bt_pa_pro_be
rebid = row[11]
query_bt_pa_pro_be = "SELECT * FROM bt_pa_pro_be WHERE ref_pa_record_bid='" + rebid + "' AND is_normal='1'"
cursor_bt_pa_pro_be = conn2.cursor()
cursor_bt_pa_pro_be.execute(query_bt_pa_pro_be)
bt_pa_pro_be_data = cursor_bt_pa_pro_be.fetchall()
for detail_item in bt_pa_pro_be_data:
pa_number = detail_item[15]
detail_ref_project_bid = detail_item[13]
# Xử lý bảng bt_bi với kiểm tra giá trị không rỗng
if bt_fax_pa_no is not None and len(bt_fax_pa_no) > 0:
query_bt_bi = "SELECT * FROM bt_bi WHERE bi_no = '" + bt_fax_pa_no + "' AND is_normal='1'"
bt_bi_cursor = conn3.cursor()
bt_bi_cursor.execute(query_bt_bi)
bt_bi_data = bt_bi_cursor.fetchall()
for bt_bi_item in bt_bi_data:
bi_date = bt_bi_item[3]
# Xử lý bảng nt_prd
query_nt_prd = "SELECT * FROM nt_prd WHERE bid ='" + detail_ref_project_bid + "' AND is_normal='1'"
nt_prd_cursor = conn3.cursor()
nt_prd_cursor.execute(query_nt_prd)
nt_prd_data = nt_prd_cursor.fetchall()
for nt_prd_item in nt_prd_data:
bt_fax_code = nt_prd_item[12]
bt_fax_name = nt_prd_item[13]
bt_in_category_code = nt_prd_item[14]
in_sort_code = nt_prd_item[16]
# Tạo câu lệnh chèn dữ liệu
insert_query = """INSERT INTO p_result VALUES("{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}","{}")""".format(
adm_div_code, pa_year, nt_pa_vo_id, bi_date, accept_bt_code, exec_bt_name,
bt_fax_proj_code, bt_fax_code, bt_fax_name, bt_in_category_code,
bt_in_category_name, in_sort_code, in_sort_name, bt_fax_pa_no,
act_payer_name, charge_stand_name, pb_am, pbd_am, pa_number, pa_date
)
# Ghi dữ liệu vào file
content_line = "{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}\n".format(
adm_div_code, pa_year, nt_pa_vo_id, bi_date, accept_bt_code, exec_bt_name,
bt_fax_proj_code, bt_fax_code, bt_fax_name, bt_in_category_code,
bt_in_category_name, in_sort_code, in_sort_name, bt_fax_pa_no,
act_payer_name, charge_stand_name, pb_am, pbd_am, pa_number, pa_date
)
# Ghi vào file
with open('result', 'a+', encoding='utf8') as output_file:
output_file.write(content_line)
# Thực thi chèn vào cơ sở dữ liệu
insert_cursor = conn4.cursor()
insert_cursor.execute(insert_query)
conn4.commit()
print(time.asctime(time.localtime(time.time())))