def count_user_core_fixed():
u = User.__table__.c
t = User.__table__.c
sel = select([func.distinct(t.id), t.name, func.count()])\
.select_from(User.__table__.join(Team.__table__, t.id == u.team_id))\
.where(u.age < 50).group_by(t.id)
counts = {r[1]: r[2] for r in database.session().execute(sel)}
return counts
def count_user_core():
teams = select_teams_core()
sess = lambda sel: database.session().execute(sel)
u = User.__table__.c
counts = {tm['name']: sess(
select([func.count()]).select_from(User.__table__)\
.where(and_(u.team_id == tm['id'], u.age < 50))\
).scalar() for tm in teams}
return counts
def select_user_core():
u = User.__table__.c
t = Team.__table__.c
sel = select([u.id, u.name, u.age, t.name])\
.select_from(User.__table__.join(Team.__table__, t.id == u.team_id))\
.order_by(desc(u.age)).limit(limit)
result = [{'id': r[0], 'name': r[1], 'age': r[2], 'team': r[3]}
for r in database.session().execute(sel)]
return result
def select_user_multi():
u = User.__table__.c
t = Team.__table__.c
sel = select([u.id, u.name, u.age, t.name])\
.select_from(User.__table__.join(Team.__table__, t.id == u.team_id))\
.order_by(desc(u.age)).limit(limit)
p = Pool(multi.cpu_count())
result = p.map(get_user, database.session().execute(sel))
p.close()
return result
def select_user_team(team):
global _session
u = User.__table__.c
session = _session if _session else database.session()
sel = select([u.id, u.name, u.age]).select_from(User.__table__)\
.where(u.team_id == team['id'])\
.order_by(desc(u.age)).limit(limit)
users = [{'id': r[0], 'name': r[1], 'age': r[2]}
for r in session.execute(sel)]
return {'id': team['id'],
'name': team['name'],
'users': users}
def select_user_team_core():
u = User.__table__.c
teams = select_teams_core()
query = lambda team_id:\
select([u.id, u.name, u.age]).select_from(User.__table__)\
.where(u.team_id == team_id)\
.order_by(desc(u.age)).limit(limit)
res_list = [database.session().execute(query(tm['id'])) for tm in teams]
result = [{'id': t['id'],
'name': t['name'],
'users': [{'id': r[0], 'name': r[1], 'age': r[2]}
for r in res]}
for t,res in zip(teams, res_list)]
return result
def multi_insertion(data_list, team_list):
teams = [Team(t) for t in team_list]
database.session().add_all(teams)
database.session().commit()
teams = Team.query().all()
team_dict = {t.name: t for t in teams}
# Parallelにorm objectは渡せない
# users = Parallel(n_jobs=-1)([delayed(User)
# (d[0], d[1], team_dict[d[2]]) for d in data_list])
users = [User(d[0], d[1], team_dict[d[2]]) for d in data_list]
start = time.time()
database.session().add_all(users)
database.session().commit()
header = 'SqlAlchemy ORM multi insert:'
print('{0} elapsed time of insertion: {1:.3f} [sec]'.format(
header,
time.time() - start))
def bulk_insertion(data_list, team_list):
teams = [Team(t) for t in team_list]
database.session().add_all(teams)
database.session().commit()
teams = Team.query().all()
team_dict = {t.name: t for t in teams}
start = time.time()
# database.session().bulk_save_objects(users, return_defaults=True)
database.session().bulk_save_objects([
UserTable(name=d[0],
age=d[1],
team_id=team_dict[d[2]].id,
updated_at=time.time(),
created_at=time.time()) for d in data_list
],
return_defaults=True)
database.session().commit()
header = 'SqlAlchemy ORM bulk insert:'
print('{0} elapsed time of insertion: {1:.3f} [sec]'.format(
header,
time.time() - start))
def core_insertion(data_list, team_list, size=None):
teams = [Team.create_dict(t) for t in team_list]
database.session().execute(Team.__table__.insert(), teams)
database.session().commit()
team_dict = {t: i + 1 for i, t in enumerate(team_list)}
users = Parallel(n_jobs=-1)([
delayed(User.create_dict)(d[0], d[1], team_dict[d[2]])
for d in data_list
])
#users = [User.create_dict(d[0], d[1], team_dict[d[2]]) for d in data_list]
start = time.time()
if not size:
database.session().execute(User.__table__.insert(), users)
database.session().commit()
header = 'SqlAlchemy core bulk insert:'
print('{0} elapsed time of insertion: {1:.3f} [sec]'.format(
header,
time.time() - start))
else:
chunked_data = chunked(users, size)
[
database.session().execute(User.__table__.insert(), cd)
for cd in chunked_data
]
database.session().commit()
header = 'SqlAlchemy core bulk insert ({0}):'.format(size)
print('{0} elapsed time of insertion: {1:.3f} [sec]'.format(
header,
time.time() - start))
def insert_user(name, age, team):
u = User(name, age, team)
database.session().add(u)
database.session().commit()
def insert_team(team_name):
t = Team(team_name)
database.session().add(t)
database.session().commit()
return Team.query().filter(Team.name == team_name).first()
def select_teams_core():
t = Team.__table__.c
sel = select([t.id, t.name]).select_from(Team.__table__)
res = database.session().execute(sel)
result = [{'id': r[0], 'name': r[1]} for r in res]
return result
def query(cls):
if not hasattr(cls, "_query"):
cls._query = database.session().query_property()
return cls._query