Symfoware

Symfowareについての考察blog

SQLAlchemyの使い方7 JOINデータの取得方法とデータの削除

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

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



現在登録されているデータは以下のとおり。


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



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







Eager Loading



Eager:熱心な
という意味らしい。


違いを見るために、create_engineでecho=Trueを指定して、
実行時の詳細を表示します。

まず、通常のデータ取得方法。


  1. import sqlalchemy
  2. import sqlalchemy.orm
  3. import sqlalchemy.ext.declarative
  4. import sqlalchemy.sql
  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.     
  15.     def __repr__(self):
  16.         r = u"<Student:(name='%s', kana='%s')>" % (self.name, self.kana)
  17.         return r.encode('utf-8')
  18. class Address(Base):
  19.     __tablename__ = 'addresses'
  20.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  21.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  22.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  23.     
  24.     def __repr__(self):
  25.         r = u"<Address:(email_address='%s')>" % (self.email_address)
  26.         return r.encode('utf-8')
  27. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  28. engine = sqlalchemy.create_engine(url, echo=True)
  29. # セッションを作成
  30. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  31. session = Session()
  32. mipolin = session.query(Student).\
  33.     filter_by(name=u'西住 みほ').one()
  34. print mipolin
  35. print '-' * 20
  36. print mipolin.addresses





「西住 みほ」というユーザーを検索。
Studentテーブルの情報を表示した後、関連するAddressテーブルの情報を表示しています。

実行時のログの抜粋です。


SELECT students.id AS students_id, students.name AS students_name, students.kana AS students_kana
FROM students
WHERE students.name = %(name_1)s
sqlalchemy.engine.base.Engine {'name_1': u'\u897f\u4f4f\u3000\u307f\u307b'}
<Student:(name='西住 みほ', kana='ニシズミ ミホ')>

--------------------

sqlalchemy.engine.base.Engine SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address,
addresses.students_id AS addresses_students_id
FROM addresses
WHERE %(param_1)s = addresses.students_id ORDER BY addresses.id
sqlalchemy.engine.base.Engine {'param_1': 18}
[<Address:(email_address='nishizumi.miho@example.com')>, <Address:(email_address='mipolin@example.com')>]




addressesテーブルにはデータが必要となった時にアクセスしています。



データの取得部分を変更。


  1. mipolin = session.query(Student).\
  2.     options(sqlalchemy.orm.subqueryload(Student.addresses)).\
  3.     filter_by(name=u'西住 みほ').one()




実行時のログを見てみます。


SELECT students.id AS students_id, students.name AS students_name, students.kana AS students_kana
FROM students
WHERE students.name = %(name_1)s
sqlalchemy.engine.base.Engine {'name_1': u'\u897f\u4f4f\u3000\u307f\u307b'}

SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address,
addresses.students_id AS addresses_students_id, anon_1.students_id AS anon_1_students_id
FROM (SELECT students.id AS students_id
FROM students
WHERE students.name = %(name_1)s) AS anon_1 JOIN addresses ON anon_1.students_id = addresses.students_id
ORDER BY anon_1.students_id, addresses.id
sqlalchemy.engine.base.Engine {'name_1': u'\u897f\u4f4f\u3000\u307f\u307b'}

<Student:(name='西住 みほ', kana='ニシズミ ミホ')>
--------------------
[<Address:(email_address='nishizumi.miho@example.com')>, <Address:(email_address='mipolin@example.com')>]




今度はAddressが必要となる前に事前にデータを取得するようになりました。






Joined Load



subqueryloadでは、StudentとAddressテーブル各々にクエリを実行しました。
Joined Loadを指定し、一括でデータを取得するようにしてみます。


  1. mipolin = session.query(Student).\
  2.     options(sqlalchemy.orm.joinedload(Student.addresses)).\
  3.     filter_by(name=u'西住 みほ').one()




実行ログの抜粋です。


SELECT students.id AS students_id, students.name AS students_name, students.kana AS students_kana,
addresses_1.id AS addresses_1_id, addresses_1.email_address AS addresses_1_email_address,
addresses_1.students_id AS addresses_1_students_id
FROM students LEFT OUTER JOIN addresses AS addresses_1 ON students.id = addresses_1.students_id
WHERE students.name = %(name_1)s ORDER BY addresses_1.id
sqlalchemy.engine.base.Engine {'name_1': u'\u897f\u4f4f\u3000\u307f\u307b'}

<Student:(name='西住 みほ', kana='ニシズミ ミホ')>
--------------------
[<Address:(email_address='nishizumi.miho@example.com')>, <Address:(email_address='mipolin@example.com')>]




OUTER JOINでデータを取得するクエリに変更されました。







Explicit Join + Eagerload



今度はAddressテーブルをメインに検索してみます。


  1. mipolin_addresses = session.query(Address).\
  2.     join(Address.student).\
  3.     filter(Student.name==u'西住 みほ').\
  4.     options(sqlalchemy.orm.contains_eager(Address.student)).\
  5.     all()
  6. print mipolin_addresses
  7. print '-' * 20
  8. print mipolin_addresses[0].student
  9. print mipolin_addresses[1].student




実行時のログの抜粋です。


