Querying Data Section 5, Lecture 23

1. データを取り込む
2. フィルターで絞ってデータを取得する

Publicationテーブルにデータを取り込む

>>> from run import db, Book, Publication

>>> p1 = Publication("test1")
>>> p2 = Publication("Paramount Press")
>>> p3 = Publication("Oracle Books Inc")
>>> p4 = Publication("Vintage Books and Comics")
>>> p5 = Publication("Trolls Press")
>>> p6 = Publication("Broadway Press")

>>> db.session.add_all([p1,p2,p3,p4,p5,p6,p7,p8])

Bookテーブルにデータを取り込む

>>> db.session.add_all([p1,p2,p3,p4,p5,p6,p7,p8])
>>> b1 = Book("Miky's Delivery Service", "William Dobelli", 3.9, "ePub", "broom-145379.svg", 123, 1)
>>> b2 = Book("The Secret Life of Walter Kitty", "Kitty Stiller", 4.1, "Hardcover", "cat-150306.svg", 133, 1)
>>> b3 = Book("The Empty Book of Life", "Roy Williamson", 4.2, "eBook", "book-life-34063.svg", 153, 1)
>>> b4 = Book("Life After Dealth", "Nikita Kimmel", 3.8, "Paperback", "mummy-146868.svg", 175, 2)
>>> b5 = Book("Sali The Dali", "Charles Rowling", 4.6, "Hardcover", "el-salvador-dali-889515.jpg", 253, 2)
>>> b6 = Book("Taming Dragons", "James Vonnegut", 4.5, "MassMarket Paperback", "dragon-23164.svg", 229, 2)
>>> b7 = Book("The Singing Magpie", "Oscar Steinbeck", 5, "Hardcover", "magpie-147852.svg", 188, 3)
>>> b8 = Book("Mr. Incognito", "Amelia Funke", 4.2, "Hardcover", "incognito-160143.svg", 205, 3)

>>> db.session.add_all([b1,b2,b3,b4,b5,b6,b7,b8,b9,b10,b11,b12,b13,b14,b15,b16,b17,b18])
>>> db.session.commit()

実際にORMで確認してみる

ここから本題のQueryingを行う。

①1つ目のデータをフィルターする

>>> first= Book.query.first()
>>> first
Miky's Delivery Service by William Dobelli

②ePubのみ取得する

>>> filter_data = Book.query.filter_by(format='ePub').all()
>>> filter_data
[Miky's Delivery Service by William Dobelli, The Sacred Book of Kairo by Heidi Zimmerman]

③タイトルをアルファベット順で並べる

>>> order_records = Book.query.order_by(Book.title).all()
>>> order_records
[A Dog without purpose by Edgar Dahl, A Frog's Life by Herman Capote, As Men Thinketh by Edward McPhee,

④Paperbackのみ取得して、アルファベット順で並べる。

>>> Book.query.filter_by(format='Paperback').order_by(Book.title).all()
[As Men Thinketh by Edward McPhee, Life After Dealth by Nikita Kimmel, The Mystery of Mandalas by Jack Morrison, Thieves of Kaalapani by Mohit Gustav]

❺リミットで絞る

>>> Book.query.limit(1).all()
[Miky's Delivery Service by William Dobelli]

捕捉

>>> all = Book.query.all()
>>> all
[Miky's Delivery Service by William Dobelli, The Secret...

出版者での絞り方

変数名 = テーブル名.query.filter_by(name='Broadway Press').first()　で絞ることが出来る。

更に　 　変数名 = テーブル名.query.filter_by(pub_id = result.id).all()　 をすることでシュッパシャで絞ってデータを取得することが出来る。

写真の通りprimary keyは勝手に設定される。

>>> result = Publication.query.filter_by(name='Broadway Press').first()
>>> result
Publisher is Broadway Press
>>> result.name
'Broadway Press'
>>> result.id
6
>>> broadway = Book.query.filter_by(pub_id = result.id).all()
>>> broadway
[As Men Thinketh by Edward McPhee, Mathematics of Music by Mary Turing, The Mystery of Mandalas by Jack Morrison]