peeweeを使用したデータベースの操作 | pythonのORM

Peeweeについて

ORMフレームワークについて

ORMフレームワークについては以下の記事がわかりやすいです.

関係データベース(RDB)のレコードを、オブジェクトとして直感的に扱えるようにする。 また、RDBにアクセスするプログラムを書く際の煩雑な処理を軽減させ、 プログラマSQLを意識することなくプログラムを書ける。

https://qiita.com/yk-nakamura/items/acd071f16cda844579b9

model

from peewee import *
sqlite_db = SqliteDatabase('tutorial.sqlite3')


class BaseModel(Model):
    """A base model that will use our Sqlite database."""
    class Meta:
        database = sqlite_db


class Group(BaseModel):
    name = CharField()


class Member(BaseModel):
    name = CharField()
    group = ForeignKeyField(Group)

使い方

レコード保存

from peewee import *

sqlite_db = SqliteDatabase('tutorial.sqlite3')


class BaseModel(Model):
    """A base model that will use our Sqlite database."""

    class Meta:
        database = sqlite_db


class Group(BaseModel):
    name = CharField()


class Member(BaseModel):
    name = CharField()
    group = ForeignKeyField(Group)


sqlite_db.connect()
sqlite_db.create_tables([Member, Group])


with sqlite_db.atomic():
    for group_record in group_records:
        Group.create(name=group_record['name'])

# with sqlite_db.atomic():
#     for group_record in group_records:
#         Group.create(**group_record)

sqlite_db.close()

1対多(One-to-Many)のリレーションモデル

from peewee import *

sqlite_db = SqliteDatabase('tutorial.sqlite3')


class BaseModel(Model):
    """A base model that will use our Sqlite database."""

    class Meta:
        database = sqlite_db


class Group(BaseModel):
    name = CharField()


class Member(BaseModel):
    name = CharField()
    group = ForeignKeyField(Group, backref='members')


sqlite_db.connect()
sqlite_db.create_tables([Member, Group])

group_records = [{'name': "beatles"}, {"name": "bump"}]

with sqlite_db.atomic():
    for group_record in group_records:
        Group.create(name=group_record['name'])

beatles = Group.select().where(Group.name == 'beatles').first()
bump = Group.select().where(Group.name == 'bump').first()
member_records = [
    {'name': 'john', 'group_id': beatles},
    {'name': 'paul', 'group_id': beatles},
    {'name': 'fujiwara', 'group_id': bump},
    {'name': 'masukawa', 'group_id': bump}
]

with sqlite_db.atomic():
    for member_record in member_records:
        Member.create(**member_record)

# あるグループに所属しているメンバーが知りたい場合
group = Group.select().first()
for member in group.members:
    print(member.name)
# -----------------output-------------------
# john
# paul
# ------------------------------------------

# メンバーが所属しているグループが知りたい場合
member = Member.select().first()
for group in Group.select().where(Group.id == member):
    print(group.name)

# -----------------output-------------------
# beatles
# ------------------------------------------


# メンバーが所属しているグループが知りたい場合(INNER JOIN)
member = Member.select().first()
groups = Group.select().join(Member).where(Member.id == member.id)
for group in groups:
    print(group.name)
# -----------------output-------------------
# beatles
# ------------------------------------------

sqlite_db.close()

多対多(Many to Many)のリレーションモデル

from peewee import *

sqlite_db = SqliteDatabase('tutorial.sqlite3')


class BaseModel(Model):
    """A base model that will use our Sqlite database."""

    class Meta:
        database = sqlite_db


class Group(BaseModel):
    name = CharField()


class Member(BaseModel):
    name = CharField()
    group = ForeignKeyField(Group, backref='members')


class Office(BaseModel):
    name = CharField()


class OfficeGroup(BaseModel):
    group = ForeignKeyField(Group, backref='office_groups')
    office = ForeignKeyField(Office, backref='office_groups')


sqlite_db.connect()
sqlite_db.create_tables([Member, Group, Office, OfficeGroup])

group_records = [
    {'name': "beatles"},
    {"name": "bump"},
    {'name': 'amuro'}
]

with sqlite_db.atomic():
    for group_record in group_records:
        Group.create(name=group_record['name'])

beatles = Group.select().where(Group.name == 'beatles').first()
bump = Group.select().where(Group.name == 'bump').first()
member_records = [
    {'name': 'john', 'group_id': beatles},
    {'name': 'paul', 'group_id': beatles},
    {'name': 'fujiwara', 'group_id': bump},
    {'name': 'masukawa', 'group_id': bump}
]

with sqlite_db.atomic():
    for member_record in member_records:
        Member.create(**member_record)


office_records = [
    {'name': 'NEMS'},
    {'name': 'LONGFELLOW'},
    {'name': 'SONY'}
]

Office.insert_many(office_records).execute()


beatles = Group.select().where(Group.name == 'beatles').get()
bump = Group.select().where(Group.name == 'bump').get()

nems = Office.select().where(Office.name == 'NEMS').get()
long = Office.select().where(Office.name == 'LONGFELLOW').get()

office_group_records = [
    {'group_id': beatles, 'office_id': nems},
    {'group_id': beatles, 'office_id': long},
    {'group_id': bump, 'office_id': long},
]

OfficeGroup.insert_many(office_group_records).execute()


# beatlesが所属している事務所を知りたい場合
for office_group in beatles.office_groups:
    print(Office.select().where(Office.id == office_group.office_id).get().name)
# -----------------output-------------------
# NEMS
# LONGFELLOW
# ------------------------------------------

query = OfficeGroup.select(OfficeGroup, Group, Office).join(Group).switch(OfficeGroup).join(Office)

for i in query:
    print(i.group.name, i.office.name)

# -----------------output-------------------
# beatles NEMS
# beatles LONGFELLOW
# bump LONGFELLOW
# ------------------------------------------



sqlite_db.close()