Symfoware

Symfowareについての考察blog

SQLAlchemyの使い方5 テーブル間のリレーション

SQLAlchemyの使い方を勉強してます。

前回に引き続き、チュートリアルに沿って進めてみます。
http://docs.sqlalchemy.org/en/latest/orm/tutorial.html



現在は、studentsテーブルとstudents_id_seqというシーケンスがある状態。
studentsの内容は以下のとおりです。


sample=# select * from students;
id |        name        |         kana        
----+--------------------+--------------------------
18 | 西住 みほ    | ニシズミ ミホ
19 | 武部 沙織    | タケベ サオリ
20 | 五十鈴 華    | イスズ ハナ
21 | 秋山 優花里 | アキヤマ ユカリ
22 | 冷泉 麻子    | レイゼイ マコ







Building a Relationship



見様見真似でリレーションを定義してみます。
生徒のメールアドレスを保持するテーブル「Address」を作成。

id,email_address,students_idという3つのフィールドを持ちます。

id:シーケンス
email_address:生徒のメールアドレス
students_id:students.idと紐付


プログラムはこのようになりました。


  1. # -*- coding:utf-8 -*-
  2. import sqlalchemy
  3. import sqlalchemy.orm
  4. import sqlalchemy.ext.declarative
  5. Base = sqlalchemy.ext.declarative.declarative_base()
  6. class Student(Base):
  7.     __tablename__ = 'students'
  8.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  9.     name = sqlalchemy.Column(sqlalchemy.String(20))
  10.     kana = sqlalchemy.Column(sqlalchemy.String(40))
  11. class Address(Base):
  12.     __tablename__ = 'addresses'
  13.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  14.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  15.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  16.     
  17.     # StudentとAddressテーブルの関連を定義
  18.     student = sqlalchemy.orm.relationship("Student", backref=sqlalchemy.orm.backref('addresses', order_by=id))
  19. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  20. engine = sqlalchemy.create_engine(url, echo=False)
  21. # スキーマ再構成
  22. Base.metadata.create_all(engine)





実行後、データベースを確認すると、addressesテーブルとaddresses_id_seqシーケンスが
作成されています。


Schema |     Name     | Type | Owner
--------+------------------+----------+---------
public | addresses        | table    | pgadmin
public | addresses_id_seq | sequence | pgadmin
public | students         | table    | pgadmin
public | students_id_seq | sequence | pgadmin





この関連は以下のようにも定義できるようです。


  1. # -*- coding:utf-8 -*-
  2. import sqlalchemy
  3. import sqlalchemy.orm
  4. import sqlalchemy.ext.declarative
  5. Base = sqlalchemy.ext.declarative.declarative_base()
  6. class Student(Base):
  7.     __tablename__ = 'students'
  8.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  9.     name = sqlalchemy.Column(sqlalchemy.String(20))
  10.     kana = sqlalchemy.Column(sqlalchemy.String(40))
  11.     
  12.     # StudentとAddressテーブルの関連を定義
  13.     addresses = sqlalchemy.orm.relationship("Address", order_by="Address.id", backref="students")
  14. class Address(Base):
  15.     __tablename__ = 'addresses'
  16.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  17.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  18.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  19.     
  20. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  21. engine = sqlalchemy.create_engine(url, echo=False)
  22. # スキーマ再構成
  23. Base.metadata.create_all(engine)








Working with Related Objects



これだけで、Studentオブジェクトとaddressテーブルの内容が関連付けられます。
データを1件取得して、内容を表示してみます。


  1. # -*- coding:utf-8 -*-
  2. import sqlalchemy
  3. import sqlalchemy.orm
  4. import sqlalchemy.ext.declarative
  5. Base = sqlalchemy.ext.declarative.declarative_base()
  6. class Student(Base):
  7.     __tablename__ = 'students'
  8.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  9.     name = sqlalchemy.Column(sqlalchemy.String(20))
  10.     kana = sqlalchemy.Column(sqlalchemy.String(40))
  11. class Address(Base):
  12.     __tablename__ = 'addresses'
  13.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  14.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  15.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  16.     
  17.     # StudentとAddressテーブルの関連を定義
  18.     student = sqlalchemy.orm.relationship("Student", backref=sqlalchemy.orm.backref('addresses', order_by=id))
  19. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  20. engine = sqlalchemy.create_engine(url, echo=False)
  21. # セッションを作成
  22. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  23. session = Session()
  24. student = session.query(Student).filter(Student.name==u'西住 みほ').one()
  25. # 名前を表示
  26. print(student.name)
  27. # 自動的にaddressesフィールドが追加される
  28. print(student.addresses)




