query = "SELECT Company.*, COUNT(User.company_id) AS user_cnt, personal.personal_sum, team.team_sum, personal_team.personal_team_sum, cold.cold_sum"
query += " FROM Company"
query += " LEFT OUTER"
query += " JOIN User ON Company.id = User.company_id"
query += " LEFT OUTER"
query += " JOIN ("
query += " SELECT `Drive`.`company_id`, SUM(`Drive`.`usage_total`) AS `personal_sum`"
query += " FROM `Drive`"
query += " WHERE `Drive`.`drive_type` IN ('PERSONAL','DEVICE')"
query += " GROUP BY `Drive`.`company_id`"
query += " ORDER BY NULL) personal ON personal.company_id = Company.id"
query += " LEFT OUTER"
query += " JOIN ("
query += " SELECT `Drive`.`company_id`, SUM(`Drive`.`usage_total`) AS `team_sum`"
query += " FROM `Drive`"
query += " WHERE `Drive`.`drive_type` = 'TEAM'"
query += " GROUP BY `Drive`.`company_id`"
query += " ORDER BY NULL) team ON team.company_id = Company.id"
query += " LEFT OUTER"
query += " JOIN ("
query += " SELECT `Drive`.`company_id`, SUM(`Drive`.`usage_total`) AS `personal_team_sum`"
query += " FROM `Drive`"
query += " WHERE `Drive`.`drive_type` IN ('PERSONAL','DEVICE','TEAM')"
query += " GROUP BY `Drive`.`company_id`"
query += " ORDER BY NULL) personal_team ON personal_team.company_id = Company.id"
query += " LEFT OUTER"
query += " JOIN ("
query += " SELECT `company_id`, SUM(`ColdFile`.`content_length`) AS `cold_sum`"
query += " FROM `ColdFile`"
query += " GROUP BY `ColdFile`.`company_id`"
query += " ORDER BY NULL) cold ON cold.company_id = Company.id"
query += " GROUP BY `Company`.`NAME`"
query += " ORDER BY `Company`.`created_date`"
dict를 만들기 위해서 제가 필요한건 컬럼 이름입니다.
with connections["daily_lab_db"].cursor() as cursor:
cursor.execute("select * from tb_p2pnotice_header")
desc = cursor.description
columns = [col[0] for col in desc]
print(
[dict(zip(columns, row))
for row in cursor.fetchall()])
결과를 보자면
[{"col1": "value1"}, {"col2": "value2"}, ... {"colN": "valueN"}]
list comprehension을 통해서 dict를 담은 리스트로 만들었습니다.
이렇게 하면 Serializer를 사용하지 않아도 list 로 보낼수 있습니다.
|
ip(반복가능한객체, ...) |
반복 가능한 객체 여러 개를 넣으면 요소 순서대로 튜플로 묶어서 zip 객체를 반환 예) list(zip([1, 2, 3], [97, 98, 99]))는 [(1, 97), (2, 98), (3, 99)] |
열을 미리 모르는 경우 cursor.description 을 사용하여 열 이름 목록을 만들고 각 행에 Zip 을 사용하여 사전 목록을 생성하십시오. 예에서는 연결 및 쿼리가 작성되었다고 가정합니다.
>>> cursor = connection.cursor().execute(sql)
>>> columns = [column[0] for column in cursor.description]
>>> print(columns) ['name', 'create_date']
>>> results = []
>>> for row in cursor.fetchall(): ... results.append(dict(Zip(columns, row))) ...
>>> print(results)
[{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'master'},
{'create_date': datetime.datetime(2013, 1, 30, 12, 31, 40, 340000), 'name': u'tempdb'},
{'create_date': datetime.datetime(2003, 4, 8, 9, 13, 36, 390000), 'name': u'model'},
{'create_date': datetime.datetime(2010, 4, 2, 17, 35, 8, 970000), 'name': u'msdb'}]