Earlier we did a lot of preparation work, now it is one of the key content of the query, of course, the previous article more or less interspersed some of the things about the query, such as a query object is through the session of the query () method to obtain, Note that the number of parameters in this method is variable, that is, we can pass in any number of arguments, the type of the argument can be any class combination or the name of the class, and then we have the query object load the user instance with the example below.
Copy Code code as follows:
>>> for instance in Session.query (User). Order_by (user.id):
... print instance.name, instance.fullname
SELECT Users.id as users_id,
Users.name as Users_name,
Users.fullname as Users_fullname,
Users.password as Users_password
From the users order by users.id
()
Ed Ed Jones.
Wendy Wendy Williams
Mary Mary Contrary
Fred Fred Flinstone
Of course, in this case, we get the query object returns a set of iterations of the user instance table, which we then access through the for in statement, such as the "username" Instance.name and the "User full name" Instance.fullname, in turn. You may also notice that there is a. order_by (user.id), the same as the SQL statement, that indicates that the result set is sorted by the table columns mapped by user.id.
Suppose we just need "username" and "Full user name". For other properties of object instances, you can also query them directly (the property name of the Class), although the premise here is that the class must be an ORM mapping, and whenever any number of class entities or columns based entities can be used as query () method, and of course the final query object returns the tuple type.
Copy Code code as follows:
>>> for name, FullName in Session.query (User.Name, User.fullname):
... print name, fullname
SELECT Users.name as Users_name,
Users.fullname as Users_fullname
From users
()
Ed Ed Jones.
Wendy Wendy Williams
Mary Mary Contrary
Fred Fred Flinstone
The returned tuple type can also be considered a generic Python object, attribute name Attribution name, type name to type name, such as the following example:
Copy Code code as follows:
>>> for row in Session.query (User, User.Name). All ():
... print row. User, Row.name
SELECT Users.id as users_id,
Users.name as Users_name,
Users.fullname as Users_fullname,
Users.password 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 personalize it, such as by changing the name of a separate column expression by the label () method, of course it exists only in the Column element object (columnelement-derived) that maps to the entity table (for example, User.Name):
Copy Code code as follows:
>>> for row in Session.query (User.name.label (' Name_label ')). All ():
... print (Row.name_label)
SELECT Users.name as Name_label
From users
()
Ed
Wendy
Mary
Fred
Before we saw that the query object instance must use the full name of the entity class (User). Let's say we're going to use this entity class name multiple times for the parameters of Query objects (such as table join operations) in query (), we can give it an "alias", and then we can pass the argument through the alias:
Copy Code code as follows:
>>> from Sqlalchemy.orm import aliased
>>> User_alias = aliased (user, name= ' user_alias ')
>>> for row in Session.query (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 ') >
Such databases as MySQL and other students may know the two SQL operations limit and offset, this can be very convenient to help us control the number and location of records, often used for data paging operations, of course, such operations SQLAlchemy query object has helped us to think about it, And it's easy to do this with Python array fragmentation, which is often used in conjunction with an ORDER by:
Copy Code code as follows:
>>> for u in Session.query (User) order_by (user.id) [1:3]:
... print u
SELECT Users.id as users_id,
Users.name as Users_name,
Users.fullname as Users_fullname,
Users.password as Users_password
From the users order by users.id
LIMIT? OFFSET?
(2, 1)
<user (' Wendy ', ' Wendy Williams ', ' Foobar ') >
<user (' Mary ', ' Mary contrary ', ' xxg527 ') >
If we need filtering to filter for specific results, you can use the Filter_by () method, which uses the keyword parameters:
Copy Code code as follows:
>>> for Name, in Session.query (user.name). \
... filter_by (fullname= ' Ed Jones '):
... print name
SELECT Users.name as Users_name from users
WHERE users.fullname =?
(' Ed Jones ',)
Ed
or use filter () to achieve the same goal, but note that it uses a more flexible expression structure similar to SQL statements, which means that you can use Python's own operators within it, such as comparison operations:
Copy Code code as follows:
>>> for Name, in Session.query (user.name). \
... filter (user.fullname== ' Ed Jones '):
... print name
SELECT Users.name as Users_name from users
WHERE users.fullname =?
(' Ed Jones ',)
Ed
Notice the user.fullname== ' Ed Jones ', which is comparable to the same screening as Ed Jones.
Of course, the powerful query object has a very useful feature, that is, it can be concatenated, meaning that each step of the query object will return a query object, and you can concatenate the same method together to form an expression structure, if we are going to query the user name "Ed" and the Full name is "Ed Jones "User, you can call filter () two times directly in series, representing the and connection in the SQL statement:
Copy Code code as follows:
>>> for user in Session.query (user). \
... filter (user.name== ' Ed '). \
... filter (user.fullname== ' Ed Jones '):
... print user
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 =? and Users.fullname =?
(' Ed ', ' Ed Jones ')
<user (' Ed ', ' Ed Jones ', ' F8s7ccs ') >
Here are some common filtering actions that use filter ():
1. Equal
Copy Code code as follows:
Query.filter (User.Name = = ' ed ')
2. Unequal
Copy Code code as follows:
Query.filter (User.Name!= ' Ed ')
3. Like
Copy Code code as follows:
Query.filter (User.name.like ('%ed% '))
4. In
Copy Code code as follows:
Query.filter (User.name.in_ ([' Ed ', ' Wendy ', ' Jack ')]
# works with Query objects too:
Query.filter (User.name.in_) (Session.query (User.Name). Filter (User.name.like ('%ed% ')))
5. Not in
Copy Code code as follows:
Query.filter (~user.name.in_ ([' Ed ', ' Wendy ', ' Jack ')]
6. Is NULL
Copy Code code as follows:
Filter (User.Name = None)
7. is not NULL
Copy Code code as follows:
Filter (User.Name!= None)
8. and
Copy Code code as follows:
From SQLAlchemy import And_
Filter (and_ (user.name = = ' ed ', User.fullname = ' ed Jones '))
# or call filter ()/filter_by () multiple times
Filter (User.Name = = ' ed '). Filter (User.fullname = = ' ed Jones ')
9. OR
Copy Code code as follows:
From SQLAlchemy import or_
Filter (or_ (user.name = = ' ed ', User.Name = ' Wendy '))
10. Matching
Copy Code code as follows:
Query.filter (User.name.match (' Wendy '))
The match () parameter content is specified by the database background. (Note: The original is "the contents of the match parameter are database backend specific.", I don't quite understand the meaning of this operation)
OK, so much to introduce today, basically is a crappy translation, I hope to be able to help