SELECT students.id AS students_id, students.name AS students_name, students.kana AS students_kana,
addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.students_id AS addresses_students_id
FROM addresses JOIN students ON students.id = addresses.students_id
WHERE students.name = %(name_1)s
sqlalchemy.engine.base.Engine {'name_1': u'\u897f\u4f4f\u3000\u307f\u307b'}

[<Address:(email_address='nishizumi.miho@example.com')>, <Address:(email_address='mipolin@example.com')>]
--------------------
<Student:(name='西住 みほ', kana='ニシズミ ミホ')>
<Student:(name='西住 みほ', kana='ニシズミ ミホ')>





options(sqlalchemy.orm.contains_eager(Address.student))
の指定がない場合は、必要となった時データの取得を行います。


SELECT addresses.id AS addresses_id, addresses.email_address AS addresses_email_address, addresses.students_id AS addresses_students_id
FROM addresses JOIN students ON students.id = addresses.students_id
WHERE students.name = %(name_1)s
sqlalchemy.engine.base.Engine {'name_1': u'\u897f\u4f4f\u3000\u307f\u307b'}

[<Address:(email_address='nishizumi.miho@example.com')>, <Address:(email_address='mipolin@example.com')>]
--------------------
SELECT students.id AS students_id, students.name AS students_name, students.kana AS students_kana
FROM students
WHERE students.id = %(param_1)s
sqlalchemy.engine.base.Engine {'param_1': 18}

<Student:(name='西住 みほ', kana='ニシズミ ミホ')>
<Student:(name='西住 みほ', kana='ニシズミ ミホ')>








Deleting



データの削除方法です。
session.delete(削除したいオブジェクト)
でOK。


こんなプログラムを実行してみます。
※commitを実行していないので、実際のデータは削除されません。


  1. import sqlalchemy
  2. import sqlalchemy.orm
  3. import sqlalchemy.ext.declarative
  4. import sqlalchemy.sql
  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.     
  15.     def __repr__(self):
  16.         r = u"<Student:(name='%s', kana='%s')>" % (self.name, self.kana)
  17.         return r.encode('utf-8')
  18. class Address(Base):
  19.     __tablename__ = 'addresses'
  20.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  21.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  22.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  23.     
  24.     #student = sqlalchemy.orm.relationship("Student", backref=sqlalchemy.orm.backref('addresses', order_by=id))
  25.     
  26.     def __repr__(self):
  27.         r = u"<Address:(email_address='%s')>" % (self.email_address)
  28.         return r.encode('utf-8')
  29. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  30. engine = sqlalchemy.create_engine(url, echo=False)
  31. # セッションを作成
  32. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  33. session = Session()
  34. mipolin = session.query(Student).filter_by(name=u'西住 みほ').one()
  35. session.delete(mipolin)
  36. print u'Studentの件数'
  37. print session.query(Student).filter_by(name=u'西住 みほ').count()
  38. print u'Addressの件数'
  39. print session.query(Address).\
  40.     filter(Address.email_address.in_(['nishizumi.miho@example.com', 'mipolin@example.com'])).\
  41.     count()





まあ、当然といえば当然なのですが、Addressテーブルのデータは削除されません。


$ python sample.py
Studentの件数
0
Addressの件数
2









Configuring delete/delete-orphan Cascade



Studentテーブルのデータを削除したら、関連するAddressテーブルのデータも削除するようにしてみます。
変更した箇所は、relationshipにcascadeを指定しただけです。


  1. import sqlalchemy
  2. import sqlalchemy.orm
  3. import sqlalchemy.ext.declarative
  4. import sqlalchemy.sql
  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.     # cascadeを指定して、削除を連動
  14.     addresses = sqlalchemy.orm.relationship("Address", order_by="Address.id", backref="students",
  15.         cascade="all, delete, delete-orphan")
  16.     
  17.     def __repr__(self):
  18.         r = u"<Student:(name='%s', kana='%s')>" % (self.name, self.kana)
  19.         return r.encode('utf-8')
  20. class Address(Base):
  21.     __tablename__ = 'addresses'
  22.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  23.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  24.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  25.     
  26.     def __repr__(self):
  27.         r = u"<Address:(email_address='%s')>" % (self.email_address)
  28.         return r.encode('utf-8')
  29. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  30. engine = sqlalchemy.create_engine(url, echo=False)
  31. # セッションを作成
  32. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  33. session = Session()
  34. mipolin = session.query(Student).filter_by(name=u'西住 みほ').one()
  35. session.delete(mipolin)
  36. print u'Studentの件数'
  37. print session.query(Student).filter_by(name=u'西住 みほ').count()
  38. print u'Addressの件数'
  39. print session.query(Address).\
  40.     filter(Address.email_address.in_(['nishizumi.miho@example.com', 'mipolin@example.com'])).\
  41.     count()






実行してみると、Addressテーブルのデータも削除してくれました。
これは便利。


$ python sample.py
Studentの件数
0
Addressの件数
0


関連記事

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

  1. 2014/04/09(水) 22:54:17|
  2. Python
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<SQLAlchemyの使い方8 多対多のリレーション | ホーム | SQLAlchemyの使い方6 サブクエリーやEXISTS>>

コメント

コメントの投稿


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

トラックバック

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