from sqlalchemy import func

        #rows = session.query(Person).count()

        c.count = Session.query(func.count(Person.id)).scalar()

        c.avg = Session.query(func.avg(Person.id).label('average')).scalar()
       
        c.sum = Session.query(func.sum(Person.id).label('average')).scalar()
        
        c.max = Session.query(func.max(Person.id).label('average')).scalar() 

Group by

c.coutg = Session.query(func.count(Person.id).label('count'), Person.name ).group_by(Person.name).all()

templet:

Group count
 <br>
count g
<table  border="1">
% for a in c.coutg:
    <tr>
        
        <td>${ a.name } </td> <td> ${ a.count }</td>
    </tr>
% endfor

Distinct

from sqlalchemy import distinct

# count distinct "name" values
session.query(func.count(distinct(User.name)))

Eg : 1

        query = Session.query(Person)
        
        query = query.join(Address, Address.person_id == Person.id)
        
        query = query.filter(Address.city == "abccity")
        
        c.j2 = query.all()

Eg : 2

c.jo = Session.query(Address.city, Person.name).filter(Address.person_id==Person.id).all()

"""Person model"""
from sqlalchemy import Column
from sqlalchemy.types import Integer, String

from pylontutor.model.meta import Base

class Person(Base):
    __tablename__ = "person"

    id = Column(Integer, primary_key=True)
    name = Column(String(100))
    email = Column(String(100))

    def __init__(self, name='', email=''):
        self.name = name
        self.email = email

    def __repr__(self):
        return "<Person('%s')" % self.name

Eg : 1

def delete(self):
titles = request.POST.getall('title')
pages = self.page_q.filter(Page.title.in_(titles))
for page in pages:
Session.delete(page)
Session.commit()

redirect_to('pages')

Eg : 2

from sqlalchemy import delete

d = delete(page_table, page_table.c.id==1)
connection.execute(d)

Eg : 1

page = self.page_q.filter_by(title=title).first()
page.content=escape(request.POST.getone('content'))
Session.commit()

Eg: 2

from sqlalchemy import update

u = update(page_table, page_table.c.title==u'New Title')
connection.execute(u, title=u"Updated Title")

equal to =>
UPDATE page SET title=? WHERE page.title = ?
[u’Updated Title’, u’New Title’]

Eg : 1

page = Page(title=title)
Session.add(page)
Session.commit()

p = Person( name='acb', email='abc@gmail.com')
Session.add(p)
Session.commit()

Eg : 2

from object_test import session
import model

test_page = model.Page()
test_page.title = u'Test Page'
test_page.content = u'Test content'
session.add(test_page)
session.commit()

http://pylonsbook.com/en/1.1/introducing-the-model-and-sqlalchemy.html#queries