def limit_offset_step(self):
# 始まり1、終わり3、step2
b = Book.objects.all()[:3:2]
#=> SELECT * FROM "runner_book" LIMIT 3
dump_pk(b)
dump_sql()
def case_sum(self):
# ratingが3より大きい本のうち、ratingごとに、出版社1・出版社2別の冊数を算出する
# インスパイア: [CASE式のススメ](http://www.geocities.jp/mickindex/database/db_case.html)
b = Book.objects.values('rating').filter(rating__gt=3).annotate(
# defalutがあるので、then=0はいらないけど、見栄え上残しておく
pub1=Sum(
Case(When(rating=5, then=1),
When(rating=4, then=0),
default=Value(0),
output_field=IntegerField())),
pub2=Sum(
Case(When(rating=5, then=0),
When(rating=4, then=1),
default=Value(0),
output_field=IntegerField())))
#=> SELECT "runner_book"."rating",
# SUM(CASE WHEN "runner_book"."rating" = %s THEN %s
# WHEN "runner_book"."rating" = %s THEN %s ELSE %s END) AS "pub2",
# SUM(CASE WHEN "runner_book"."rating" = %s THEN %s
# WHEN "runner_book"."rating" = %s THEN %s ELSE %s END) AS "pub1"
# FROM "runner_book"
# WHERE "runner_book"."rating" > %s
# GROUP BY "runner_book"."rating"
# LIMIT 21' - PARAMS = (5.0, 0, 4.0, 1, 0, 5.0, 1, 4.0, 0, 0, 3.0)
print(b)
dump_sql()
def all_avg(self):
# 集計結果には明示的な名前を付けない
b = Book.objects.all().aggregate(Avg('price'))
#=> SELECT AVG("runner_book"."price") AS "price__avg" FROM "runner_book"
print(b)
dump_sql()
def limit_offset(self):
# 2-3番目を取り出す
b = Book.objects.all()[1:3]
#=> SELECT * FROM "runner_book" LIMIT 2 OFFSET 1
dump_pk(b)
dump_sql()
def all_sum(self):
# 集計結果に対し、明示的に名前をつける
b = Book.objects.all().aggregate(sum_price=Sum('price'))
#=> SELECT SUM("runner_book"."price") AS "sum_price" FROM "runner_book"
print(b)
dump_sql()
def where_and(self):
b = Book.objects.all().filter(pubdate='2015-10-01', publisher=1).values()
#=> 'SELECT * FROM "runner_book"
# WHERE ("runner_book"."publisher_id" = %s AND "runner_book"."pubdate" = %s)' - PARAMS = (1, '2015-10-01')
print(b)
dump_sql()
def where_or(self):
b = Book.objects.all().filter(Q(pubdate='2015-10-01') | Q(publisher=1)).values()
#=> 'SELECT * FROM "runner_book"
# WHERE ("runner_book"."pubdate" = %s OR "runner_book"."publisher_id" = %s)' - PARAMS = ('2015-10-01', 1)
print(b)
dump_sql()
def case_sum(self):
# ratingが3より大きい本のうち、ratingごとに、出版社1・出版社2別の冊数を算出する
# インスパイア: [CASE式のススメ](http://www.geocities.jp/mickindex/database/db_case.html)
b = Book.objects.values('rating').filter(rating__gt=3).annotate(
# defalutがあるので、then=0はいらないけど、見栄え上残しておく
pub1=Sum(Case(
When(rating=5, then=1),
When(rating=4, then=0),
default=Value(0),
output_field=IntegerField()
)),
pub2=Sum(Case(
When(rating=5, then=0),
When(rating=4, then=1),
default=Value(0),
output_field=IntegerField()
))
)
#=> SELECT "runner_book"."rating",
# SUM(CASE WHEN "runner_book"."rating" = %s THEN %s
# WHEN "runner_book"."rating" = %s THEN %s ELSE %s END) AS "pub2",
# SUM(CASE WHEN "runner_book"."rating" = %s THEN %s
# WHEN "runner_book"."rating" = %s THEN %s ELSE %s END) AS "pub1"
# FROM "runner_book"
# WHERE "runner_book"."rating" > %s
# GROUP BY "runner_book"."rating"
# LIMIT 21' - PARAMS = (5.0, 0, 4.0, 1, 0, 5.0, 1, 4.0, 0, 0, 3.0)
print(b)
dump_sql()
def limit_offset_step(self):
# 始まり1、終わり3、step2
b = Book.objects.all()[:3:2]
#=> SELECT * FROM "runner_book" LIMIT 3
dump_pk(b)
dump_sql()
def select_all(self):
b = Book.objects.all().values()
#=> 'SELECT * FROM "runner_book"'
# 注) 実際は列名が全部列挙されるけど、スペースの関係上`*`で表記
print(b)
dump_sql()
def delete_filter(self):
'''条件一致のみ削除'''
Deletion.objects.filter(name='foo').delete()
dump_sql()
#=> 'DELETE FROM "runner_deletion" WHERE "runner_deletion"."name" = %s' - PARAMS = ('foo',)
print(Deletion.objects.all().values()) #=> []
def delete_all(self):
'''全件削除'''
Deletion.objects.all().delete()
dump_sql()
#=> DELETE FROM "runner_deletion"
print(Deletion.objects.all().values()) #=> []
def limit_offset(self):
# 2-3番目を取り出す
b = Book.objects.all()[1:3]
#=> SELECT * FROM "runner_book" LIMIT 2 OFFSET 1
dump_pk(b)
dump_sql()
def where_and(self):
b = Book.objects.all().filter(pubdate='2015-10-01',
publisher=1).values()
#=> 'SELECT * FROM "runner_book"
# WHERE ("runner_book"."publisher_id" = %s AND "runner_book"."pubdate" = %s)' - PARAMS = (1, '2015-10-01')
print(b)
dump_sql()
def where_or(self):
b = Book.objects.all().filter(
Q(pubdate='2015-10-01') | Q(publisher=1)).values()
#=> 'SELECT * FROM "runner_book"
# WHERE ("runner_book"."pubdate" = %s OR "runner_book"."publisher_id" = %s)' - PARAMS = ('2015-10-01', 1)
print(b)
dump_sql()
def delete_set_null(self):
'''nullをセットする削除'''
dump_related_models(SetNullKey)
#=> related_model: [{'id': 1, 'name': 'set_null'}]
# Deletion_model: [{'name': 'foo', 'id': 6, 'set_null_row_id': 1}]
SetNullKey.objects.all().delete()
dump_sql()
#=> 'DELETE FROM "runner_setnullkey" WHERE "runner_setnullkey"."id" IN (%s)' - PARAMS = (1,)
dump_related_models(SetNullKey)
def delete_set(self):
'''on_deleteで設定したSETの結果をセットする削除'''
dump_related_models(SetKey)
#=> related_model: [{'id': 3, 'name': 'set_row'}]
# Deletion_model: [{'id': 9, 'name': 'foo', 'set_key_row_id': 3}]
SetKey.objects.filter(name='set_row').delete()
dump_sql()
#=> 'DELETE FROM "runner_setkey" WHERE "runner_setkey"."id" IN (%s)' - PARAMS = (3,)
dump_related_models(SetKey)
def delete_nothing(self):
'''何も値を変更しない削除'''
dump_related_models(DoNothingKey)
#=> related_model: [{'id': 4, 'name': 'do_nothing'}]
# Deletion_model: [{'do_nothing_row_id': 4, 'id': 11, 'name': 'foo'}]
DoNothingKey.objects.filter(name='do_nothing').delete()
dump_sql()
#=> 'DELETE FROM "runner_donothingkey" WHERE "runner_donothingkey"."name" = %s' - PARAMS = ('do_nothing',)
dump_related_models(DoNothingKey)
def delete_set_default(self):
'''Deletionのdefault値をセットする削除'''
dump_related_models(SetDefaultKey)
#=> related_model: [{'name': 'set_default', 'id': 11}, {'name': 'on_default', 'id': 12}]
# Deletion_model: [{'id': 8, 'name': 'foo', 'set_default_row_id': 11}]
SetDefaultKey.objects.filter(name='set_default').delete()
dump_sql()
#=> 'DELETE FROM "runner_setdefaultkey" WHERE "runner_setdefaultkey"."id" IN (%s)' - PARAMS = (11,)
dump_related_models(SetDefaultKey)
def delete_cascade(self):
'''cascadeな削除'''
dump_related_models(CascadeKey)
#=> related_model: [{'name': 'cascade', 'id': 3}]
# Deletion_model: [{'cascade_row_id': 3, 'name': 'foo', 'id': 5}] *関係する部分のみ
CascadeKey.objects.all().delete()
dump_sql()
#=> 'DELETE FROM "runner_cascadekey" WHERE "runner_cascadekey"."id" IN (%s)' - PARAMS = (1,)
dump_related_models(CascadeKey)
def multi_join(self):
a = Author.objects.filter(book__publisher__name='Pub2').values()
#=> SELECT "runner_author"."id", "runner_author"."name", "runner_author"."age"
# FROM "runner_author"
# INNER JOIN "runner_book_authors" ON ( "runner_author"."id" = "runner_book_authors"."author_id" )
# INNER JOIN "runner_book" ON ( "runner_book_authors"."book_id" = "runner_book"."id" )
# INNER JOIN "runner_publisher" ON ( "runner_book"."publisher_id" = "runner_publisher"."id" )
# WHERE "runner_publisher"."name" = %s
# LIMIT 21 - PARAMS = ('Pub2',)
print(a)
dump_sql()
def extra_sum(self):
# SQLiteの関数`strftime`を使うために、extraを使った例
b = Book.objects.all()\
.extra(select={'month': "strftime('%m', pubdate)"})\
.values('month')\
.annotate(sum_price=Sum('price'))
#=> SELECT (strftime(\'%m\', pubdate)) AS "month", SUM("runner_book"."price") AS "sum_price"
# FROM "runner_book"
# GROUP BY (strftime(\'%m\', pubdate))
print(b)
dump_sql()
def extra_max(self):
# SQLiteの関数`strftime`を使うために、extraを使った例
# monthの値をstringからintにキャスト
b = Book.objects.all()\
.extra(select={'month': "cast(strftime('%m', pubdate) AS integer)"})\
.values('month')\
.annotate(max_price=Max('price'))
#=> SELECT (cast(strftime(\'%m\', pubdate) AS integer)) AS "month", MAX("runner_book"."price") AS "max_price"
# FROM "runner_book"
# GROUP BY (cast(strftime(\'%m\', pubdate) AS integer))
print(b)
dump_sql()
def backward_join(self):
# p = Author.objects.filter(age__gt=5)や
# p = Author.objects.all() だと逆引きできない
a = Author.objects.get(pk=1).book_set.all().values()
#=> SELECT "runner_book"."id", "runner_book"."name", "runner_book"."pages", "runner_book"."price",
# "runner_book"."rating", "runner_book"."publisher_id", "runner_book"."pubdate"
# FROM "runner_book"
# INNER JOIN "runner_book_authors" ON ( "runner_book"."id" = "runner_book_authors"."book_id" )
# WHERE "runner_book_authors"."author_id" = %s
# LIMIT 21 - PARAMS = (1,)
print(a)
dump_sql()
def avg(self):
# ポイント:
# ・`GROUP BY`する場合、values.annotate の順で書く
# ・集約キーはvalues内に書く
# ・annotateで新しい集約列を追加
# `GROUP BY`されない例
# books = Book.objects.annotate(price_avg=Avg('price')).values('publisher_id')
b = Book.objects.all().values('publisher_id').annotate(price_avg=Avg('price'))
#=> SELECT "runner_book"."publisher_id", AVG("runner_book"."price") AS "price_avg"
# FROM "runner_book"
# GROUP BY "runner_book"."publisher_id"
print(b)
dump_sql()
def case_when(self):
# name列とcase式で作成したimpression列を表示
b = Book.objects.annotate(
impression=Case(When(pages=10, then=Value('short')),
When(pages=20, then=Value('short')),
When(pages=30, then=Value('good')),
When(pages=40, then=Value('long')),
default=Value('nothing'),
output_field=CharField())).values(
'name', 'impression')
#=> SELECT "runner_book"."name",
# CASE WHEN "runner_book"."pages" = %s THEN %s
# WHEN "runner_book"."pages" = %s THEN %s
# WHEN "runner_book"."pages" = %s THEN %s
# WHEN "runner_book"."pages" = %s THEN %s
# ELSE %s END AS "impression"
# FROM "runner_book"
# LIMIT 21' - PARAMS = (10, 'short', 20, 'short', 30, 'good', 40, 'long', 'nothing')
print(b)
dump_sql()
def left_join(self):
# id IS NULL
# p1 = Publisher.objects.filter(book__isnull=False) # これだと、INNER JOIN になる
p1 = Publisher.objects.filter(book__isnull=True).values()
#=> SELECT *
# FROM "runner_publisher"
# LEFT OUTER JOIN "runner_book" ON ( "runner_publisher"."id" = "runner_book"."publisher_id" )
# WHERE "runner_book"."id" IS NULL
print(p1)
dump_sql()
# publisher_id IS NULL
p2 = Publisher.objects.filter(book__publisher__isnull=True).values()
#=> SELECT *
# FROM "runner_publisher"
# LEFT OUTER JOIN "runner_book" ON ( "runner_publisher"."id" = "runner_book"."publisher_id" )
# WHERE "runner_book"."publisher_id" IS NULL
print(p2)
dump_sql()
def case_when(self):
# name列とcase式で作成したimpression列を表示
b = Book.objects.annotate(
impression=Case(
When(pages=10, then=Value('short')),
When(pages=20, then=Value('short')),
When(pages=30, then=Value('good')),
When(pages=40, then=Value('long')),
default=Value('nothing'),
output_field=CharField()
)
).values('name', 'impression')
#=> SELECT "runner_book"."name",
# CASE WHEN "runner_book"."pages" = %s THEN %s
# WHEN "runner_book"."pages" = %s THEN %s
# WHEN "runner_book"."pages" = %s THEN %s
# WHEN "runner_book"."pages" = %s THEN %s
# ELSE %s END AS "impression"
# FROM "runner_book"
# LIMIT 21' - PARAMS = (10, 'short', 20, 'short', 30, 'good', 40, 'long', 'nothing')
print(b)
dump_sql()
def inner_join(self):
# 微妙に違うSQLが出される
# INNER JOINするけど、SELECT句にはpublisherの列は無い
b1 = Book.objects.filter(publisher__num_awards=5).values()
#=> SELECT "runner_book"."id", "runner_book"."name", "runner_book"."pages", "runner_book"."price",
# "runner_book"."rating", "runner_book"."publisher_id", "runner_book"."pubdate"
# FROM "runner_book"
# INNER JOIN "runner_publisher" ON ( "runner_book"."publisher_id" = "runner_publisher"."id" )
# WHERE "runner_publisher"."num_awards" = %s
# LIMIT 21 - PARAMS = (5,)
print(b1)
dump_sql()
# INNER JOINし、SELECT句にもpublisherの列がある
b2 = Book.objects.select_related().all().values()
#=> SELECT "runner_book"."id", "runner_book"."name", "runner_book"."pages", "runner_book"."price",
# "runner_book"."rating", "runner_book"."publisher_id", "runner_book"."pubdate",
# "runner_publisher"."id", "runner_publisher"."name", "runner_publisher"."num_awards"
# FROM "runner_book"
# INNER JOIN "runner_publisher" ON ( "runner_book"."publisher_id" = "runner_publisher"."id" )
# LIMIT 21 - PARAMS = ()
print(b2)
dump_sql()
# INNER JOINとWHEREを使って、全列出す
b3 = Book.objects.filter(publisher__num_awards=5).select_related().all().values()
#=> SELECT "runner_book"."id", "runner_book"."name", "runner_book"."pages", "runner_book"."price",
# "runner_book"."rating", "runner_book"."publisher_id", "runner_book"."pubdate",
# "runner_publisher"."id", "runner_publisher"."name", "runner_publisher"."num_awards"
# FROM "runner_book"
# INNER JOIN "runner_publisher" ON ( "runner_book"."publisher_id" = "runner_publisher"."id" )
# WHERE "runner_publisher"."num_awards" = %s
# LIMIT 21 - PARAMS = (5,)
print(b3)
dump_sql()
def select_particular_column(self):
b = Book.objects.all().values('name')
#=> SELECT "runner_book"."name" FROM "runner_book"
print(b)
dump_sql()
def where_not_equal(self):
b = Book.objects.all().exclude(pubdate='2015-10-01').values()
#=> 'SELECT * FROM "runner_book" WHERE NOT ("runner_book"."pubdate" = %s)' - PARAMS = ('2015-10-01',)]
print(b)
dump_sql()
def where_equal(self):
b = Book.objects.all().filter(pubdate='2015-10-01').values()
#=> 'SELECT * FROM "runner_book" WHERE "runner_book"."pubdate" = %s' - PARAMS = ('2015-10-01',)
print(b)
dump_sql()
def where_not_equal(self):
b = Book.objects.all().exclude(pubdate='2015-10-01').values()
#=> 'SELECT * FROM "runner_book" WHERE NOT ("runner_book"."pubdate" = %s)' - PARAMS = ('2015-10-01',)]
print(b)
dump_sql()
def where_contain(self):
b = Book.objects.all().filter(name__contains='d').values()
#=> 'SELECT * FROM "runner_book" WHERE "runner_book"."name" LIKE %s ESCAPE \'\\\'' - PARAMS = ('%d%',)
print(b)
dump_sql()
def limit_only(self):
b = Book.objects.all()[:2]
#=> SELECT * FROM "runner_book" LIMIT 2
dump_pk(b)
dump_sql()
def order_by_desc(self):
b = Book.objects.all().order_by('-pages').values()
#=> SELECT * FROM "runner_book" ORDER BY "runner_book"."pages" DESC - PARAMS = ()
print(b)
dump_sql()
def where_contain(self):
b = Book.objects.all().filter(name__contains='d').values()
#=> 'SELECT * FROM "runner_book" WHERE "runner_book"."name" LIKE %s ESCAPE \'\\\'' - PARAMS = ('%d%',)
print(b)
dump_sql()
def where_equal(self):
b = Book.objects.all().filter(pubdate='2015-10-01').values()
#=> 'SELECT * FROM "runner_book" WHERE "runner_book"."pubdate" = %s' - PARAMS = ('2015-10-01',)
print(b)
dump_sql()
def limit_only(self):
b = Book.objects.all()[:2]
#=> SELECT * FROM "runner_book" LIMIT 2
dump_pk(b)
dump_sql()