Verify has many association of data mapping patterns.

expected_response.json

"car_makers": [
    {
        "id": 1,
        "name": 'TOYOTA',
        "cars": [
            {
                "id": 1,
                "name": 'カローラ'
            },
            {
                "id": 2,
                "name": 'クラウン'
            },
            {
                "id": 3,
                "name": '86'
            },
        ]
    },
    {
        "id": 2,
        "name": 'FIAT',
        "cars": [
            {
                "id": 1,
                "name": '500'
            },
            {
                "id": 2,
                "name": 'PANDA'
            },
            {
                "id": 3,
                "name": 'PUNTO'
            },
        ]
    },
]

main.py

import json
import time
import psycopg2
import psycopg2.extras

# replace your database account
DB_CONFIG = {
    'host': 'localhost',
    'dbname': 'your_db',
    'user': 'hogehoge',
    'password': 'piyopiyo'
}


def fetch(query, data=()):
    rows = None
    with psycopg2.connect(**DB_CONFIG) as db:
        with db.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
            cur.execute(query, data)
            db.commit()
            rows = cur.fetchall()
    return [dict(row) for row in rows]


def measure(pattern):
    def _measure(func):
        def wrapper(*args, **kargs):
            start_time = time.time()
            func()
            elapsed_time = time.time() - start_time
            print('{0} 実行時間: {1:.4f} [sec]'.format(pattern, elapsed_time))
        return wrapper
    return _measure


@measure('Pattern: 1')
def pattern_1():
    query = """
        SELECT
            a.id AS maker_id,
            a.name AS maker_name,
            b.id AS car_id,
            b.name AS car_name
        FROM car_makers AS a
        INNER JOIN cars AS b ON a.id = b.car_maker_id;
    """
    rows = fetch(query)
    car_makers = []
    car_maker = None
    current_maker_id = None
    for row in rows:
        if current_maker_id != row['maker_id']:
            current_maker_id = row['maker_id']
            car_maker = {
                'id': row['maker_id'],
                'name': row['maker_name'],
                'cars': []
            }
            car_makers.append(car_maker)
        car_maker['cars'].append({
            'id': row['car_id'],
            'name': row['car_name']
        })
    # debugging
    # print(json.dumps(car_makers, indent=2))


@measure('Pattern: 2')
def pattern_2():
    # fetch car makers
    car_maker_query = 'SELECT id, name FROM car_makers;'
    rows = fetch(car_maker_query)
    car_makers = [{
        'id': row['id'],
        'name': row['name'],
        'cars': []
    } for row in rows]
    car_maker_ids = tuple([car_maker['id'] for car_maker in car_makers])

    # fettch cars
    car_query = """
        SELECT
            id,
            car_maker_id,
            name
        FROM cars
        WHERE car_maker_id in %s;
    """
    rows = fetch(car_query, (car_maker_ids,))
    for car_maker in car_makers:
        car_maker['cars'] = [{
            'id': row['id'],
            'name': row['name']
        } for row in rows if car_maker['id'] == row['car_maker_id']]
    # debugging
    # print(json.dumps(car_makers, indent=2, ensure_ascii=False))


if __name__ == '__main__':
    # joinして問い合わせてマッピングするパターン
    pattern_1()
    # joinせずに、個別に問い合わせてマッピングするパターン
    pattern_2()

measured_time.md

実行時間

単位: sec

-	パターン1	パターン2
1回目	0.0280	0.0485
2回目	0.0314	0.0495
3回目	0.0299	0.0487
最速	0.0280	0.0485
平均	0.0297	0.0498

query.sql

CREATE TABLE car_makers (
    id serial NOT NULL,
    name text NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE cars (
    id serial NOT NULL,
    car_maker_id integer NOT NULL,
    name text NOT NULL,
    PRIMARY KEY(id)
);

CREATE INDEX idx_car_maker_id ON cars (car_maker_id);

INSERT INTO car_makers (name) VALUES
('TOYOTA'),
('FIAT');

INSERT INTO cars (car_maker_id, name) VALUES
(1, 'カローラ'),
(1, 'クラウン'),
(1, '86'),
(2, '500'),
(2, 'PANDA'),
(2, 'PUNTO');