django query 기초2

 

 

1. 일반 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 "<input>", line 1, in <module>
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

 

 

 

 

---