A tutorial on the query operations of SQLAlchemy libraries in the Python ORM framework _python

1. Return list and scalar (Scalar)

We noticed earlier that the query object can return the value of the iteration (iterator value), and then we can query it through for. However, the all (), one (), and the Primary () methods of the query object return a Non-iterator value, such as All (), which 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,
    Users.password as Users_password from the
users
WHERE 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_password from the
users
WHERE users.name like? ORDER by Users.id
 LIMIT? OFFSET?
('%ed ', 1, 0)
 
<user (' Ed ', ' Ed Jones ', ' F8s7ccs ') >

One () method, which extracts all of the record rows, and if there is no clear line of records (not found) or there are multiple record rows in the result, an error exception Noresultfound or Multipleresultsfound is thrown:

>>> from Sqlalchemy.orm.exc import multipleresultsfound
>>> try: ...   user = Query.one () ...
except Multipleresultsfound, E:
...   Print e
SELECT users.id as users_id,
    users.name as Users_name,
    users.fullname as Users_fullname,
    Users.password as Users_password from the
users
WHERE users.name like? ORDER by Users.id
('%ed ',)
 
multiple rows were found to one ()

>>> from Sqlalchemy.orm.exc Import Noresultfound
>>> Try: 
...   user = Query.filter (user.id = =). One () ...
except Noresultfound, E: ...   Print e
SELECT users.id as users_id,
    users.name as Users_name,
    users.fullname as Users_fullname,
    Users.password as Users_password from the
users
WHERE users.name like? and users.id =? ORDER by Users.id
('%ed ',)
 
No Row is found for one ()

2. Use literal SQL (Literal sql)

The query object has the flexibility to use a literal SQL query string as an argument, such as the filter () and order_by () method we used before:

>>> for user in Session.query (user). \ ...       Filter ("id<224").       Order_by ("id"). All (): 
.   Print User.Name
SELECT users.id as users_id,
    users.name as Users_name,
    Users.fullname as users_fullname,< C10/>users.password as Users_password from the
users
WHERE id<224 order by ID
()
 
Ed
Wendy.
Mary
Fred.

Of course, a lot of people may feel the same way I do, 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 need to take care of the flexibility of the use of it, after all, some query directly into the sentence is much easier.

Of course the binding parameter can also use a string based SQL assignment, use a colon to mark an alternate parameter, and then use 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_password from
users
WHERE id< User (' Fred ', ' Fred Flinstone ', ' blah ') >

Here, the appearance of the SQL statement has begun to appear, in fact, we can be more extreme, directly using SQL statements, what? This will lose the value of ORM! Don't worry, this is just a introduction to support this use, of course, I do not recommend the last resort, try not to write, because there may be compatibility issues, after all, the different database SQL dialect. However, one thing to note is that if you want to use a native SQL statement directly, in the mapping class queried by query (), you must ensure that the column that the statement refers to is still managed by the mapping 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 ',)
 
[<user (' Ed ', ' Ed Jones ', ' F8s7ccs ')]

We can also get rid of the shackles of the mapping class by using the column names directly in query () to assign the columns we want:

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

3. Count (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_1 from
( SELECT users.id as users_id,
        users.name as Users_name,
        users.fullname as Users_fullname,
        Users.password as Users_password from the
users
WHERE users.name like? As Anon_1
('%ed ',)
 
2

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

If we want the query to be more refined or to be clear about the columns to be counted, we can use the Count function directly using the expression Func.count (), for example, the following example describes statistics and returns 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_name from the
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 do this by using the following example:

>>> session.query (Func.count (' * ')). Select_from (User). Scalar ()
select count (?) As Count_1
from Users
(' * ',)
 
4

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

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