We made sufficient preparations in the early stage, and now it is one of the key contents to query. Of course, the previous article has more or less interspersed with some queries. For example, a query object is obtained through the query() method of the Session session. It should be noted that the number of parameters in this method is variable, that is, we can pass in as many parameters as the type of parameters, and the type of parameters can be any class combination or the name of the class. Our example illustrates this point. We let the Query object load the User instance.
>>> for instance in (User).order_by():
... print ,
SELECT AS users_id,
AS users_name,
AS users_fullname,
AS users_password
FROM users ORDER BY
()
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone
Of course, through this example, we get that the Query object returns a set of iterable User instance tables, and then we access them through the for in statement, for example, here you can output "user name" and "user full name" in turn. You may also notice that there is a .order_by() behind it, which is the same as the SQL statement, instructing the result set to sort by the mapped table column.
Assuming that we only need "user name" and "user full name", if we are not interested in other attributes of the object instance, we can also directly query them (class attribute name). Of course, the premise here is that this class must be ORM mapped. Whenever, any number of class entities or column-based entities can be used as parameters of the query() method. Of course, the Query object will return the tuple type in the end.
>>> for name, fullname in (, ):
... print name, fullname
SELECT AS users_name,
AS users_fullname
FROM users
()
ed Ed Jones
wendy Wendy Williams
mary Mary Contrary
fred Fred Flinstone
The returned tuple type can also be regarded as a normal Python object, the attribute name belongs to the attribute name, and the type name belongs to the type name, such as the following example:
>>> for row in (User, ).all():
... print ,
SELECT AS users_id,
AS users_name,
AS users_fullname,
AS users_password
FROM users
()
<User('ed','Ed Jones', 'f8s7ccs')> ed
<User('wendy','Wendy Williams', 'foobar')> wendy
<User('mary','Mary Contrary', 'xxg527')> mary
<User('fred','Fred Flinstone', 'blah')> fred
Of course, you can also do some personalization, such as changing the name of a separate list expression through the label() method. Of course, this method only exists in the ColumnElement-derived object mapped to the entity table (for example):
>>> for row in (('name_label')).all():
... print(row.name_label)
SELECT AS name_label
FROM users
()
ed
wendy
mary
fred
Previously, we saw that the query object instance must use the full name of the entity class (User). Suppose we want to use this entity class name as the parameter of query() multiple times (such as table join operation), then we can give it a "alias" and then pass the parameters through the alias:
>>> from import aliased
>>> user_alias = aliased(User, name='user_alias')
>>> for row in (user_alias, user_alias.name).all():
... print row.user_alias
SELECT user_alias.id AS user_alias_id,
user_alias.name AS user_alias_name,
user_alias.fullname AS user_alias_fullname,
user_alias.password AS user_alias_password
FROM users AS user_alias
()
<User('ed','Ed Jones', 'f8s7ccs')>
<User('wendy','Wendy Williams', 'foobar')>
<User('mary','Mary Contrary', 'xxg527')>
<User('fred','Fred Flinstone', 'blah')>
Students who have studied databases such as MySQL may know the two SQL operations: LIMIT and OFFSET. This can help us control the number and location of records and is often used for data paging operations. Of course, this type of operation SQLAlchemy's Query object has been designed for us, and it can be easily implemented through Python array sharding. This operation is often used with ORDER BY:
>>> for u in (User).order_by()[1:3]:
... print u
SELECT AS users_id,
AS users_name,
AS users_fullname,
AS users_password
FROM users ORDER BY
LIMIT ? OFFSET ?
(2, 1)
<User('wendy','Wendy Williams', 'foobar')>
<User('mary','Mary Contrary', 'xxg527')>
If we need to filter specific results, we can use the filter_by() method, which uses keyword parameters:
>>> for name, in ().\
... filter_by(fullname='Ed Jones'):
... print name
SELECT AS users_name FROM users
WHERE = ?
('Ed Jones',)
ed
Or using filter() can also achieve the goal, but it should be noted that it uses a more flexible expression structure similar to SQL statements, which means that you can use Python's own operators internally, such as comparison operations:
>>> for name, in ().\
... filter(=='Ed Jones'):
... print name
SELECT AS users_name FROM users
WHERE = ?
('Ed Jones',)
ed
Note that =='Ed Jones' here, filter only if the comparison operation is equal to Ed Jones.
Of course, a powerful Query object has a very useful feature, that is, it can be concatenated, which means that each step of the Query object will return a Query object. You can concatenate the same methods together to form an expression structure. If we want to query a user with the user name "ed" and the full name "Ed Jones", you can directly call filter() twice in series to represent the AND connection in the SQL statement:
>>> for user in (User).\
... filter(=='ed').\
... filter(=='Ed Jones'):
... print user
SELECT AS users_id,
AS users_name,
AS users_fullname,
AS users_password
FROM users
WHERE = ? AND = ?
('ed', 'Ed Jones')
<User('ed','Ed Jones', 'f8s7ccs')>
Here are some common filtering operations using filter():
1. Equal
2. Not waiting
3. LIKE
4. IN
(.in_(['ed', 'wendy', 'jack']))
# works with query objects too:
(.in_(().filter(('%ed%'))))
5. NOT IN
6. IS NULL
7. IS NOT NULL
8. AND
from sqlalchemy import and_
filter(and_( == 'ed', == 'Ed Jones'))
# or call filter()/filter_by() multiple times
filter( == 'ed').filter( == 'Ed Jones')
9. OR
from sqlalchemy import or_
filter(or_( == 'ed', == 'wendy'))
10. Match
(('wendy'))
The match() parameter content is specified by the database background. (Note: The original text is "The contents of the match parameter are database backend specific." I don't quite understand the meaning of this operation)
Okay, I will introduce so much today. Basically, they are all crappy translations. I hope they can help you