PythonORM framework SQLAlchemy learning notes-data query instance

This article mainly introduces the data query instance of the PythonORM framework SQLAlchemy learning notes. if you need it, you can refer to the preparation work we have done in the early stage. now this is one of the key content to query, of course, the previous article involves more or less queries. for example, a Query object is obtained through the query () method of the Session, note that the number of parameters in this method is variable. that is to say, we can input any number of parameters. the parameter type can be any combination of classes or the name of classes, this is illustrated in the following example. we have loaded the User instance into the Query object.

The 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 users order by users. id
()

Ed Ed Jones
Wendy Wendy Williams
Mary Mary Contrary
Fred Fred Flinstone

Of course, in this example, we can obtain that the Query object returns a set of User instance tables that can be iterated, and then we can access the table using the for in statement. for example, here we can output the "username" instance in sequence. name and "user full name" instance. fullname. You may also notice that there is a. order_by (User. id) later. this is the same as the SQL statement, indicating that the result set is sorted by the table columns mapped by User. id.

Assume that we only need "user name" and "user name". if you are not interested in other attributes of the object instance, you can directly query them (class attribute names ), of course, the premise here is that this class must be ORM ing. at any time, any number of class entities or column-based entities can be used as parameters of the query () method, of course, the final Query object will return the tuples.

The code is 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 type of the returned tuples can also be seen as a common Python object. the attribute name is the attribute name, and the type name is the type name. for example:

The 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 customize it. for example, you can use the label () method to change the name of a separate column expression. of course, this method only applies to the column element object (ColumnElement-derived) mapped to the object table) such as User. name ):

The 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

Previously, we can see that the query object instance must use the full name (User) of the object class. Suppose we want to use this object class name multiple times as the query object (such as table join operation) query () then we can give it an "alias", and then we can pass the parameter through the alias:

The 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
()

Those who have studied MySQL and other such databases may know the LIMIT and offset SQL operations. This helps us easily 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 helped us think about it, and it is very easy to implement through Python array sharding. this operation is often used with order:

The 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 users order by users. id
LIMIT? OFFSET?
(2, 1)

If we need to filter out specific results, we can use the filter_by () method, which uses the keyword parameter:

The 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 filter () can achieve the same purpose, but note that it uses a more flexible expression structure similar to SQL statements, this means that you can use Python operators internally, such as comparison operations:

The 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

Note that User. fullname = 'ed Jones 'is selected only when the comparison operation is equal to Ed Jones.

Of course, a powerful Query object has a very useful feature, that is, it can be connected in series, meaning that each step of the Query object will return a Query object, you can concatenate the same method to form an expression structure. if we want to query users whose username is "ed" and full name is "Ed Jones, you can call filter () twice in tandem to indicate the AND connection in the SQL statement:

The code is 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 ')

The following lists some common filtering operations using filter:

1. equal

The code is as follows:

Query. filter (User. name = 'Ed ')

2. not equal

The code is as follows:

Query. filter (User. name! = 'Ed ')

3. LIKE

The code is as follows:

Query. filter (User. name. like ('% ed % '))

4. IN

The 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

The code is as follows:

Query. filter (~ User. name. in _ (['ed', 'Wendy ', 'Jack'])

6. IS NULL

The code is as follows:

Filter (User. name = None)

7. IS NOT NULL

The code is as follows:

Filter (User. name! = None)

8. AND

The 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

The code is as follows:

From sqlalchemy import or _
Filter (or _ (User. name = 'ed', User. name = 'Wendy '))

10. match

The code is as follows:

Query. filter (User. name. match ('Wendy '))

The content of the match () parameter is specified by the database background. (Note: The original article is "the contents of The match parameter are database backend specific.". I don't quite understand what this operation means)

Well, today we will introduce so much, which is basically a poor translation. I hope it will help you.