The application of Query

The Session query function returns a Query object. The Query function can accept multiple argument types. This can be a class or a instrumented descriptor for a class.
For instance in session.query(User).order_by(user.id): print (instance.name, instance.fullname)Copy the code
Query also accepts orm-instrumented Descriptors as entries, returning tuples in the same order when multiple parameters are passed in
for name, fullname in session.query(User.name, User.fullname):
	print(name, fullname)
Copy the code
The tuples returned by Query are provided by the KeyedTuple class, whose members can be retrieved as instance variables in addition to being accessed with subscripts. The corresponding variable name is the same as that of the class variable being queried
for row in session.query(User, User.name).all():
...    print(row.User, row.name)
<User(name='ed', fullname='Ed Jones', password='f8s7ccs')> ed
<User(name='wendy', fullname='Wendy Williams', password='foobar')> wendy
<User(name='mary', fullname='Mary Contrary', password='xxg527')> mary
<User(name='fred', fullname='Fred Flinstone', password='blah')> fred
Copy the code

You can use label () to customize the name of the instance variable corresponding to the Descriptor

for row in session.query(User.name.label('name_label')).all():
...    print(row.name_label)
ed
wendy
mary
fred
Copy the code

For class parameters, aliased is required to achieve the same customization

>>> from sqlalchemy.orm import aliased
>>> user_alias = aliased(User, name='user_alias')

SQL>>> for row in session.query(user_alias, user_alias.name).all():
...    print(row.user_alias)
<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>
<User(name='wendy', fullname='Wendy Williams', password='foobar')>
<User(name='mary', fullname='Mary Contrary', password='xxg527')>
<User(name='fred', fullname='Fred Flinstone', password='blah')>
Copy the code
OFFSET and LIMIT, which can be done with Python’s Array slice.
for u in session.query(User).order_by(User.id)[1:3]
	print(u)
<User(name='wendy', fullname='Wendy Williams', password='foobar')>
<User(name='mary', fullname='Mary Contrary', password='xxg527')>
Copy the code

The above procedure only involves fetching the whole, and does not filter the commonly used functions filter_by and filter. The latter is more flexible than the former, and you can use Python operators in the latter arguments.

for name in session.query(User.name).filter_by(fullname='shawna'):
	print name
    
for name in sesssion.query(User.name).filter(User.fullname=='shawna'):
	print name
Copy the code

Query objects are generative, which means you can call them in tandem

The relation between the concatenated filters is and.

for user in session.query(User).filter
(User.name=='shawna'
').filter(User.fullname=='Jones'):
	print user

<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>
Copy the code

Common filter operator

Filter_by is available ？？？？？

  • equals
query.filter(User.name == 'ed')
Copy the code
  • not equals
query.filter(User.name ! = 'ed')Copy the code
  • LIKE
query.filter(User.name.like(%sha%))
Copy the code
  • IN
query.filter(User.name.in_(['as','ed', 'jack']))

query.filter(User.name.in_(session.query(User.name).filter(User.name.like('%jack%'))))
Copy the code
  • NOT IN
query.filter(~User.name.in_(['ed']))
Copy the code
  • IS NULL
query.filter(User.name.is_(None))
query.filter(User.name == None)
Copy the code
  • IS NOT NULL
query.filter(User.name ! = None) query.filter(User.name.isnot(None))Copy the code
  • AND
query.filter(User.name == 'shawna', User.fullname == 'Ed')

query.filter(and_(User.name == 'ed', User.fulllname == 'jack'))

query.filter(User.name == 'ed').filter(User.fullname == 'jack')
Copy the code
  • OR
query.filter(or_(User.name == 'ed', User.name == 'sha'))
Copy the code
  • MATCH
query.filter(User.name.match('wendy'))
Copy the code

Returns the list and single item (Scalar)

  • All () returns a list
>>> query = session.query(User).filter(User.name.like('%ed')).order_by(User.id)
SQL>>> query.all()
[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>,
      <User(name='fred', fullname='Fred Flinstone', password='blah')>]
Copy the code
  • First () returns at most one result, and it returns the result as a single item, not as a single element tuple.
>>> query.first()
<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>
Copy the code
  • One () returns only one query result. An error is reported when the number of results is less than one or more.
>>> user = query.one()
Traceback (most recent call last):
...
MultipleResultsFound: Multiple rows were found for one()
Copy the code
  • One_or_none () : As the name indicates, None is returned when the number of results is 0, with more than one error
  • Scalar () is similar to one(), but returns a single item instead of a tuple

Embedding with SQL

  • You can use SQL statements in Query via text(). Such as:

Write the string comparison directly to filter

>>> from sqlalchemy import text
>>> for user in session.query(User).\
...             filter(text("id<224")).\
...             order_by(text("id")).all():
...     print(user.name)
ed
wendy
mary
fred
Copy the code
  • The params() method is used to pass parameters

Params defines parameters that can be called from text

sesssion.query(User).filter(text("id<:value and name=:name")).params(value=224, name='shawna').order_by(User.id).one()

<User(name='fred', fullname='Fred Flinstone', password='blah')>
Copy the code
  • Also, you can use the full SQL statement directly, but be careful to write the table names and columns correctly.
>>> session.query(User).from_statement(
...                     text("SELECT * FROM users where name=:name")).\
...                     params(name='ed').all()
[<User(name='ed', fullname='Ed Jones', password='f8s7ccs')>]

Copy the code

count

Query defines a handy count function, count()

>>> session.query(User).filter(User.name.like('%ed')).count()
SELECT count(*) AS count_1
FROM (SELECT users.id AS users_id,
                users.name AS users_name,
                users.fullname AS users_fullname,
                users.password AS users_password
FROM users
WHERE users.name LIKE ?) AS anon_1
('%ed',)
2
Copy the code
  • from sqlalchemy import func
>>> 
from sqlalchemy import funcsession.query(func.count('*')).select_from(User).scalar()
SELECT count(?) AS count_1
FROM users
('*',)
4
Copy the code

Read the article from www.jianshu.com/p/8d085e2f2…