Symfoware

Symfowareについての考察blog

SQLAlchemyの使い方6 サブクエリーやEXISTS

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)








Using Aliases



id:18の生徒のメールアドレスが2件登録さています。
このアドレス2つを条件にいれてデータを検索してみます。


  1. import sqlalchemy
  2. import sqlalchemy.orm
  3. import sqlalchemy.ext.declarative
  4. Base = sqlalchemy.ext.declarative.declarative_base()
  5. class Student(Base):
  6.     __tablename__ = 'students'
  7.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  8.     name = sqlalchemy.Column(sqlalchemy.String(20))
  9.     kana = sqlalchemy.Column(sqlalchemy.String(40))
  10. class Address(Base):
  11.     __tablename__ = 'addresses'
  12.     id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True)
  13.     email_address = sqlalchemy.Column(sqlalchemy.String(100), nullable=False)
  14.     students_id = sqlalchemy.Column(sqlalchemy.Integer, sqlalchemy.ForeignKey('students.id'))
  15.     
  16.     # StudentとAddressテーブルの関連を定義
  17.     student = sqlalchemy.orm.relationship("Student", backref=sqlalchemy.orm.backref('addresses', order_by=id))
  18. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  19. engine = sqlalchemy.create_engine(url, echo=False)
  20. # セッションを作成
  21. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  22. session = Session()
  23. adalias1 = sqlalchemy.orm.aliased(Address)
  24. adalias2 = sqlalchemy.orm.aliased(Address)
  25. students = session.query(Student.name, adalias1.email_address, adalias2.email_address).\
  26.     join(adalias1, Student.addresses).\
  27.     join(adalias2, Student.addresses).\
  28.     filter(adalias1.email_address==u'nishizumi.miho@example.com').\
  29.     filter(adalias2.email_address==u'mipolin@example.com').\
  30.     all()
  31. for name, email1, email2 in students:
  32.     print name, email1, email2






実行結果はこちら。


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






裏で実行されているクエリはこんな感じです。


SELECT
students.name AS students_name,
addresses_1.email_address AS addresses_1_email_address,
addresses_2.email_address AS addresses_2_email_address

FROM
students
JOIN addresses AS addresses_1 ON students.id = addresses_1.students_id
JOIN addresses AS addresses_2 ON students.id = addresses_2.students_id

WHERE
addresses_1.email_address = %(email_address_1)s
AND addresses_2.email_address = %(email_address_2)s







取得できる行のオブジェクトで、メールアドレスは「email_address」に集約されるようで、
両方のアドレスを取得することができません。

列名でアクセスしたい時はlabelをつければ良いようです。


  1. adalias1 = sqlalchemy.orm.aliased(Address)
  2. adalias2 = sqlalchemy.orm.aliased(Address)
  3. students = session.query(
  4.         Student.name,
  5.         adalias1.email_address.label("email1"),
  6.         adalias2.email_address.label("email2")).\
  7.     join(adalias1, Student.addresses).\
  8.     join(adalias2, Student.addresses).\
  9.     filter(adalias1.email_address==u'nishizumi.miho@example.com').\
  10.     filter(adalias2.email_address==u'mipolin@example.com').\
  11.     all()
  12. for s in students:
  13.     print s.name, s.email1, s.email2









Using Subqueries



生徒が保持しているメールアドレスの件数を、生徒の名前と共に表示してみます。

やりかたは、まずメールアドレスの件数を数え上げるサブクエリーを作成。
それを指定して、生徒の検索を行います。


  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. 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. # メールアドレスの件数を数えるサブクエリーを定義
  25. stmt = session.query(Address.students_id, sqlalchemy.sql.func.count('*').\
  26.     label('address_count')).\
  27.     group_by(Address.students_id).subquery()
  28. students = session.query(Student, stmt.c.address_count).\
  29.     outerjoin(stmt, Student.id==stmt.c.students_id).order_by(Student.id)
  30. for s, count in students:
  31.     print s.name, count




実行結果はこちら。


$ python sample.py
西住 みほ 2
武部 沙織 None
五十鈴 華 None
秋山 優花里 None
冷泉 麻子 None








Selecting Entities from Subqueries



サブクエリーに条件式を指定してみます。


  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. 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. # 「mipolin@example.com」以外で絞り込み
  25. stmt = session.query(Address).\
  26.     filter(Address.email_address != 'mipolin@example.com').\
  27.     subquery()
  28. adalias = sqlalchemy.orm.aliased(Address, stmt)
  29. students = session.query(Student, adalias).\
  30.     join(adalias, Student.addresses)
  31. for s, a in students:
  32.     print s.name, a.email_address




実行結果


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








Using EXISTS



EXISTSを使用して、メールアドレスを持っている生徒の名前を取得します。


  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. 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. stmt = sqlalchemy.sql.exists().where(Address.students_id==Student.id)
  25. students = session.query(Student.name).filter(stmt)
  26. for name, in students:
  27.     print name





実行結果


$ python sample.py
西住 みほ






こういう書き方でもOKみたい。


  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. 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. 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.name).filter(Student.addresses.any())
  25. for name, in students:
  26.     print name







anyには条件式を含めることもできます。


  1. students = session.query(Student.name).\
  2.     filter(Student.addresses.any(Address.email_address.like('%miho%')))
  3. for name, in students:
  4.     print name






チルダをつけると、条件式が逆転。


  1. students = session.query(Student.name).\
  2.     filter(~Student.addresses.any(Address.email_address.like('%miho%')))
  3. for name, in students:
  4.     print name




実行結果


$ python sample.py
武部 沙織
五十鈴 華
秋山 優花里
冷泉 麻子






生徒の名前が「西住 みほ」のメールアドレスを検索したい場合は、
anyではなくてhasを使用します。


  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. 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. 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(Address).\
  25.     filter(Address.students.has(Student.name==u'西住 みほ'))
  26. for s in students:
  27.     print s.email_address






実行結果


$ python sample.py
nishizumi.miho@example.com
mipolin@example.com

関連記事

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

  1. 2014/04/07(月) 23:14:16|
  2. Python
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<SQLAlchemyの使い方7 JOINデータの取得方法とデータの削除 | ホーム | SQLAlchemyの使い方5 テーブル間のリレーション>>

コメント

コメントの投稿


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

トラックバック

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