CData Python Connector for Wasabi

Build 21.0.7930

Other SQL Clauses

SQLAlchemy ORM also exposes support for other clauses in SQL, such as ORDER BY, GROUP BY, LIMIT, and OFFSET. All of these are supported by this connector:

ORDER BY

The below example sorts by a specified column using the session object's query() method.

rs = session.query(Buckets).order_by(Buckets.AnnualRevenue)
for instance in rs:
	print("Id: ", instance.Id)
	print("Name: ", instance.Name)
	print("OwnerId: ", instance.OwnerId)
	print("---------")

Alternatively, you can perform an ORDER BY using the session object's execute() method.

rs = session.execute(Buckets_table.select().order_by(Buckets_table.c.AnnualRevenue))
for instance in rs:

GROUP BY

The below example groups records with a specified column using the session object's query() method.

rs = session.query(func.count(Buckets.Id).label("CustomCount"), Buckets.Name).group_by(Buckets.Name)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("Name: ", instance.Name)
	print("---------")

Alternatively, you can perform a GROUP BY using the session object's execute() method.

rs = session.execute(Buckets_table.select().with_only_columns([func.count(Buckets_table.c.Id).label("CustomCount"), Buckets_table.c.Name]).group_by(Buckets_table.c.Name))
for instance in rs:

LIMIT and OFFSET

The below example skips the first 100 records and fetches the following 25 using the session object's query() method.

rs = session.query(Buckets).limit(25).offset(100)
for instance in rs:
	print("Id: ", instance.Id)
	print("Name: ", instance.Name)
	print("OwnerId: ", instance.OwnerId)
	print("---------")

Alternatively, you can set a LIMIT or OFFSET using the session object's execute() method.

rs = session.execute(Buckets_table.select().limit(25).offset(100))
for instance in rs:

Copyright (c) 2021 CData Software, Inc. - All rights reserved.
Build 21.0.7930