「QyDBLib」

2.資料合并

在日常的軟體開發過程中，資料合并是經常會用到的。比如：從兩個資料表中查詢到資料後，将資料進行合并。

2.1資料合并方法

1.merge_list2(list1: list, list2: list, how: str, list1_on: list, list2_on: list)

功能：将兩個清單中的資料進行合并操作。

參數：

• list1 - 左清單，清單中内容為字典，理論上應該是資料表查詢後的結果集
• list2 - 右清單，清單中内容為字典，理論上應該是資料表查詢後的結果集
• how - 表示合并方式，與資料表連接配接方式相同，取值為：inner|outer|left|right
• list1_on - 表示資料合并時，左清單使用的列名稱，類型為list
• list2_on - 表示資料合并時，右清單使用的列名稱，類型為list

傳回：傳回值為合并後的結果集，類型為字典清單。

2.2示例

2.2.1示例所使用的資料表及資料

--專業表
drop table if exists test.c_speciality;

create table test.c_speciality (
    speciality_id varchar(64) primary key,
    speciality_code varchar(16),
    speciality_name varchar(32)
);
--班級表
drop table if exists test.c_class;

create table test.c_class (
    class_id varchar(64) primary key,
    speciality_id varchar(64),
    class_name varchar(32)
);
--學生表
drop table if exists test.d_student;

create table test.d_student (
    student_id varchar(64) primary key,
    class_id varchar(64),
    student_num varchar(32),
    student_name varchar(32),
    sex varchar(2),
    birthday date,
    native_place varchar(256)
);
--專業資料
INSERT INTO test.c_speciality
(speciality_id, speciality_code, speciality_name)
VALUES('S1', 'jsjyy', '計算機應用');

INSERT INTO test.c_speciality
(speciality_id, speciality_code, speciality_name)
VALUES('S2', 'fdcgl', '房地産管理');

INSERT INTO test.c_speciality
(speciality_id, speciality_code, speciality_name)
VALUES('S3', 'jjgl', '經濟管理');
--班級資料
INSERT INTO test.c_class
(class_id, speciality_id, class_name)
VALUES('C1', 'S1', '9801班');

INSERT INTO test.c_class
(class_id, speciality_id, class_name)
VALUES('C2', 'S1', '9902班');

INSERT INTO test.c_class
(class_id, speciality_id, class_name)
VALUES('C3', 'S2', '9001班');

INSERT INTO test.c_class
(class_id, speciality_id, class_name)
VALUES('C4', 'S2', '9102班');

INSERT INTO test.c_class
(class_id, speciality_id, class_name)
VALUES('C5', 'S3', '9009班');

INSERT INTO test.c_class
(class_id, speciality_id, class_name)
VALUES('C6', 'S3', '9110班');
--學生資料
INSERT INTO test.d_student
(student_id, class_id, student_num, student_name, sex, birthday, native_place)
VALUES('T1', 'C1', '1018', '趙XX', '女', '1979-10-18', null);

INSERT INTO test.d_student
(student_id, class_id, student_num, student_name, sex, birthday, native_place)
VALUES('T2', 'C4', '0329', '陳XX', '女', '1973-09-28', null);           

2.2.2合并示例

# coding: utf-8
from logger import logger
import qydblib.transaction as ts
import qydblib.compound.merge as merge

if __name__ == '__main__':
    ts = ts.Transaction()
    try:
        ts.begin()

        sql = 'select * from test.c_class'
        classes = ts.retrieve_data_many(sql)

        sql = 'select * from test.c_speciality'
        specialities = ts.retrieve_data_many(sql)

        m1 = merge.merge_list2(classes, specialities, 'inner', ['speciality_id'], ['speciality_id'])
        print(m1)

        sql = 'select * from test.d_student'
        students = ts.retrieve_data_many(sql)
        students = merge.merge_list2(m1, students, 'inner', ['class_id'], ['class_id'])
        for student in students:
            print(student)

        ts.end()
    except Exception as ex:
        logger.error(ex)
        ts.abort()           

輸出結果：

m1:

[
    {
        'class_id': 'C1', 
        'speciality_id': 'S1', 
        'class_name': '9801班', 
        'speciality_code': 'jsjyy', 
        'speciality_name': '計算機應用'
    }, {
        'class_id': 'C2', 
        'speciality_id': 'S1', 
        'class_name': '9902班', 
        'speciality_code': 'jsjyy', 
        'speciality_name': '計算機應用'
    }, {
        'class_id': 'C3', 
        'speciality_id': 'S2', 
        'class_name': '9001班', 
        'speciality_code': 'fdcgl', 
        'speciality_name': '房地産管理'
    }, {
        'class_id': 'C4', 
        'speciality_id': 'S2', 
        'class_name': '9102班', 
        'speciality_code': 'fdcgl', 
        'speciality_name': '房地産管理'
    }, {
        'class_id': 'C5', 
        'speciality_id': 'S3', 
        'class_name': '9009班', 
        'speciality_code': 'jjgl', 
        'speciality_name': '經濟管理'
    }, {
        'class_id': 'C6', 
        'speciality_id': 'S3', 
        'class_name': '9110班', 
        'speciality_code': 'jjgl', 
        'speciality_name': '經濟管理'
    }
]           

students：

{
    'class_id': 'C1', 
    'speciality_id': 'S1', 
    'class_name': '9801班', 
    'speciality_code': 'jsjyy', 
    'speciality_name': '計算機應用', 
    'student_id': 'T1', 
    'student_num': '1018', 
    'student_name': '趙XX', 
    'sex': '女', 
    'birthday': datetime.date(1979, 10, 18), 
    'native_place': None
}
{
    'class_id': 'C4', 
    'speciality_id': 'S2', 
    'class_name': '9102班', 
    'speciality_code': 'fdcgl', 
    'speciality_name': '房地産管理', 
    'student_id': 'T2', 
    'student_num': '0329', 
    'student_name': '陳XX', 
    'sex': '女', 
    'birthday': datetime.date(1973, 9, 28), 
    'native_place': None
}