A tutorial on querying operations for SQLAlchemy libraries in the ORM framework of Python

1. Return to list and scalar (scalar)

Before we notice that the query object can return an iterative value (iterator value), then we can query through for. However, the all (), one (), and first () methods of the Query object will return a non-iterative value (non-iterator value), for example all () returns a list:

>>> query = Session.query (User) .\>>>     filter (User.name.like ('%ed ')). Order_by (User.ID) > >> Query.all () SELECT users.id as users_id,    users.name as Users_name,    Users.fullname as users_fullname,< C4/>users.password as Users_passwordfrom userswhere users.name like? ORDER by Users.id ('%ed ',) [User (' Ed ', ' Ed Jones ', ' F8s7ccs '), User (' Fred ', ' Fred Flinstone ', ' blah ')]

The first () method restricts and returns only one record of the result set as a scalar:

>>> Query.first () SELECT users.id as users_id,    users.name as Users_name,    Users.fullname as Users_ FullName,    Users.password as Users_passwordfrom userswhere users.name like? ORDER by Users.id LIMIT? OFFSET? ('%ed ', 1, 0) 
 
  

The one () method, which extracts all the rows of records completely, and throws an error exception noresultfound or Multipleresultsfound if there is no clear record line (the record is not found) or if there are multiple rows of records in the result:

>>> from Sqlalchemy.orm.exc Import multipleresultsfound>>> try: ...   user = Query.one () ... except Multipleresultsfound, E: ...   Print Eselect users.id as users_id, users.name as Users_name, users.fullname as    users_fullname,    Users.password as Users_passwordfrom userswhere users.name like? ORDER by Users.id ('%ed ',) multiple rows were found for one () >>> from sqlalchemy.orm.exc import noresultfound>& Gt;> Try:   ... user = Query.filter (user.id = =). One () ... except Noresultfound, E: ...   Print Eselect users.id as users_id, users.name as Users_name, users.fullname as    users_fullname,    Users.password as Users_passwordfrom userswhere users.name like? and users.id =? ORDER by Users.id ('%ed ', in the loop) No row is found for one ()

2. Using literal SQL (Literal sql)

The query object has the flexibility to use the literal SQL query string as a parameter for queries, such as the filter () and order_by () methods we used before:

>>> for the user in Session.query (user).       Filter ("id<224").       Order_by ("id"). All (): ...   Print User.nameselect users.id as users_id, users.name as Users_name, users.fullname as    users_fullname,    Users.password as Users_passwordfrom userswhere id<224 ORDER by ID () edwendymaryfred

Of course a lot of people may feel the same as me, will be a bit uncomfortable, because the use of ORM is to get rid of SQL statements, I did not expect to see the shadow of SQL now. Oh, SQLAlchemy also to take care of the use of flexibility, after all, some query statements directly into the more easily.

Of course, binding parameters can also be assigned using string-based SQL, using colons to mark substitution parameters, and then using the params () method to specify the appropriate value:

>>> session.query (User). Filter ("Id<:value and Name=:name").   params (value=224, name= ' Fred '). Order_by (User.ID). One () SELECT users.id as users_id,    Users.name as Users_name,    Users.fullname as Users_fullname,    Users.password as Users_passwordfrom userswhere ID
 
  

Here, the SQL statement looks like the beginning of the beginning, in fact, we can be more extreme, direct use of SQL statements, what? This will lose the value of ORM! Don't worry, this is just an introduction to support this usage, of course, I do not recommend the last resort, try not to write, because there may be compatibility problems, after all, the database of the SQL dialect is different. One thing to note, however, is that if you want to use native SQL statements directly, in the mapping class queried by query (), you must ensure that the columns that the statement refers to are still managed by the mapped class, such as the following example:

>>> session.query (User). From_statement (... ")           SELECT * from users where Name=:name ").           Params (name= ' Ed '). All () SELECT * from users where name=? (' Ed ',) [
 
  
   
  ]
 
  

We can also use column names directly in query () to assign the columns we want and get rid of the constraints of the mapping class:

>>> session.query ("id", "name", "Thenumber12").     From_statement ("Select ID, Name, ...         ") Thenumber12 from users where Name=:name ").         Params (name= ' Ed '). All () SELECT ID, name, and Thenumber12 from users where name=? (' Ed ',) [(1, U ' Ed ', 12)]

3. Counting (counting)

For query, the Count function also has a separate method called COUNT ():

>>> session.query (User). Filter (User.name.like ('%ed ')). Count () SELECT count (*) as Count_1from (SELECT Users.id as users_id,        users.name as Users_name,        users.fullname as Users_fullname, Users.password as Users_        Passwordfrom userswhere users.name like?) As anon_1 ('%ed ',) 2

The count () method is used to determine how many rows are in the returned result set, so let's take a look at the resulting SQL statement, SQLAlchemy all the rows that meet the criteria, and then count the number of rows by select COUNT (*). Of course, a bit of SQL knowledge of the classmate may know that this statement can be written in a more concise way, such as SELECT COUNT (*) from table, of course, the modern version of SQLAlchemy will not try to figure out the idea.

If we want to make the query more concise or to be clear about the columns to be counted, we can use the Count function directly through the expression Func.count (), such as the following example to introduce statistics and return each unique user name:

>>> from SQLAlchemy import func>>> session.query (Func.count (user.name), User.Name). Group_by ( User.Name). All () SELECT count (users.name) as count_1, users.name as Users_namefrom users GROUP by Users.name () [(1, U ' Ed ') , (1, U ' Fred '), (1, U ' Mary '), (1, U ' Wendy ')]

For the simple Select COUNT (*) from table statement just mentioned, we can use the following example to implement:

>>> session.query (Func.count (' * ')). Select_from (User). Scalar () select count (?) As Count_1from users (' * ',) 4

Of course, if we directly count the user's primary key, the above statement can be more concise, we can save the Select_from () method:

>>> session.query (Func.count (user.id)). Scalar () SELECT count (users.id) as Count_1from users () 4

