Symfoware

Symfowareについての考察blog

SQLAlchemyの使い方2 データの更新と検索、ロールバック

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

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



Rolling Back



データの更新、追加を行なってデータを表示。
ロールバックして、その後の状態を再度表示してみます。


  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.     
  11.     # デバッグ用
  12.     def __repr__(self):
  13.     return '<name=%s, kana=%s>' % (self.name, self.kana)
  14. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  15. engine = sqlalchemy.create_engine(url, echo=False)
  16. # セッションを作成
  17. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  18. session = Session()
  19. # データを検索
  20. found_student = session.query(Student).filter_by(name='西住 みほ').first()
  21. # 検索結果を変更
  22. found_student.name = '西住 まほ'
  23. # 新しいユーザーを登録
  24. new_student = Student(name='蝶野 亜美', kana='チョウノ アミ')
  25. # セッションに追加
  26. session.add(new_student)
  27. # この時点で、変更、追加したデータの内容を表示
  28. print('ロールバック前')
  29. print(found_student.name)
  30. print(new_student.name)
  31. print(new_student in session)
  32. # ロールバック
  33. session.rollback()
  34. # ロールバック後のデータを表示
  35. print('ロールバック後')
  36. print(found_student.name)
  37. print(new_student.name)
  38. print(new_student in session)




実行してみると、更新したデータは元通りに。
追加したデータはセッションから削除されていました。


$ python sample.py
ロールバック前
西住 まほ
蝶野 亜美
True
ロールバック後
西住 みほ
蝶野 亜美
False






Querying



こんなデータが入っているstudentsテーブルを検索します。


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




全件検索したい場合はこんな感じで良いようです。


  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.     
  11.     # デバッグ用
  12.     def __repr__(self):
  13.         return '<name=%s, kana=%s>' % (self.name, self.kana)
  14. url = 'postgresql://pgadmin:password@192.168.1.101:5432/sample'
  15. engine = sqlalchemy.create_engine(url, echo=False)
  16. # セッションを作成
  17. Session = sqlalchemy.orm.sessionmaker(bind=engine)
  18. session = Session()
  19. # データを検索
  20. students = session.query(Student)
  21. for row in students:
  22.     print('%d, %s' % (row.id, row.name))





以降、データの検索部分のロジックを抜粋します。






order by



id順に並び替えてみます。


  1. students = session.query(Student).order_by(Student.id)
  2. for row in students:
  3.     print('%d, %s' % (row.id, row.name))




$ python sample.py
18, 西住 みほ
19, 武部 沙織
20, 五十鈴 華
21, 秋山 優花里
22, 冷泉 麻子




昇順、降順を変更したい場合は、order_byで指定しているフィールド名に
「asc()」または「desc()」を指定します。


  1. students = session.query(Student).order_by(Student.id.desc())
  2. for row in students:
  3.     print('%d, %s' % (row.id, row.name))




$ python sample.py
22, 冷泉 麻子
21, 秋山 優花里
20, 五十鈴 華
19, 武部 沙織
18, 西住 みほ








列の絞り込み



取得したい列を指定して検索してみます。


  1. students = session.query(Student.id, Student.name)
  2. for id, name in students:
  3.     print('%d, %s' % (id, name))




$ python sample.py
18, 西住 みほ
19, 武部 沙織
20, 五十鈴 華
21, 秋山 優花里
22, 冷泉 麻子




create_engineにecho=Trueを指定して発行されるクエリを見てみると、
id,nameの列指定あり。

SELECT students.id AS students_id, students.name AS students_name
FROM students




Studentを指定。

SELECT students.id AS students_id, students.name AS students_name, students.kana AS students_kana
FROM students




ちゃんとSELECTに反映されてます。
たくさんフィールドがあるテーブルでは効果的かと。





オブジェクト + 列名



オブジェクトにマッピングしつつ、一部の列は別途取得したい。


  1. students = session.query(Student.id, Student)
  2. for id, s in students:
  3.     print(id)
  4.     print('%d, %s, %s' % (s.id, s.name, s.kana))




$ python sample.py
18
18, 西住 みほ, ニシズミ ミホ
19
19, 武部 沙織, タケベ サオリ
20
20, 五十鈴 華, イスズ ハナ
21
21, 秋山 優花里, アキヤマ ユカリ
22
22, 冷泉 麻子, レイゼイ マコ









列の別名



列に別名をつけて検索したい。


  1. students = session.query(Student.kana.label('kana_name'))
  2. for row in students:
  3.     print(row.kana_name)




$ python sample.py
ニシズミ ミホ
タケベ サオリ
イスズ ハナ
アキヤマ ユカリ
レイゼイ マコ








