Tutorial on querying the SQLAlchemy library in the Python ORM framework, ormsqlalchemy

Tutorial on querying the SQLAlchemy library in the Python ORM framework, ormsqlalchemy

1. Return list and Scalar (Scalar)

Previously we noticed that the Query object can return iterator values, and then we can Query them through for in. However, the all (), one (), and first () Methods of the Query object return non-iterator values. 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,    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 is restricted and only serves as the first record of the scalar returned result set:

>>> 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) <User('ed','Ed Jones', 'f8s7ccs')>

The one () method completely extracts all record rows. If there is no clear record row (this record is not found) or multiple record rows exist in the result, the error NoResultFound or MultipleResultsFound will be thrown:

>>> 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>>> try: ...   user = query.filter(User.id == 99).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', 99) No row was found for one()

2. Use the original SQL statement (Literal SQL)

The Query object can use the original SQL Query string as the Query parameter flexibly. For example, we have used the filter () and order_by () methods:

>>> for 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, many people may feel the same as me and will not be able to adapt to it, because the ORM is used to get rid of SQL statements. I did not expect to see the shadows of SQL again. Well, SQLAlchemy also needs to take care of the flexibility in use. After all, it is much easier to directly compile some query statements.

Of course, you can also bind a parameter with a string-based SQL assignment, use a colon to mark the replacement parameter, and then use the params () method to specify the corresponding 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<User('fred','Fred Flinstone', 'blah')>

At this point, the appearance of SQL statements has begun to take shape. In fact, we can use SQL statements more challenging. What? In this way, the value of ORM is lost! Don't worry. Here we will only introduce the support for this kind of usage. Of course, we recommend that you do not have to write it like this, because there may be compatibility issues. After all, the SQL dialects of different databases are different. However, if you want to use a native SQL statement directly, in the ing Class queried by query, you must ensure that the columns referenced by the statement are still managed by the ing Class, for example, in 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 directly use the column name in the query () to assign the desired column and get rid of the constraints of the ing class:

>>> session.query("id", "name", "thenumber12").\...     from_statement("SELECT id, name, 12 as "...         "thenumber12 FROM users where name=:name").\...         params(name='ed').all()SELECT id, name, 12 as thenumber12 FROM users where name=?('ed',) [(1, u'ed', 12)]

3. Count)

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 the number of rows in the returned result set. Let's take a look at the generated SQL statement. SQLAlchemy first retrieves the set of all rows that meet the condition, then, we use SELECT count (*) to calculate the number of rows. Of course, those who have some SQL knowledge may know that this statement can be written in a more streamlined manner, such as SELECT count (*) FROM table. Of course, the modern version of SQLAlchemy will not try to figure out this idea.

If we want to make the query statement more refined or specify the columns for statistics, we can use the expression func. count () directly uses the count function. For example, the following example describes statistics and returns each unique username word:

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

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