django query 기초2

일반 ORM

모델 정의

class Country(models.Model): name = models.CharField(max_length=30) class City(models.Model): name = models.CharField(max_length=30) country = models.ForeignKey(Country, on_delete=models.CASCADE, relatec[ame='cities') population = models.PositiveIntegerField()

데이터 추가

>>> temp = ["Brazil","Turkey","Italy","Bangladesh","Canada","France","Peru","Argentina","Nigeria","Australia","Iran","Singapore","China","Chile","Thailand","Germany","Spain","Philippines","Indonesia","United States","South Korea","Pakistan","Angola","Mexico","India","United Kingdom","Colombia","Taiwan","Japan"] >>> bulk_country = [] >>> for c in temp: new_country = Country() new_country.name = c bulk_country.append(new_country) >>> Country.objects.bulk_create(bulk_country) >>> temp = [("Tokyo", 28, 36923000),("Shanghai", 13, 34000000),("Jakarta", 19, 30000000),("Seoul", 21, 25514000),("Guangzhou", 13, 25000000),("Beijing", 13, 24900000),("Karachi", 22, 24300000),("Shenzhen", 13, 23300000),("Delhi", 25, 21753486),("Mexico City", 24, 21339781),("Lagos", 9, 21000000),("São Paulo", 1, 20935204),("Mumbai", 25, 20748395),("New York City", 20, 20092883),("Osaka", 28, 19342000),("Wuhan", 13, 19000000),("Chengdu", 13, 18100000),("Dhaka", 4, 17151925),("Chongqing", 13, 17000000),("Tianjin", 13, 15400000),("Kolkata", 25, 14617882),("Tehran", 11, 14595904),("Istanbul", 2, 14377018),("London", 26, 14031830),("Hangzhou", 13, 13400000),("Los Angeles", 20, 13262220),("Buenos Aires", 8, 13074000),("Xian", 13, 12900000),("Paris", 6, 12405426),("Changzhou", 13, 12400000),("Shantou", 13, 12000000),("Rio de Janeiro", 1, 11973505),("Manila", 18, 11855975),("Nanjing", 13, 11700000),("Rhine-Ruhr", 16, 11470000),("Jinan", 13, 11000000),("Bangalore", 25, 10576167),("Harbin", 13, 10500000),("Lima", 7, 9886647),("Zhengzhou", 13, 9700000),("Qingdaoo", 13, 9600000),("Chicago", 20, 9554598),("Nagoya", 28, 9107000),("Chennai", 25, 8917749),("Bangkok", 15, 8305218),("Bogota", 27, 7878783),("Hyderabad", 25, 7749334),("Shenyang", 13, 7700000),("Wenzhou", 13, 7600000),("Nanchang", 13, 7400000),("Hong Kong", 13, 7298600),("Taipei", 29, 7045488),("Dallas–Fort Worth", 20, 6954330),("Santiago", 14, 6683852),("Luanda", 23, 6542944),("Houston", 20, 6490180),("Madrid", 17, 6378297),("Ahmedabad", 25, 6352254),("Toronto", 5, 6055724),("Philadelphia", 20, 6051170),("Washington DC", 20, 6033737),("Miami", 20, 5929819),("Belo Hori zonte", 1, 5767414),("Atlanta", 20, 5614323),("Singapore", 12, 5535000),("Barcelona", 17, 5445616),("Munich", 16, 5203738),("Stuttgart", 16, 5200000),("Ankara", 2, 5150072),("Hamburg", 16, 5100000),("Pune", 25, 5049968),("Berlin", 16, 5005216),("Guadalajara", 24, 4796050),("Boston", 20, 4732161),("Sydney", 10, 5000500),("San Francisco", 20, 4594060),("Surat", 25, 4585367),("Phoenix", 20, 4489109),("Monterrey", 24, 4477614),("Inland Empire", 20, 4441890),("Rome", 3, 4321244),("Detroit", 20, 4296611),("Milan", 3, 4267946),("Melbourne", 10, 4650000)] >>> bulk_city = [] >>> for city in temp: new_city= City() new_city.name = city[0] new_city.country = Country.objects.get(id=city[1]) new_city.population = city[2] bulk_city.append(new_city) >>> City.objects.bulk_create(bulk_city)

Like 검색

>>> city = City.objects.filter(name__contains='c') >>> city.query >>> for c in city: print(c.__dict__) >>> c = City.objects.filter(country__name__contains='c')

모든 City의 population의 합

>>> from django.db.models import Sum >>> city_sum = City.objects.aggregate(Sum('population')) Sql: select sum(population) from polls_city;

모든 City의 population의 평균

>>> from django.db.models import Avg >>> city_avg = City.objects.aggregate(population_avg=Avg('population')) Sql: select avg(population) from polls_city

모든 City 개수 && 나라별 City 개수

>>> City.objects.count() >>> from Django.db.models import Count >>> c = Country.objects.annotate(Count('cities'))

필드 지정

>>> c = City.objects.values('name', 'country__name') >>> c[0].__dict__ Traceback (most recent call last): File "", line 1, in AttributeError: 'dict' object has no attribute '__dict__' >>> c[0]

Alias

>>> from django.db.models import F >>> c = City.objects.values('name', country_name=F('country__name')) .extra() is going to be deprecated Django >= 1.8

나라별 population ( 출력 : 나라 이름, 인구수 합 )

>>> for country in Country.objects.all(): result = City.objects.filter(country=country).aggregate(Sum('population')) print '{}: {}'.format(country.name, result['population__sum'])

-> n번 호출!!!! 안되!!!!

>>> City.objects.values('country__name').annotate(Sum('population')) >>> City.objects.values('country__name') \ .annotate(country_population=Sum('population')) \ .order_by('-country_population')

Sql : SELECT "core_country"."name", SUM("core_city"."population") AS "country_population"

FROM "core_city" INNER JOIN "core_country" ON ("core_city"."country_id" = "core_country"."id")

GROUP BY "core_country"."name"

ORDER BY "country_population" DESC

>>> Country.objects.annotate(Sum('cities__population'))

인구수가 50000000 이상인 도시를 내림차순

>>> City.objects.values('country__name') \ .annotate(country_population=Sum('population')) \ .filter(country_population__gt=50000000) \ .order_by('-country_population') >>> queryset = City.objects.values('country__name').annotate(country_population=Sum('population')) >>> queryset.filter(country_population__gt=50000000).order_by('-country_population')

Sql : SELECT "core_country"."name", SUM("core_city"."population") AS "country_population"

FROM "core_city" INNER JOIN "core_country" ON ("core_city"."country_id" = "core_country"."id")

GROUP BY "core_country"."name" HAVING SUM("core_city"."population") > 50000000

ORDER BY "country_population" DESC

from http://uiandwe.tistory.com/1254 by ccl(A) rewrite - 2020-03-06 05:20:21