テーブルの別名



テーブルに別名をつけて検索したい。


import sqlalchemy.orm

student_alias = sqlalchemy.orm.aliased(Student, name='student_alias_name')
students = session.query(student_alias, student_alias.name)

for row in students:
    print(row.student_alias_name.name)
    print(row.student_alias_name.kana)










取得範囲を指定したい



SQLでいうlimitとoffsetの指定です。


  1. students = session.query(Student).order_by(Student.id)[1:3]
  2. for row in students:
  3.     print("%d, %s" % (row.id, row.name))




この指定で
「LIMIT 2 OFFSET 1」
という条件での検索になります。


19, 武部 沙織
20, 五十鈴 華








絞り込みの条件を指定したい



filter_byをつかった絞り込み


  1. students = session.query(Student).filter_by(kana='ニシズミ ミホ')
  2. for row in students:
  3.     print("%d, %s" % (row.id, row.name))




filterを使った絞り込み。
「=」ではなくて「==」な点に注意。


  1. students = session.query(Student).filter(Student.kana=='ニシズミ ミホ')
  2. for row in students:
  3.     print("%d, %s" % (row.id, row.name))




実行されるSQL文は同じになるようです。


filter_byやfilterは複数指定することが可能です。


  1. students = session.query(Student).\
  2.     filter(Student.name=='西住 みほ').\
  3.     filter(Student.kana=='ニシズミ ミホ')
  4. for row in students:
  5.     print("%d, %s" % (row.id, row.name))








Common Filter Operators



条件式の指定方法。

【equals】


  1. students = session.query(Student).filter(Student.name=='西住 みほ')




$ python sample.py
18, 西住 みほ






【not equals】


  1. students = session.query(Student).filter(Student.name!='西住 みほ')




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






【LIKE】


  1. students = session.query(Student).filter(Student.name.like('%住%'))




$ python sample.py
18, 西住 みほ






【IN】


  1. students = session.query(Student).\
  2.     filter(Student.name.in_(['西住 みほ', '武部 沙織', '五十鈴 華']))




$ python sample.py
18, 西住 みほ
19, 武部 沙織
20, 五十鈴 華







【NOT IN】


  1. students = session.query(Student).\
  2.     filter(~Student.name.in_(['西住 みほ', '武部 沙織', '五十鈴 華']))



チルダ(~)を条件の先頭に付けます。


$ python sample.py
21, 秋山 優花里
22, 冷泉 麻子







【IS NULL】


  1. students = session.query(Student).filter(Student.name==None)




$ python sample.py
(結果なし)







【IS NOT NULL】


  1. students = session.query(Student).filter(Student.name!=None)




$ python sample.py
18, 西住 みほ
19, 武部 沙織
20, 五十鈴 華
21, 秋山 優花里
22, 冷泉 麻子








【AND】

3種類指定方法があるようです。

「sqlalchemy.and_」を使用する方法


  1. students = session.query(Student).filter(
  2.     sqlalchemy.and_(Student.name=='西住 みほ', Student.kana=='ニシズミ ミホ')
  3.     )




filterの中に条件を並べて書く方法


  1. students = session.query(Student).filter(
  2.     Student.name=='西住 みほ', Student.kana=='ニシズミ ミホ'
  3.     )




filterを重ねる方法。


  1. students = session.query(Student).\
  2.     filter(Student.name=='西住 みほ').\
  3.     filter(Student.kana=='ニシズミ ミホ')




もちろん結果は同じです。


$ python sample.py
18, 西住 みほ






【OR】


  1. students = session.query(Student).filter(
  2.     sqlalchemy.or_(Student.name=='西住 みほ', Student.name=='武部 沙織')
  3.     )




$ python sample.py
18, 西住 みほ
19, 武部 沙織






【match】

クエリを表示してみると、PostgreSQLだと
「WHERE students.name @@ to_tsquery(%(name_1)s)」
という条件が指定されました。

to_tsqueryで調べてみると、全文検索のようです。


  1. students = session.query(Student).filter(Student.name.match('西住 みほ'))
  2.     
  3. for row in students:
  4.     print("%d, %s" % (row.id, row.name))




$ python sample.py
18, 西住 みほ



関連記事

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

  1. 2014/03/27(木) 23:55:53|
  2. Python
  3. | トラックバック:0
  4. | コメント:0
  5. | 編集
<<SQLAlchemyの使い方3 データの取得方法いろいろ | ホーム | SQLAlchemyの使い方1 単一テーブルのマッピングとデータの追加>>

コメント

コメントの投稿


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

トラックバック

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