We did a lot of preparatory work, now it is one of the key content query, of course, the previous article is more or less interspersed with some of the query, such as a query object is through the session of the query () method to obtain, It is important to note that the parameter number of this method is variable, that is, we can pass in any number of arguments, the type of the parameter can be any class combination or the name of the class, and then our example illustrates this point, we have the query object loaded the user instance.
Copy CodeThe code is 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, by this example, we get a query object that returns a set of iterations of the user instance table, and then we access it through the for-in statement, for example, you can output the user name Instance.name and the user full name Instance.fullname in turn. You may also notice that there is a. order_by (User.ID), which is the same as the SQL statement, which indicates that the result set is sorted by the table column mapped by user.id.
Assuming that we only need "username" and "user name" and not interested in other properties of the object instance, you can also query them directly (the property name of the Class), but the premise here is that the class must be an ORM mapping, whenever an arbitrary number of class entities or column-based entities can be used as query () method, and of course the final query object returns the tuple type.
Copy the 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 thought of as a normal Python object, a property name Attribution name, and a type name to the type name, such as the following example:
Copy CodeThe code is 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
()
Ed
Wendy
Mary
Fred
Of course you can also personalize it by using the label () method to change the name of the individual column expression, but this method only exists in the Column element object (columnelement-derived) that is mapped to the entity table (for example, User.Name):
Copy CodeThe code is 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 had to use the full name of the entity class (User), if we were to use the entity class famous as a parameter of query object (such as table join operation), we could give it an "alias" and then pass the parameter through the alias:
Copy CodeThe code is 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
()
Students who have learned MySQL and other such databases may know the two SQL operations of limit and offset, which can help us to control the number and location of records, and is often used for data paging operations, of course, this kind of operation SQLAlchemy's query object has helped us to think about it, And it's easy to do this with Python array shards, which are often used with order by:
Copy CodeThe code is 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)
If we need to filter the specific results, we can use the Filter_by () method, which uses the keyword parameters:
Copy CodeThe code is 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 using filter () can do the same thing, but note that it uses a more flexible expression structure like SQL statements, which means that you can use Python's own operators internally, such as comparison operations:
Copy CodeThe code is 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 here that the user.fullname== ' Ed Jones ', compared with Ed Jones, is equally screened.
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, you can concatenate the same method together to form an expression structure, if we want to query the user named "Ed" and the Full name of "Ed Jones "User, you can call filter () two times directly in series, representing the and connection in the SQL statement:
Copy the 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 ')
Here are some common filter filtering operations using filter ():
1. Equal
Copy the Code code as follows:
Query.filter (User.Name = = ' ed ')
2. Unequal
Copy CodeThe code is as follows:
Query.filter (User.Name! = ' Ed ')
3. Like
Copy CodeThe code is as follows:
Query.filter (User.name.like ('%ed% '))
4. In
Copy CodeThe code is 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 CodeThe code is as follows:
Query.filter (~user.name.in_ ([' Ed ', ' Wendy ', ' Jack ')]
6. Is NULL
Copy CodeThe code is as follows:
Filter (User.Name = = None)
7. is not NULL
Copy CodeThe code is as follows:
Filter (User.Name! = None)
8. and
Copy CodeThe code is 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 CodeThe code is as follows:
From SQLAlchemy import or_
Filter (or_ (user.name = = ' ed ', user.name = = ' Wendy '))
10. Match
Copy CodeThe code is 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 is database backend specific.", not quite understand the meaning of this operation)
Well, today introduced so much, basically is a lame translation, I hope that everyone can help
