単位: 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 |
"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' | |
}, | |
] | |
}, | |
] |
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() |
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'); |