Sqlalchemy 简明教程

SQLAlchemy ORM - Using Query

SQLAlchemy ORM 生成的所有 SELECT 语句都是由 Query 对象构建的。它提供了一个生成接口，因此连续调用会返回一个新的 Query 对象，该对象是前一个对象的副本，并带有与其关联的其他 criteria 和 options。

All SELECT statements generated by SQLAlchemy ORM are constructed by Query object. It provides a generative interface, hence successive calls return a new Query object, a copy of the former with additional criteria and options associated with it.

Query 对象最初是使用 Session 的 query() 方法生成的，如下所示 -

Query objects are initially generated using the query() method of the Session as follows −

q = session.query(mapped class)

以下语句也相当于上述给出的语句 -

Following statement is also equivalent to the above given statement −

q = Query(mappedClass, session)

此查询对象具有 all() 方法，该方法以对象列表的形式返回结果集。如果我们对 customers 表执行它 -

The query object has all() method which returns a resultset in the form of list of objects. If we execute it on our customers table −

result = session.query(Customers).all()

此语句实际上等效于以下 SQL 表达式 -

This statement is effectively equivalent to following SQL expression −

SELECT customers.id
AS customers_id, customers.name
AS customers_name, customers.address
AS customers_address, customers.email
AS customers_email
FROM customers

结果对象可以使用 For 循环遍历，如下所示，以获取底层 customers 表中的所有记录。以下是显示 Customers 表中所有记录的完整代码 −

The result object can be traversed using For loop as below to obtain all records in underlying customers table. Here is the complete code to display all records in Customers table −

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   id = Column(Integer, primary_key =  True)
   name = Column(String)

   address = Column(String)
   email = Column(String)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind = engine)
session = Session()
result = session.query(Customers).all()

for row in result:
   print ("Name: ",row.name, "Address:",row.address, "Email:",row.email)

Python 控制台显示了如下所示的记录列表 −

Python console shows list of records as below −

Name: Ravi Kumar Address: Station Road Nanded Email: ravi@gmail.com
Name: Komal Pande Address: Koti, Hyderabad Email: komal@gmail.com
Name: Rajender Nath Address: Sector 40, Gurgaon Email: nath@gmail.com
Name: S.M.Krishna Address: Budhwar Peth, Pune Email: smk@gmail.com

Query 对象还具有以下有用的方法 −

The Query object also has following useful methods −

Sr.No.	Method & Description
1	add_columns() It adds one or more column expressions to the list of result columns to be returned.
2	add_entity() It adds a mapped entity to the list of result columns to be returned.
3	count() It returns a count of rows this Query would return.
4	delete() It performs a bulk delete query. Deletes rows matched by this query from the database.
5	distinct() It applies a DISTINCT clause to the query and return the newly resulting Query.
6	filter() It applies the given filtering criterion to a copy of this Query, using SQL expressions.
7	first() It returns the first result of this Query or None if the result doesn’t contain any row.
8	get() It returns an instance based on the given primary key identifier providing direct access to the identity map of the owning Session.
9	group_by() It applies one or more GROUP BY criterion to the query and return the newly resulting Query
10	join() It creates a SQL JOIN against this Query object’s criterion and apply generatively, returning the newly resulting Query.
11	one() It returns exactly one result or raise an exception.
12	order_by() It applies one or more ORDER BY criterion to the query and returns the newly resulting Query.
13	update() It performs a bulk update query and updates rows matched by this query in the database.