DBアクセス

Djangoで、SQL的に言うとJOINしてデータとってくるところが、よくわからなかったので色々試してみたときのメモ。
ばぁーーって書いたので、間違ってるだろうし意味不明なので、参考にしないでください。

  • Member(メンバー)テーブル
  • Prefectural(都道府県)テーブル
  • Category(カテゴリ)テーブル

JOIN的なことは、以下のようにして取得する

>>> from xmlprj.display.models import Prefectural, Member

>>> m = Member.objects.all().select_related().get(pk=1) 

>>> m.member_name1
u'Name1'

>>> m.prefectural.prefectural_name
u'Hokkaido'

>>> m.category.category_name
u'Category1'


以下は、わからなかったときグダグダやってたメモ

1) 詳細情報を取得する

SQL

  • SQL文
    SELECT 
    	m.member_id,
    	m.member_name1,
    	p.prefectural_name
    FROM 
    	display_member AS m
    INNER JOIN 
    	display_prefectural AS p
    ON
    	p.prefectural_id = m.prefectural_id
    WHERE
    	m.member_id = 1
  • 結果
    -[ RECORD 1 ]----+---------
    member_id        | 1
    member_name1     | Name1
    prefectural_name | Hokkaido

Django

>>> from xmlprj.display.models import Prefectural, Member

>>> m = Member.objects.get(member_id=1)

>>> m.member_id
1

>>> m.member_name1
u'Name1'

>>> m.prefectural_id
1

>>> p = m.prefectural
>>> p.prefectural_name
u'Hokkaido'

2) 詳細情報を取得する

SQL

  • SQL文
    SELECT 
    	m.member_id,
    	m.member_name1,
    	p.prefectural_name,
    	c.category_name
    FROM 
    	display_member AS m
    INNER JOIN 
    	display_prefectural AS p
    ON
    	p.prefectural_id = m.prefectural_id
    LEFT JOIN 
    	display_category AS c
    ON
    	c.category_id = m.category_id
    WHERE
    	m.member_id = 1
  • 結果
    -[ RECORD 1 ]----+----------
    member_id        | 1
    member_name1     | Name1
    prefectural_name | Hokkaido
    category_name    | Category1

Django

>>> from xmlprj.display.models import Prefectural, Member

>>> m = Member.objects.get(member_id=1)

>>> m.member_id
1

>>> m.member_name1
u'Name1'

>>> p = m.prefectural
>>> m.prefectural_id
1

>>> p.prefectural_name
u'Hokkaido'

>>> m.category_id
1

>>> c = m.category
>>> c.category_name
u'Category1'

3) 情報を取得する

SQL

  • SQL文
    SELECT 
    	p.prefectural_name,
    	m.member_id,
    	m.member_name1
    FROM 
    	display_prefectural AS p
    INNER JOIN 
    	display_member AS m
    ON
    	m.prefectural_id = p.prefectural_id
    WHERE
    	p.prefectural_id = 1
  • 結果
    -[ RECORD 1 ]----+---------
    prefectural_name | Hokkaido
    member_id        | 1
    member_name1     | Name1
    -[ RECORD 2 ]----+---------
    prefectural_name | Hokkaido
    member_id        | 2
    member_name1     | Name3

Django

>>> from xmlprj.display.models import Prefectural, Category, Member

>>> p = Prefectural.objects.get(prefectural_id=1)

>>> p.prefectural_name
u'Hokkaido'

>>> p.p_members.count()
2L

>>> p.p_members.filter(member_delete_flag=False).values("member_id", "member_name1", "prefectural_id")
[
    {'member_name1': u'Name1', 'prefectural_id': 1, 'member_id': 1}, 
    {'member_name1': u'Name3', 'prefectural_id': 1, 'member_id': 2}
]


Model

from django.db import models
from datetime import datetime

class Prefectural(models.Model):
    prefectural_id = models.AutoField('prefectural id', primary_key=True)
    prefectural_name = models.CharField('prefectural name', max_length=100)
    prefectural_insert = models.DateTimeField('prefectural insert', default=datetime.now)
    prefectural_update = models.DateTimeField('prefectural update', default=datetime.now, auto_now=True)

    def __unicode__(self):
        return self.name

    class Admin:
        pass

class Category(models.Model):
    category_id = models.AutoField('category id', primary_key=True)
    category_name = models.CharField('category name', max_length=100)
    category_insert = models.DateTimeField('category insert', default=datetime.now)
    category_update = models.DateTimeField('category update', default=datetime.now, auto_now=True)

    def __unicode__(self):
        return self.name

    class Admin:
        pass

class Member(models.Model):
    member_id = models.AutoField('member id', primary_key=True)
    member_name1 = models.CharField('member name1', max_length=100)
    member_name2 = models.CharField('member name2', max_length=100)
    member_sex = models.IntegerField('member sex', default=1, null=True)
    prefectural = models.ForeignKey(Prefectural, related_name='p_members')
    category = models.ForeignKey(Category, related_name='c_members')
    member_address1 = models.CharField('member address1', max_length=255)
    member_address2 = models.CharField('member address2', max_length=255, null=True)
    member_delete_flag = models.BooleanField('member delete flag', default=False)
    member_insert = models.DateTimeField('member insert', default=datetime.now)
    member_update = models.DateTimeField('member update', default=datetime.now, auto_now=True)

    def __unicode__(self):
        return self.name

    class Admin:
        pass


Comment



Counter: 13292, today: 5, yesterday: 0

トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS
Last-modified: 2013-09-24 (火) 14:56:27 (1461d)