peeweeを使用したデータベースの操作 | pythonのORM
Peeweeについて
ORMフレームワークについて
ORMフレームワークについては以下の記事がわかりやすいです.
関係データベース(RDB)のレコードを、オブジェクトとして直感的に扱えるようにする。 また、RDBにアクセスするプログラムを書く際の煩雑な処理を軽減させ、 プログラマはSQLを意識することなくプログラムを書ける。
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()