sqlalchemy.sql.selectable.GenerativeSelect

Undocumented

Convert the given value to an "offset or limit" clause.

This handles incoming integers and converts to an expression; if an expression is already given, it is passed through.

Convert the "offset or limit" clause of a select construct to an integer.

This is only possible if the value is stored as a simple bound parameter. Otherwise, a compilation error is raised.

Undocumented

Return a new selectable with the given FETCH FIRST criterion applied.

This is a numeric value which usually renders as FETCH {FIRST | NEXT} [ count ] {ROW | ROWS} {ONLY | WITH TIES} expression in the resulting select. This functionality is is currently implemented for Oracle, PostgreSQL, MSSQL.

Use _sql.GenerativeSelect.offset to specify the offset.

Retrieve the current label style.

Return a new selectable with the given list of GROUP BY criterion applied.

All existing GROUP BY settings can be suppressed by passing None.

e.g.:

stmt = select(table.c.name, func.max(table.c.stat)).\
group_by(table.c.name)

Return a new selectable with the given LIMIT criterion applied.

This is a numerical value which usually renders as a LIMIT expression in the resulting select. Backends that don't support LIMIT will attempt to provide similar functionality.

Return a new selectable with the given OFFSET criterion applied.

This is a numeric value which usually renders as an OFFSET expression in the resulting select. Backends that don't support OFFSET will attempt to provide similar functionality.

Return a new selectable with the given list of ORDER BY criteria applied.

e.g.:

stmt = select(table).order_by(table.c.id, table.c.name)

All existing ORDER BY criteria may be cancelled by passing None by itself. New ORDER BY criteria may then be added by invoking _sql.Select.order_by again, e.g.:

# will erase all ORDER BY and ORDER BY new_col alone
stmt = stmt.order_by(None).order_by(new_col)

Return a new selectable with the specified label style.

There are three "label styles" available, _sql.LABEL_STYLE_DISAMBIGUATE_ONLY, _sql.LABEL_STYLE_TABLENAME_PLUS_COL, and _sql.LABEL_STYLE_NONE. The default style is _sql.LABEL_STYLE_TABLENAME_PLUS_COL.

In modern SQLAlchemy, there is not generally a need to change the labeling style, as per-expression labels are more effectively used by making use of the _sql.ColumnElement.label method. In past versions, _sql.LABEL_STYLE_TABLENAME_PLUS_COL was used to disambiguate same-named columns from different tables, aliases, or subqueries; the newer _sql.LABEL_STYLE_DISAMBIGUATE_ONLY now applies labels only to names that conflict with an existing name so that the impact of this labeling is minimal.

The rationale for disambiguation is mostly so that all column expressions are available from a given _sql.FromClause.c collection when a subquery is created.

Apply LIMIT / OFFSET to this statement based on a slice.

The start and stop indices behave like the argument to Python's built-in range function. This method provides an alternative to using LIMIT/OFFSET to get a slice of the query.

For example,

stmt = select(User).order_by(User).id.slice(1, 3)

renders as

SELECT users.id AS users_id,
       users.name AS users_name
FROM users ORDER BY users.id
LIMIT ? OFFSET ?
(2, 1)

Specify a FOR UPDATE clause for this _expression.GenerativeSelect.

E.g.:

stmt = select(table).with_for_update(nowait=True)

On a database like PostgreSQL or Oracle, the above would render a statement like:

SELECT table.a, table.b FROM table FOR UPDATE NOWAIT

on other backends, the nowait option is ignored and instead would produce:

SELECT table.a, table.b FROM table FOR UPDATE

When called with no arguments, the statement will render with the suffix FOR UPDATE. Additional arguments can then be provided which allow for common database-specific variants.