All SELECT statements generated via SQLAlchemy ORM are constructed through Query item. It presents a generative interface, for this reason successive calls go back a new Query object, a copy of the previous with additional standards and alternatives associated with it.
Query gadgets are to begin with generated using the query() method of the Session as follows −
q = session.query(mapped class)
Following declaration is likewise equal to the above given assertion −
q = Query(mappedClass, session)
The query object has all() approach which returns a resultset in the form of list of items. If we execute it on our clients table −
result = session.query(Customers).all()
This assertion 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
The end result item may be traversed the usage of For loop as underneath to attain all statistics in underlying customers table. Here is the complete code to display all information in Customers desk −
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 console shows list of information as under −
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
The Query item also has following beneficial techniques −
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. |