Python SQLAlchemy--2

This article is a series of teaching texts for Python SQLAlchemy ORM:

Next, we'll explore the use of the inquiry in more depth.

The basic use of inquiry for session.query(Mapped Class) , and then add .group_by() ,, and .order_by() .delete other methods.

Just note that the query() accepted parameters are Mapped Class (for example, the User category of the previous definition), not the table name.

For example, in Example 2, it is done as a reference User , not as a reference to user this table name.

If the search succeeds, it will return the Query Object, if it does not return to None.

First of all, query() you can use the from_statement() method, directly with the full SQL instructions to inquire, but also can be paired with params() the variables into SQL instructions.

For example:

1 2 3

rows = session.query (user). From_statement (' SELECT * from User WHERE name=:name '). Params (name= ' user1 ') For R in rows: Print (r.id)

Where the representative of the above SQL instruction :name will have a reference name, then the params(name=‘user1‘) value of name is indicated in User1.

Basically, you can do most of the inquiry, but if you want to learn more about using SQLAlchemy, you can read the following articles.

Here are a few query() examples of usage (as the user category has already been previously defined, so the following examples will be omitted directly).

SELECT

The SQL instruction of the example-SELECT * FROM user

1 2 3

For R in Session.query (User): Print (Type (r)) Print (R.name, r.username, R.password)

The results are as follows:

1 2	[Class Main__.user] 1 User1 username1 3833b3a1c69cf71a31d86cb5bb4d3866789b4d1e

The example code above is a list of the results of each column of SELECT search, and it is noted that each column of R is a user instance, so we can print this out according to the linked fields of the previous definition of the user category.

SELECT user.name FROM userquery()You can also pin a SELECT to a linked fields bit, as long as you take the Mapped Class and its dependencies, for example, just to find out all the username, then query() the parameters will be User.username .

1 2 3

For R in Session.query (User.username): Print (Type (r)) Print (R.username)

The results are as follows:

1 2	[Class Sqlalchemy.util._collections.keyedtuple] UserName1

It is worth noting that when there is a pin-to-linked fields-bit SELECT, it is a back- KeyedTuple up example, not a User-type instance of our previous definition.

WHERE

Example SQL command-SELECT * FROM user WHER id = 1

To use the WHERE condition to inquire, it is the use filter_by() or filter() method.

The difference between the two is that you must use the name of the reference as the name of the reference filter_by() , and then add the check terms. For example, the information ID is 1 filter_by(id=1) . \

filter()This is based on the Mapped Class and its dependencies, plus the search terms, and supports the Python operator. For example, the same inquiry ID is 1 of the information filter(User.id == 1) .

In fact, SQLAlchemy also provides filter() the use of string parameters, such as the inquiry ID of 1 can also be expressed as filter(id=1) .

In addition, multiple inquiries can be made in an additional .filter(…) form.

1 2 3 4 5 6

For R in Session.query (User.ID). Filter_by (id=1): Print (r.id) # Multiple Search terms For R in Session.query (User.ID). Filter (User.id==1). Filter (user.name== ' user1 '): Print (r.id)

More inquiry terms can be seen Common Filter Operators, such as like, in, not in and so on.

ORDER by

Example SQL command-SELECT user.id FROM user ORDER BY user.id DESC

The sort can be used order_by() as Mapped Class and its dependencies. For example, if you want to sort by ID linked fields, then the parameters will be User.id . In addition, the ordering of the presets is ordered as a sort of increment .

The part of the descending order is to remember the import desc module:

1	From SQLAlchemy Import desc

The following examples show the increment and descending order:

1 2 3 4 5 6 7 8 9

# on the minus For R in Session.query (User.ID). Order_by (User.id.desc ()): Print (Type (r)) Print (r.id)) # increasing For R in Session.query (User.ID). Order_by (User.id.asc ()): Print (Type (r)) Print (r.id)

LIMIT

