The Query method in SQLAlchemy

Executing queries in SQLAlchemy is done through the query method of the Session object. The query method is very flexible and you can use different query methods to find the data as needed, for example, below one by one.
1. Find directly through the mapping class:

#Querying User Instance
For instance Insession.query (User). Order_by (user.id):
Print Instance.name,instance.fullname

This method can query the data in the database table represented by this mapping class as long as the mapping class is used as a parameter in the Query method. It is equivalent to the following SQL statement:

Selectusers.id asusers_id, Users.name asusers_name,
Users.fullname Asusers_fullname, Users.password Asusers_password
From Users Users.id

2. Query through the attribute fields of the mapping class:

#Querying by Orm-instrument
For Name,fullnamein Session.query (user.name,user.fullname):
Print Name,fullname

Instead of querying all the fields of the mapping class as in the first method, this method specifies the specific field of the query, which is equivalent to executing the following SQL statement:

Selectusers.name Asusers_name, Users.fullname asusers_fullname
From users

The result of a 3.query query is saved in a tuple, so we can specify in query that it returns the entire mapping class object and some of its property fields:

#Querying as a Python object
For row insession.query (User,user.name). All ():
Print row. User,row.name

This query method can return a user object and the value of its Name property field, which is equivalent to executing the following SQL statement:

Selectusers.id asusers_id, Users.name asusers_name, Users.fullname asusers_fullname, Users.password ASusers_password
From users

Its output is a tuple and a string:

<</span>user ("Ed", "Ed Jones", "f8x902") > Ed
<</span>user ("Wendy", "Wendy Williams", "Foobar") > Wendy
<</span>user ("Marry", "Marry contrary", "xxg527") > Marry
<</span>user ("Fred", "Fred Flinstone", "blah") > Fred

4. We can also give the result of the return an alias, or tag:

#Querying labeled
For Row insession.query (User.name.label (' Name_label ')). All ():
Print (Row.name_label)

The key here is the label method, which means to change the name field of the user to be called Name_label, which is equivalent to executing the following SQL statement:

Selectusers.name Asname_label
From users

6. In addition to alias the Mapping class field, we can also give the mapping class an individual name:

#Querying with aliased
From Sqlalchemy.orm import aliased
user_alias=aliased (user,name= ' User_alias ')
For row insession.query (User_alias,user_alias.name). All ():
Print Row.user_alias

It is important to note that we introduced the aliased function and gave the user mapping class an individual named User_alias. Then we can use this alias in query, which is equivalent to the user object. The above code is equivalent to executing the following SQL statement:

Selectuser_alias.id asuser_alias_id, User_alias.name asuser_alias_name,
User_alias.fullname as User_alias_fullname,user_alias.password as User_alias_password
From Users Asuser_alias

7. Because query queries return a tuple, we can use Python to "Shard" the Array class object to limit the range of returned result sets:

#Querying with limit and offset
For u insession.query (User). order_by (User.ID) [1:3]:
Print U

Here we add [1:3] to the returned results to limit the range of result sets returned. Its execution is equivalent to the following SQL statement:

Selectusers.id asusers_id, Users.name asusers_name,
Users.fullname Asusers_fullname, Users.password Asusers_password
From Users Users.id
LIMIT? OFFSET?

8. The preceding query does not involve subqueries, that is, the WHERE clause of SQL. In the SQLAlchemy framework, query subqueries can be implemented by filter_by:

#Qyering with Filter by
For Name,in Session.query (user.name). filter_by (fullname= ' Ed Jones '):
Print Name

The above query is equivalent to finding the data in the User mapping table FullName to ' Ed Jones ', which is equivalent to executing the following SQL statement:

Selectusers.name Asusers_name
From users
WHERE users.fullname =?

9. In addition to filter_by, we can also use the filter method, which looks more flexible, and we can specify the query criteria according to the properties of the mapped class object:

#Querying with Filter
For Name,in Session.query (User.Name). Filter (user.fullname== ' Ed Jones '):
Print Name

Unlike Filter_by, where the mapping class name plus the attribute field is used to specify the query clause parameter, it is equivalent to executing the following SQL statement:

Selectusers.name Asusers_name
From users
WHERE users.fullname =?

If we want to nest multiple query criteria, we can nest multiple filter:

#Querying with fully generative
For Name,in Session.query (User.Name). Filter (user.fullname== ' Ed Jones '). Filter (user.name== ' Ed '):
Print Name

We can see that we have nested a filter behind the filter method and can theoretically nest countless, which is equivalent to executing the following SQL statement:

Selectusers.name Asusers_name
From users
WHERE users.fullname =? and Users.name =?

The complete sample code is as follows:

Fromsqlalchemy.ext.declarative Importdeclarative_base
From SQLAlchemy import column,integer,string
From SQLAlchemy import Sequence
From Sqlalchemy.orm import Sessionmaker
Base=declarative_base ()
From SQLAlchemy import Create_engine
Engine=create_engine (' sqlite:///:memory: ', echo=true)
Class User (Base):
__tablename__= ' users '
Id=column (integer,sequence (' User_id_seq '), primary_key=true)
Name=column (String (50))
Fullname=column (String (50))
Password=column (String (12))
Def__init__ (Self,name,fullname,password):
Self.name=name
Self.fullname=fullname
Self.password=password

Def__repr__ (self):
Return '% (Self.name,self.fullname,self.password)
Base.metadata.create_all (Engine)
Session=sessionmaker (Bind=engine)
Session=session ()
#Add on user
Ed_user=user (' Ed ', ' Ed Jones ', ' Edpassword ')
Session.add (Ed_user)
#Retrive saved Ed_user
Our_user=session.query (user). Filter_by (name= ' Ed '). First ()
print ' Our_user is: ', Our_user
print ' Our_user ID is: ', our_user.id
print ' Our_user is Ed_user ', Our_user==ed_user

#Add Multiple Object
Session.add_all (
[
User (' Wendy ', ' Wendy Williams ', ' foobar '),
User (' Marry ', ' Marry contrary ', ' xxg527 '),
User (' Fred ', ' Fred Flinstone ', ' blah ')
]
)
#Detective The Dirty Data
Ed_user.password= ' f8x902 '
print ' Dirty data ', Session.dirty
#Detective the new data
print ' New data ', session.new
#Commit data
Session.commit ()
#========querying===============
#Querying User Instance
For instance Insession.query (User). Order_by (user.id):
Print Instance.name,instance.fullname
#Querying by Orm-instrument
For Name,fullnamein Session.query (user.name,user.fullname):
Print Name,fullname
#Querying as a Python object
For row insession.query (User,user.name). All ():
Print row. User,row.name

#Querying labeled
For Row insession.query (User.name.label (' Name_label ')). All ():
Print (Row.name_label)
#Querying with aliased
From Sqlalchemy.orm import aliased
user_alias=aliased (user,name= ' User_alias ')
For row insession.query (User_alias,user_alias.name). All ():
Print Row.user_alias
#Querying with limit and offset
For u insession.query (User). order_by (User.ID) [1:3]:
Print U
#Qyering with Filter by
For Name,in Session.query (user.name). filter_by (fullname= ' Ed Jones '):
Print Name
#Querying with Filter
For Name,in Session.query (User.Name). Filter (user.fullname== ' Ed Jones '):
Print Name
#Querying with fully generative
For Name,in Session.query (User.Name). Filter (user.fullname== ' Ed Jones '). Filter (user.name== ' Ed '):
Print Name

Python standard function library, Sqlalcehemy

The Query method in SQLAlchemy