実行してみると、addressesというフィールドの値が配列で取得できている模様。
データは登録していないので内容は空です。


$ python sample.py
西住 みほ
[]







メールアドレスを追加してみます。


  1. # -*- coding:utf-8 -*-
  2. import sqlalchemy
  3. import sqlalchemy.orm
  4. import sqlalchemy.ext.declarative
  5. Base = sqlalchemy.ext.declarative.declarative_base()
  6. class Student(Base):
  7.     __tablename__ = 'students'
  8.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  9.     name = sqlalchemy.Column(sqlalchemy.String(20))
  10.     kana = sqlalchemy.Column(sqlalchemy.String(40))
  11. class Address(Base):
  12.     __tablename__ = 'addresses'
  13.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  14.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  15.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  16.     
  17.     # StudentとAddressテーブルの関連を定義
  18.     student = sqlalchemy.orm.relationship("Student", backref=sqlalchemy.orm.backref('addresses', order_by=id))
  19. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  20. engine = sqlalchemy.create_engine(url, echo=False)
  21. # セッションを作成
  22. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  23. session = Session()
  24. student = session.query(Student).filter(Student.name==u'西住 みほ').one()
  25. # メールアドレスを追加
  26. student.addresses = [
  27.     Address(email_address=u'nishizumi.miho@example.com'),
  28.     Address(email_address=u'mipolin@example.com')]
  29. # 保存実行
  30. session.commit()




データベースを検索すると、ちゃんとメールアドレスが登録されています。


sample=# select * from addresses;
id |     email_address        | students_id
----+----------------------------+-------------
1 | nishizumi.miho@example.com |         18
2 | mipolin@example.com        |         18






プログラムでの検索を試してみます。


  1. # -*- coding:utf-8 -*-
  2. import sqlalchemy
  3. import sqlalchemy.orm
  4. import sqlalchemy.ext.declarative
  5. Base = sqlalchemy.ext.declarative.declarative_base()
  6. class Student(Base):
  7.     __tablename__ = 'students'
  8.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  9.     name = sqlalchemy.Column(sqlalchemy.String(20))
  10.     kana = sqlalchemy.Column(sqlalchemy.String(40))
  11. class Address(Base):
  12.     __tablename__ = 'addresses'
  13.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  14.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  15.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  16.     
  17.     # StudentとAddressテーブルの関連を定義
  18.     student = sqlalchemy.orm.relationship("Student", backref=sqlalchemy.orm.backref('addresses', order_by=id))
  19. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  20. engine = sqlalchemy.create_engine(url, echo=False)
  21. # セッションを作成
  22. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  23. session = Session()
  24. student = session.query(Student).filter(Student.name==u'西住 みほ').one()
  25. # 名前を表示
  26. print(student.name)
  27. # メールアドレスを表示
  28. for address in student.addresses:
  29.     print(address.email_address)




自動的に結合してくれて便利。


$ python sample.py
西住 みほ
nishizumi.miho@example.com
mipolin@example.com









Querying with Joins



メールアドレスで検索したい時、


  1. students = session.query(Student, Address).filter(
  2.     Student.addresses.email_address==u'mipolin@example.com').all()




こんな感じでいけるかな?と思っていたのですがエラーになります。


$ python sample.py
Traceback (most recent call last):
File "sample.py", line 35, in <module>
    Student.addresses.email_address==u'mipolin@example.com').all()
File "/usr/local/lib/python2.7/dist-packages/sqlalchemy/orm/attributes.py", line 189, in __getattr__
    key)
AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with
Student.addresses has an attribute 'email_address'





じゃあ、これは?


  1. students = session.query(Student, Address).filter(
  2.     Address.email_address==u'mipolin@example.com').all()



エラーにはなりませんが、全てのデータが取得できます。

検索するとき、studentsとaddressesの結合条件は自動的には付与されないみたいです。

プログラムはこうなりました。
session.queryの中に、StudentとAddressの2つのオブジェクトを指定。
filterで結合条件を指定してやります。

結果はタプルで、StudentとAddress別々のデータが取得できます。


  1. # -*- coding:utf-8 -*-
  2. import sqlalchemy
  3. import sqlalchemy.orm
  4. import sqlalchemy.ext.declarative
  5. Base = sqlalchemy.ext.declarative.declarative_base()
  6. class Student(Base):
  7.     __tablename__ = 'students'
  8.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  9.     name = sqlalchemy.Column(sqlalchemy.String(20))
  10.     kana = sqlalchemy.Column(sqlalchemy.String(40))
  11. class Address(Base):
  12.     __tablename__ = 'addresses'
  13.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  14.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  15.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  16.     
  17.     # StudentとAddressテーブルの関連を定義
  18.     student = sqlalchemy.orm.relationship("Student", backref=sqlalchemy.orm.backref('addresses', order_by=id))
  19. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  20. engine = sqlalchemy.create_engine(url, echo=False)
  21. # セッションを作成
  22. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  23. session = Session()
  24. students = session.query(Student, Address).\
  25.     filter(Student.id == Address.students_id).\
  26.     filter(Address.email_address==u'mipolin@example.com').\
  27.     all()
  28. for s, a in students:
  29.     print(s.name)
  30.     print(a.email_address)
  31.     #こうすると、追加でクエリを実行してメールアドレスを取得に行く
  32.     print(s.addresses[0].email_address)





実行したクエリだけでは、該当Studentのメールアドレス全ては取得できません。
別のメールアドレスを取得しようとすると、追加でselectを実行して
データを取りに行くようです。




結合の指定方法は複数あり、こんな書き方もできます。


  1. students = session.query(Student).join(Address).\
  2.     filter(Address.email_address==u'mipolin@example.com').\
  3.     all()
  4. for s in students:
  5.     print(s.name)
  6.     #print(a.email_address)
  7.     #こうすると、追加でクエリを実行してメールアドレスを取得に行く
  8.     print(s.addresses[0].email_address)
  9.     print(s.addresses[1].email_address)





他にはこんな指定方法。


  1. students = session.query(Student).join(Address, Student.id == Address.students_id).\
  2.     filter(Address.email_address==u'mipolin@example.com').\
  3.     all()





  1. students = session.query(Student).join(Student.addresses).\
  2.     filter(Address.email_address==u'mipolin@example.com').\
  3.     all()





  1. students = session.query(Student).join(Address, Student.addresses).\
  2.     filter(Address.email_address==u'mipolin@example.com').\
  3.     all()





  1. students = session.query(Student).join('addresses').\
  2.     filter(Address.email_address==u'mipolin@example.com').\
  3.     all()







「join」を使用するとこんなクエリが実行されますが、


FROM students JOIN addresses ON students.id = addresses.students_id





「outerjoin」を使用すると、


  1. students = session.query(Student).outerjoin(Student.addresses).\
  2.     filter(Address.email_address==u'mipolin@example.com').\
  3.     all()




その名のとおり、LEFT OUTER JOINになります。


FROM students LEFT OUTER JOIN addresses ON students.id = addresses.students_id

関連記事

テーマ:プログラミング - ジャンル:コンピュータ

  1. 2014/04/02(水) 22:51:25|
  2. Python
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<SQLAlchemyの使い方6 サブクエリーやEXISTS | ホーム | GroovyServでJRubyの起動を高速化する>>

コメント

コメントの投稿


管理者にだけ表示を許可する

トラックバック

トラックバック URL
http://symfoware.blog68.fc2.com/tb.php/1378-f631ae1b
この記事にトラックバックする(FC2ブログユーザー)