Sometimes it is necessary to use limit to restrict the range of inquiries, such as paging, the most straightforward way is to use the Python slice function, SQLAlchemy will automatically append the LIMIT's inquiry to the SQL language of the inquiry.

Example:

1 2	For row in Session.query (User) [500:520]: Print (row.id)

If added above [500:520] , we can see the information of SQLAlchemy's inquiry instruction LIMIT ? OFFSET ? .

Where 520 minus 500 is the value of limit, which limits 20 strokes, and 500 is the OFFSET from the No. 500 information.

GROUP by

Example SQL command-SELECT * FROM user GROUP BY user.name

If you want to use it GROUP BY , use it group_by() .

1 2	For R in Session.query (User). Group_by (User.Name): Print (R.name)

Having

Example SQL command-SELECT user.name FROM user GROUP BY user.name HAVING count(user.id) > 2

Often used together with GROUP by,,, HAVING COUNT() SUM() MAX() ... and other languages.

In SQLAlchemy the having is the group_by() subsequent having() method, and the COUNT (), SUM (), MAX () and other functions are actually made by the SQLAlchemy's func module, so to use COUNT() , SUM() The c10/> function must be import func modeled.

1	From SQLAlchemy import func

Here is an example of the combination of having and COUNT (), used to print out the same name as more than 2 user names in the information table:

1 2	For row in Session.query (User). group_by (User.Name). have (Func.count (user.id) > 2): Print (Row.name)

Alias (AS)

Example SQL command-SELECT u.id FROM user AS u

The name usage of the table is the use of the AS aliasd() method, you can Mapped Class and the name of the table in the name of the inquiry.

This method requires a import aliasd module.

1	From Sqlalchemy.orm import aliased

The name of the table is named for example:

1 2 3

User_alias = aliased (User, name= ' User_alias ') For row in Session.query (User_alias): Print (Row.name)

If successful, you should see a more-than-a-pass message for SELECT FROM user AS user_alias , as shown below.

1 2	2013-08-25 11:52:45,177 INFO sqlalchemy.engine.base.Engine SELECT user_alias.id as user_alias_id, user_alias.name as User_alias_name, User_alias.username as User_alias_username, User_alias.password as User_alias_password From user as User_alias

In addition, the linked fields-bit name can be used as the SQL language, but in SQLAlchemy is label() named.

Examples are as follows:

1 2	For row in Session.query (User.name.label (' n ')): Print (ROW.N)

Calculate the number of results

If you want to calculate the results, use it count() .

For example:

1 2 3

Print Session.query (User) group_by (user.name). Having (Func.count (user.id) > 2). Count () )

Note: This differs from count() func.count()

First, scalar (), one (), all ()

In addition to checking the results, SQLAlchemy also provides some convenient ways to confirm the results of the inquiry or to return the search results.

first()Only the first result is returned, even if there are multiple results, only the first pen will be returned scalar()Only the first result is returned, and no results are returned, and if there are multiple results, an exception will be generated.

scalar()Examples are as follows:

1 2 3 4 5 6 7 8 9

Try result = Session.query (User). Scalar () Except Sqlalchemy.orm.exc.MultipleResultsFound: Print (' multipleresultsfound! ') Else If result is None: Print (' noresultfound! ') Else Print (result.id)

one()Return a piece of the result, if there are multiple or no results of the situation will produce an exception to the mistake

one()Example:

1 2 3 4 5 6 7 8

Try result = Session.query (User). One () Except Sqlalchemy.orm.exc.NoResultFound: Print (' noresultfound! ') Except Sqlalchemy.orm.exc.MultipleResultsFound: Print (' multipleresultsfound! ') Else Print (result.id)

all()Make all the results into a list return

all()Example:

1 2 3

Allres = Session.query (User). All () For R in Allres: Print (r.id)

Reference information:

http://docs.sqlalchemy.org/en/latest/

Python SQLAlchemy--2