Python/Django/DBアクセス

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 †