flask-SQLAlchemy で良く使うクエリーコマンド一覧

flask-SQLAlchemy で良く使うコマンド一覧。

---

 """

◇Query all user data

    User.query.all()

    How many users are queried?

    User.query.count()

*query.all()で検索した結果をprintするとレコード・オブジェクトがリスト形式で表示される。
>>> a = Tw_msg.query.all()
>>> print(a)
[<Tw_msg 1>, <Tw_msg 2>, <Tw_msg 3>, <Tw_msg 4>, <Tw_msg 5>]

・中のレコードの内容を確認したい場合、
>>> a[4].__dict__
{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x00000000046B8DA0>, 'message': 'Hello, world!', 'id': 5, 'hashtag': '1', 'time': '12:20', 'url': 'https://test.com'}

・オブジェクトの構造を確認したい場合、
>>> print(dir(a[4]))
['__class__', '__delattr__', '__dict__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__le__', '__lt__', '__mapper__', '__module__', '__ne__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__table__', '__tablename__', '__weakref__', '_decl_class_registry', '_sa_class_manager', '_sa_instance_state', 'hashtag', 'id', 'message', 'metadata', 'query', 'query_class', 'time', 'url']
>>>

 

Query the first user

    User.query.first()

         User.query.get(1) # Query based on id

 

◇◇ filter method ◇◇

・合致する条件のものを抽出
Query user with id 4 [3 ways]
    User.query.get(4)

         User.query.filter_by(id=4).all() # simple query Use the form of keyword arguments to set the field name

         User.query.filter(User.id == 4).all() # Complex Query Use other forms such as identities to set conditions

・特定の文字列で（始まる、終わる）、文字列を含む　の検索

Query all users whose name ends with g [start/include]

    User.query.filter(User.name.endswith("g")).all()

    User.query.filter(User.name.startswith("w")).all()

    User.query.filter(User.name.contains("n")).all()

         User.query.filter(User.name.like("%n%g")).all() Fuzzy Query

 Query all users whose names and mailboxes start with li [2 ways]

    User.query.filter(User.name.startswith("li"), User.email.startswith("li")).all()

・複数の条件で検索
 AND

from sqlalchemy import and_

    User.query.filter(and_(User.name.startswith("li"), User.email.startswith("li"))).all()

import and_ を書かない場合（普通、書かない）　dbモデルからのメソッドの引用であることを明示。
User.query.filter(db.and_(User.name.startswith("li"), User.email.startswith("li"))).all()


*単に検索条件を , (カンマ）で区切る記述でも可
  User.query.filter(User.name.startswith("li"), User.email.startswith("li")).all()

Queryで条件を徐々に積み上げて、検索結果を絞り込んでいくことで複数条件で、検索することも可能。（AND検索と効果は同じ）

(例）
User.query.filter(User.name=='Anthony').filter(User.email == 'test.@test.com').first()
というように検索条件を連ねていくことで、検索結果を絞りこんでいくことができる。以下のような書き方も可能。

q1 = User.query
q2 = q1.filster(User.name=='Anthony')

>> q1.all()
#
>>q2.all()
#

OR

Query age is 25 or `email` all users ending with `itheima.com`

    from sqlalchemy import or_

    User.query.filter(or_(User.age == 25, User.email.endswith("itheima.com"))).all()

    *db.or_ が普通（andと同じ）

 

Query all users whose name is not equal to wang [2 ways]

    from sqlalchemy import not_

    User.query.filter(not_(User.name == "wang")).all()

    User.query.filter(User.name != "wang").all()

         Query users with id [1, 3, 5, 7, 9]

    User.query.filter(User.id.in_([1, 3, 5, 7, 9])).all()

         All users are sorted by age from small to large, then by id from big to small, take the first 5

    User.query.order_by(User.age, User.id.desc()).limit(5).all()

         Paging query, 3 per page, query data on page 2

    pn = User.query.paginate(2, 3)

         Pn.items Get the data of this page pn.page Get the current page number pn.pages Get the total number of pages

・Not Equal
    User.query.filter(User.id !=  4).all()

・Like
　　User.query.filter(User.name.like('%eh%')).all()
      *nameというカラムの中に 'eh' という文字列を含むrow（レコード）を取得

・In と Not In
　（In）
　  User.query.filter(User.name.in_(['Anthony', 'Zach'])).all()
      *nameがAnthonyとZachに合致するrow（レコード）を、それぞれ取得
　　　in_([ リスト形式で記述] )

   （Not In）: Inと同じ書き方だが、クラス名の前にチルダをつける。
    User.query.filter(~User.name.in_(['Anthony', 'Zach'])).all()

・Null と Not Null
  (Null) 値が入力されていない（null)のレコードの取得
   User.query.filter(User.name == None)).all()

   (Not　Null)
   User.query.filter(User.name != None)).all()
・Limit
  条件にマッチするレコードの抽出する数を指定
　User.query.limit(2).all() や
   User.query.order_by(User.name).limit(2).all()
   (

・Offset
  検索する際にスキップする行数を指定。
　User.query.offset(2).all()
  (*最初の２行を検索対象から除外する。）

・Count
　クエリーに合致するレコード（row)が幾つあるか数値で返す
　User.query.filter(User.name.like('%eh%')).count()  
   >> 21
  とか。

・Inequality　（不等式）
   User.query.filter(User.id >= 4).all()

   文字列比較もできる
   User.query.filter(User.name>= 't').all()
   名前がt 以降で始まるレコードが取得できる。


 ◇◇ Order_by ◇◇

 User.query.order_by(User.name).all()
*nameがアルファベット順に昇順にソートされたリストが返される。

（降順）
SQLAlchemy文で
from sqlalchemy import desc
User.query.order_by(desc(User.id)).limit(3).all()

又は
User.query.order_by(User.id.desc()).limit(3).all()



 *.scalar()
     Return the first element of the first result or None if no rows present.
　　If multiple rows are returned,
     raises MultipleResultsFound
     >>> session.query(Item.id).filter(Item.id < 0).scalar()
     None
     '''
   (良く使う記述は)
　　if Tw_msg.query.filter_by(time=timeStr).scalar() is not None:
　　 #もし、noneでなければ（何が該当があれば）、以下の処理
           people = Tw_msg.query.filter_by(time=timeStr).all()
           print(people)
　　else:
          pass

　
＊queryの２つの記述方法
① class名.query は、(db.model)に基づいたquery.

② session.queryで、sessionを利用したqueryも可能
書き方は、上記のclass名の代替としてsession. と記述する。
session.query.all()
又は
db.session と記述する。


How can we order by a count? I.e. for example "give me the ten most common names in descending order of frequency"?

If you are using Table.query property:

from sqlalchemy import func
Table.query.with_entities(Table.column, func.count(Table.column)).group_by(Table.column).all()

If you are using session.query() method

from sqlalchemy import func
session.query(Table.column, func.count(Table.column)).group_by(Table.column).all()