Python ORM framework SQLAlchemy learning notes data query example

That we made sufficient preparation work, now it is one of the key content of the Query, and of course in front of the whole article more or less something about Query, such as a Query, the Query object is through the Session Session of the Query () method to obtain, it is important to note this method the number of parameters are variable, so we can pass in any number of parameters, the type of parameter can be any combination of the class or the name of the class, as our example illustrates this point, we let the Query object loaded the User instance.

>>> 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, from this example we get the Query object returns an iterable set of User instance tables, and then we access it through the for in statement, for example, we can output "User name" instance.name and "User fullname" instance.fullname in turn. You may also notice that there is an.order_by(user.id) after it, which, like the SQL statement, instructs the result set to be sorted by the columns of the table that user.id maps to.

Suppose we only need "user name" and "user name", the other attributes of an object instance is not interested, also can query them directly (attributes of a class name), of course, here is the premise of this class must be ORM mapping, whenever possible, any number of all can be as a kind of entity or entities for the query () method of parameters, of course will eventually return tuple type query object.

>>> 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, with the attribute name to the attribute name and the type name to the type name, as in the following example:

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

<User('ed','Ed Jones', 'f8s7ccs')> ed
<User('wendy','Wendy Williams', 'foobar')> wendy
<User('mary','Mary Contrary', 'xxg527')> mary
<User('fred','Fred Flinstone', 'blah')> fred

Of course, you can also personalize it by changing the name of a single column expression with the label() method, which is only available in columnelement-derived column objects (such as user.name) that map to the entity table:

>>> 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 saw that the full name of the entity class must be used for the instance of the query object. If we want to use the entity class name several times as the parameter of the query object (such as table join operation), we can give it an "alias", and then we can pass the parameter through the alias:

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

<User('ed','Ed Jones', 'f8s7ccs')>
<User('wendy','Wendy Williams', 'foobar')>
<User('mary','Mary Contrary', 'xxg527')>
<User('fred','Fred Flinstone', 'blah')>

Studied MySQL database, etc. This kind of classmates may know the LIMIT and OFFSET the two SQL operations, this can easily help us control record the number and location of, are often used in the data paging, of course, this kind of operation SQLAlchemy Query object has helped us to think it over, and it is simple can be done BY Python array fragmentation, the operators often use with the ORDER BY:

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

<User('wendy','Wendy Williams', 'foobar')>
<User('mary','Mary Contrary', 'xxg527')>

If we need to filter and filter specific results, we can use the filter_by() method, which USES keyword parameters:

>>> 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 use filter(), but note that it USES a more flexible sql-like expression structure, which means you can use Python's own operators inside, such as comparison:

>>> 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 that user.fullname =='Ed Jones', the comparison operation is equal to Ed Jones.

Powerful Query object, of course, there is a very useful feature, that is it can be connected in series, means that every step of the Query object operation will return a Query object, you can use the same methods in series form expression structure together, if say we want to Query the user named Ed "AND" full name is "Ed Jones" user, you can directly call series filter () twice, said in a SQL statement AND connection:

>>> 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')

<User('ed','Ed Jones', 'f8s7ccs')>

Here are some common filter operations using filter() :

Equal 1.

query.filter(User.name == 'ed')

2. The range

query.filter(User.name != 'ed')

3. LIKE

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

4. IN

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

query.filter(~User.name.in_(['ed', 'wendy', 'jack']))

6. IS NULL

filter(User.name == None)

7. IS NOT NULL

filter(User.name != None)

8. The AND

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. The OR

from sqlalchemy import or_
filter(or_(User.name == 'ed', User.name == 'wendy'))

Matching 10.

query.filter(User.name.match('wendy'))

The match() parameter content is specified by the database backend. (note: The contents of The match parameter are database backend specific.)

Ok, so much for today, basically is a poor translation, hope to help you