CData Python Connector for Sage 50 UK

Build 24.0.9060

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 following example sorts by a specified column using the session object's query() method:
rs = session.query(TradingAccounts).order_by(TradingAccounts.AnnualRevenue)
for instance in rs:
	print("TradingAccountUUID: ", instance.TradingAccountUUID)
	print("TradingAccountUUID: ", instance.TradingAccountUUID)
	print("Name: ", instance.Name)
	print("---------")

You can also use the session object's execute() method perform an ORDER BY. For example:

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

GROUP BY

The following example uses the session object's query() method to group records with a specified column:
rs = session.query(func.count(TradingAccounts.TradingAccountUUID).label("CustomCount"), TradingAccounts.TradingAccountUUID).group_by(TradingAccounts.TradingAccountUUID)
for instance in rs:
	print("Count: ", instance.CustomCount)
	print("TradingAccountUUID: ", instance.TradingAccountUUID)
	print("---------")

You can also use the session object's execute() method to perform a GROUP BY:

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

LIMIT and OFFSET

The following example uses the session object's query() method to skip the first 100 records and fetch the following 25:
rs = session.query(TradingAccounts).limit(25).offset(100)
for instance in rs:
	print("TradingAccountUUID: ", instance.TradingAccountUUID)
	print("TradingAccountUUID: ", instance.TradingAccountUUID)
	print("Name: ", instance.Name)
	print("---------")

You can also use the session object's execute() method to set a LIMIT or OFFSET:

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

Copyright (c) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060