Selectables, Tables, FROM objects

The term “selectable” refers to any object that rows can be selected from;in SQLAlchemy, these objects descend from FromClause and theirdistinguishing feature is their FromClause.c attribute, which isa namespace of all the columns contained within the FROM clause (theseelements are themselves ColumnElement subclasses).

  • sqlalchemy.sql.expression.alias(selectable, name=None, flat=False)
  • Return an Alias object.

An Alias represents any FromClausewith an alternate name assigned within SQL, typically using the ASclause when generated, e.g. SELECT * FROM table AS aliasname.

Similar functionality is available via thealias() methodavailable on all FromClause subclasses. In terms of aSELECT object as generated from the select() function, theSelectBase.alias() method returns an Alias orsimilar object which represents a named, parenthesized subquery.

When an Alias is created from a Table object,this has the effect of the table being renderedas tablename AS aliasname in a SELECT statement.

For select() objects, the effect is that of creating a namedsubquery, i.e. (select …) AS aliasname.

The name parameter is optional, and provides the nameto use in the rendered SQL. If blank, an “anonymous” namewill be deterministically generated at compile time.Deterministic means the name is guaranteed to be unique againstother constructs used in the same statement, and will also be thesame name for each successive compilation of the same statementobject.

  • Parameters

    • selectable – any FromClause subclass,such as a table, select statement, etc.

    • name – string name to be assigned as the alias.If None, a name will be deterministically generatedat compile time.

    • flat –

Will be passed through to if the given selectableis an instance of Join - see Join.alias()for details.

New in version 0.9.0.

The returned object is an instance ofCompoundSelect.

  • *selects

  • a list of Select instances.

  • **kwargs

  • available keyword arguments are the same as those ofselect().
  • sqlalchemy.sql.expression.exceptall(selects, *kwargs_)
  • Return an EXCEPT ALL of multiple selectables.

The returned object is an instance ofCompoundSelect.

  • *selects

  • a list of Select instances.

  • **kwargs

  • available keyword arguments are the same as those ofselect().
  • sqlalchemy.sql.expression.exists(*args, **kwargs)
  • Construct a new Exists against an existingSelect object.

Calling styles are of the following forms:

  1. # use on an existing select()
  2. s = select([table.c.col1]).where(table.c.col2==5)
  3. s = exists(s)
  4.  
  5. # construct a select() at once
  6. exists(['*'], **select_arguments).where(criterion)
  7.  
  8. # columns argument is optional, generates "EXISTS (SELECT *)"
  9. # by default.
  10. exists().where(table.c.col2==5)
  • sqlalchemy.sql.expression.intersect(*selects, **kwargs)
  • Return an INTERSECT of multiple selectables.

The returned object is an instance ofCompoundSelect.

  • *selects

  • a list of Select instances.

  • **kwargs

  • available keyword arguments are the same as those ofselect().
  • sqlalchemy.sql.expression.intersectall(selects, *kwargs_)
  • Return an INTERSECT ALL of multiple selectables.

The returned object is an instance ofCompoundSelect.

  • *selects

  • a list of Select instances.

  • **kwargs

  • available keyword arguments are the same as those ofselect().
  • sqlalchemy.sql.expression.join(left, right, onclause=None, isouter=False, full=False)
  • Produce a Join object, given two FromClauseexpressions.

E.g.:

  1. j = join(user_table, address_table,
  2.          user_table.c.id == address_table.c.user_id)
  3. stmt = select([user_table]).select_from(j)

would emit SQL along the lines of:

  1. SELECT user.id, user.name FROM user
  2. JOIN address ON user.id = address.user_id

Similar functionality is available given anyFromClause object (e.g. such as a Table) usingthe FromClause.join() method.

  • Parameters

    • left – The left side of the join.

    • right – the right side of the join; this is anyFromClause object such as a Table object, andmay also be a selectable-compatible object such as an ORM-mappedclass.

    • onclause – a SQL expression representing the ON clause of thejoin. If left at None, FromClause.join() will attempt tojoin the two tables based on a foreign key relationship.

    • isouter – if True, render a LEFT OUTER JOIN, instead of JOIN.

    • full –

if True, render a FULL OUTER JOIN, instead of JOIN.

New in version 1.1.

See also

FromClause.join() - method form, based on a given left side

Join - the type of object produced

  • sqlalchemy.sql.expression.lateral(selectable, name=None)
  • Return a Lateral object.

Lateral is an Alias subclass that representsa subquery with the LATERAL keyword applied to it.

The special behavior of a LATERAL subquery is that it appears in theFROM clause of an enclosing SELECT, but may correlate to otherFROM clauses of that SELECT. It is a special case of subqueryonly supported by a small number of backends, currently more recentPostgreSQL versions.

New in version 1.1.

See also

LATERAL correlation - overview of usage.

  • sqlalchemy.sql.expression.outerjoin(left, right, onclause=None, full=False)
  • Return an OUTER JOIN clause element.

The returned object is an instance of Join.

Similar functionality is also available via theouterjoin() method on anyFromClause.

  • Parameters

    • left – The left side of the join.

    • right – The right side of the join.

    • onclause – Optional criterion for the ON clause, isderived from foreign key relationships established betweenleft and right otherwise.

To chain joins together, use the FromClause.join() orFromClause.outerjoin() methods on the resultingJoin object.

  • sqlalchemy.sql.expression.select(columns=None, whereclause=None, from_obj=None, distinct=False, having=None, correlate=True, prefixes=None, suffixes=None, **kwargs)
  • Construct a new Select.

Similar functionality is also available via theFromClause.select() method on any FromClause.

All arguments which accept ClauseElement arguments alsoaccept string arguments, which will be converted as appropriate intoeither text() or literal_column() constructs.

See also

Selecting - Core Tutorial description ofselect().

  • Parameters
    • columns –

A list of ColumnElement or FromClauseobjects which will form the columns clause of the resultingstatement. For those objects that are instances ofFromClause (typically Table or Aliasobjects), the FromClause.c collection is extractedto form a collection of ColumnElement objects.

This parameter will also accept Text constructs asgiven, as well as ORM-mapped classes.

Note

The select.columns parameter is not availablein the method form of select(), e.g.FromClause.select().

See also

Select.column()

Select.with_only_columns()

  1. -

whereclause –

A ClauseElement expression which will be used to form theWHERE clause. It is typically preferable to add WHEREcriterion to an existing Select using method chainingwith Select.where().

See also

Select.where()

  1. -

from_obj –

A list of ClauseElement objects which will be added to theFROM clause of the resulting statement. This is equivalentto calling Select.select_from() using method chaining onan existing Select object.

See also

Select.select_from() - full description of explicitFROM clause specification.

  1. -

autocommit –

legacy autocommit parameter.

Deprecated since version 0.6: The select.autocommit parameter is deprecated and will be removed in a future release. Please refer to the Connection.execution_options.autocommit parameter in conjunction with the the Executable.execution_options() method in order to affect the autocommit behavior for a statement.

  1. -

bind=None – an Engine or Connection instanceto which theresulting Select object will be bound. TheSelect object will otherwise automatically bind towhatever Connectable instances can be located withinits contained ClauseElement members.

  1. -

correlate=True –

indicates that this Select object should have itscontained FromClause elements “correlated” to an enclosingSelect object. It is typically preferable to specifycorrelations on an existing Select construct usingSelect.correlate().

See also

Select.correlate() - full description of correlation.

  1. -

distinct=False –

when True, applies a DISTINCT qualifier to the columnsclause of the resulting statement.

The boolean argument may also be a column expression or listof column expressions - this is a special calling form whichis understood by the PostgreSQL dialect to render theDISTINCT ON (<columns>) syntax.

distinct is also available on an existing Selectobject via the distinct() method.

See also

Select.distinct()

  1. -

for_update=False –

when True, applies FOR UPDATE to the end of theresulting statement.

for_update accepts various string values interpreted byspecific backends, including:

  • "read" - on MySQL, translates to LOCK IN SHARE MODE;on PostgreSQL, translates to FOR SHARE.

  • "nowait" - on PostgreSQL and Oracle, translates toFOR UPDATE NOWAIT.

  • "read_nowait" - on PostgreSQL, translates toFOR SHARE NOWAIT.

See also

Select.with_for_update() - improved API forspecifying the FOR UPDATE clause.

  1. -

group_by –

a list of ClauseElement objects which will comprise theGROUP BY clause of the resulting select. This parameteris typically specified more naturally using theSelect.group_by() method on an existing Select.

See also

Select.group_by()

  1. -

having –

a ClauseElement that will comprise the HAVING clauseof the resulting select when GROUP BY is used. This parameteris typically specified more naturally using theSelect.having() method on an existing Select.

See also

Select.having()

  1. -

limit=None –

a numerical value which usually renders as a LIMITexpression in the resulting select. Backends that don’tsupport LIMIT will attempt to provide similarfunctionality. This parameter is typically specified morenaturally using the Select.limit() method on an existingSelect.

See also

Select.limit()

  1. -

offset=None –

a numeric value which usually renders as an OFFSETexpression in the resulting select. Backends that don’tsupport OFFSET will attempt to provide similarfunctionality. This parameter is typically specified more naturallyusing the Select.offset() method on an existingSelect.

See also

Select.offset()

  1. -

order_by –

a scalar or list of ClauseElement objects which willcomprise the ORDER BY clause of the resulting select.This parameter is typically specified more naturally using theSelect.order_by() method on an existing Select.

See also

Select.order_by()

  1. -

use_labels=False –

when True, the statement will be generated using labelsfor each column in the columns clause, which qualify eachcolumn with its parent table’s (or aliases) name so that nameconflicts between columns in different tables don’t occur.The format of the label is _. The “c”collection of the resulting Select object will use thesenames as well for targeting column members.

This parameter can also be specified on an existingSelect object using the Select.apply_labels()method.

See also

Select.apply_labels()

  • sqlalchemy.sql.expression.subquery(alias, *args, **kwargs)

  • Return an Alias object derivedfrom a Select.

    • name
    • alias name

args, *kwargs

all other arguments are delivered to theselect() function.

  • sqlalchemy.sql.expression.table(name, *columns)
  • Produce a new TableClause.

The object returned is an instance of TableClause, whichrepresents the “syntactical” portion of the schema-levelTable object.It may be used to construct lightweight table constructs.

Changed in version 1.0.0: expression.table() can nowbe imported from the plain sqlalchemy namespace like anyother SQL element.

  • Parameters
  • sqlalchemy.sql.expression.tablesample(selectable, sampling, name=None, seed=None)
  • Return a TableSample object.

TableSample is an Alias subclass that representsa table with the TABLESAMPLE clause applied to it.tablesample()is also available from the FromClause class via theFromClause.tablesample() method.

The TABLESAMPLE clause allows selecting a randomly selected approximatepercentage of rows from a table. It supports multiple sampling methods,most commonly BERNOULLI and SYSTEM.

e.g.:

  1. from sqlalchemy import func
  2.  
  3. selectable = people.tablesample(
  4.             func.bernoulli(1),
  5.             name='alias',
  6.             seed=func.random())
  7. stmt = select([selectable.c.people_id])

Assuming people with a column people_id, the abovestatement would render as:

  1. SELECT alias.people_id FROM
  2. people AS alias TABLESAMPLE bernoulli(:bernoulli_1)
  3. REPEATABLE (random())

New in version 1.1.

  • Parameters

    • sampling – a float percentage between 0 and 100 orfunctions.Function.

    • name – optional alias name

    • seed – any real-valued SQL expression. When specified, theREPEATABLE sub-clause is also rendered.

  • sqlalchemy.sql.expression.union(*selects, **kwargs)
  • Return a UNION of multiple selectables.

The returned object is an instance ofCompoundSelect.

A similar union() method is available on allFromClause subclasses.

  • *selects

  • a list of Select instances.

  • **kwargs

  • available keyword arguments are the same as those ofselect().
  • sqlalchemy.sql.expression.unionall(selects, *kwargs_)
  • Return a UNION ALL of multiple selectables.

The returned object is an instance ofCompoundSelect.

A similar union_all() method is available on allFromClause subclasses.

  • *selects

  • a list of Select instances.

  • **kwargs

  • available keyword arguments are the same as those ofselect().

Represents an table or selectable alias (AS).

Represents an alias, as typically applied to any table orsub-select within a SQL statement using the AS keyword (orwithout the keyword on certain databases such as Oracle).

This object is constructed from the alias() modulelevel function as well as the FromClause.alias() method availableon all FromClause subclasses.

  • eq()

inherited from the eq() method of object

Return self==value.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

See also

union()

union_all()

intersect()

intersect_all()

except()

except_all()

  • eq()

inherited from the eq() method of object

Return self==value.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

Represent a Common Table Expression.

The CTE object is obtained using theSelectBase.cte() method from any selectable.See that method for complete examples.

  • eq()

inherited from the eq() method of object

Return self==value.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

  • class sqlalchemy.sql.expression.Executable
  • Bases: sqlalchemy.sql.expression.Generative

Mark a ClauseElement as supporting execution.

Executable is a superclass for all “statement” typesof objects, including select(), delete(), update(),insert(), text().

This is a traversal which checks locally, thenchecks among the “from” clauses of associated objectsuntil a bound engine or connection is found.

  • execute(*multiparams, **params)

  • Compile and execute this Executable.

  • executionoptions(**kw_)

  • Set non-SQL options for the statement which take effect duringexecution.

Execution options can be set on a per-statement orper Connection basis. Additionally, theEngine and ORM Query objects provideaccess to execution options which they in turn configure uponconnections.

The execution_options() method is generative. A newinstance of this statement is returned that contains the options:

  1. statement = select([table.c.x, table.c.y])
  2. statement = statement.execution_options(autocommit=True)

Note that only a subset of possible execution options can be appliedto a statement - these include “autocommit” and “stream_results”,but not “isolation_level” or “compiled_cache”.See Connection.execution_options() for a full list ofpossible options.

See also

Connection.execution_options()

Query.execution_options()

Executable.get_execution_options()

  • get_execution_options()
  • Get the non-SQL options which will take effect during execution.

New in version 1.3.

See also

Executable.execution_options()

  • scalar(*multiparams, **params)
  • Compile and execute this Executable, returning theresult’s scalar representation.

Represent an element that can be used within the FROMclause of a SELECT statement.

The most common forms of FromClause are theTable and the select() constructs. Keyfeatures common to all FromClause objects include:

E.g.:

  1. a2 = some_table.alias('a2')

The above code creates an Alias object which can be usedas a FROM clause in any SELECT statement.

See also

Using Aliases and Subqueries

alias()

The columns, or c collection, is the gatewayto the construction of SQL expressions using table-bound orother selectable-bound columns:

  1. select([mytable]).where(mytable.c.somecolumn == 5)
  • correspondon_equivalents(_column, equivalents)

  • Return corresponding_column for the given column, or if Nonesearch for a match in the given dictionary.

  • correspondingcolumn(_column, require_embedded=False)

  • Given a ColumnElement, return the exportedColumnElement object from this Selectablewhich corresponds to that originalColumn via a common ancestorcolumn.

    • Parameters

      • column – the target ColumnElement to be matched

      • require_embedded – only return corresponding columns forthe given ColumnElement, if the givenColumnElement is actually present within a sub-elementof this FromClause. Normally the column will match ifit merely shares a common ancestor with one of the exportedcolumns of this FromClause.

  • count(functions, whereclause=None, **params)

  • return a SELECT COUNT generated against thisFromClause.

Deprecated since version 1.1: The FromClause.count() method is deprecated, and will be removed in a future release. Please use the functions.count function available from the func namespace.

See also

functions.count

  • property description
  • a brief description of this FromClause.

Used primarily for error message formatting.

  • foreign_keys

  • Return the collection of ForeignKey objects which thisFromClause references.

  • isderived_from(_fromclause)

  • Return True if this FromClause is ‘derived’ from the givenFromClause.

An example would be an Alias of a Table is derived from that Table.

E.g.:

  1. from sqlalchemy import join
  2.  
  3. j = user_table.join(address_table,
  4.                 user_table.c.id == address_table.c.user_id)
  5. stmt = select([user_table]).select_from(j)

would emit SQL along the lines of:

  1. SELECT user.id, user.name FROM user
  2. JOIN address ON user.id = address.user_id
  1. - Parameters
  2. - 
  3.   -

right – the right side of the join; this is anyFromClause object such as a Table object, andmay also be a selectable-compatible object such as an ORM-mappedclass.

  1.   -

onclause – a SQL expression representing the ON clause of thejoin. If left at None, FromClause.join() will attempt tojoin the two tables based on a foreign key relationship.

  1.   -

isouter – if True, render a LEFT OUTER JOIN, instead of JOIN.

  1.   -

full –

if True, render a FULL OUTER JOIN, instead of LEFT OUTERJOIN. Implies FromClause.join.isouter.

New in version 1.1.

See also

join() - standalone function

Join - the type of object produced

  • lateral(name=None)
  • Return a LATERAL alias of this FromClause.

The return value is the Lateral construct alsoprovided by the top-level lateral() function.

New in version 1.1.

See also

LATERAL correlation - overview of usage.

  • outerjoin(right, onclause=None, full=False)
  • Return a Join from this FromClauseto another FromClause, with the “isouter” flag set toTrue.

E.g.:

  1. from sqlalchemy import outerjoin
  2.  
  3. j = user_table.outerjoin(address_table,
  4.                 user_table.c.id == address_table.c.user_id)

The above is equivalent to:

  1. j = user_table.join(
  2.     address_table,
  3.     user_table.c.id == address_table.c.user_id,
  4.     isouter=True)
  1. - Parameters
  2. - 
  3.   -

right – the right side of the join; this is anyFromClause object such as a Table object, andmay also be a selectable-compatible object such as an ORM-mappedclass.

  1.   -

onclause – a SQL expression representing the ON clause of thejoin. If left at None, FromClause.join() will attempt tojoin the two tables based on a foreign key relationship.

  1.   -

full –

if True, render a FULL OUTER JOIN, instead ofLEFT OUTER JOIN.

New in version 1.1.

See also

FromClause.join()

Join

  • primary_key

  • Return the collection of Column objects which comprise theprimary key of this FromClause.

  • replaceselectable(_sqlutil, old, alias)

  • replace all occurrences of FromClause ‘old’ with the given Aliasobject, returning a copy of this FromClause.

  • schema = None

  • Define the ‘schema’ attribute for this FromClause.

This is typically None for most objects except that ofTable, where it is taken as the value of theTable.schema argument.

  • select(whereclause=None, **params)
  • return a SELECT of this FromClause.

See also

select() - general purposemethod which allows for arbitrary column lists.

  • tablesample(sampling, name=None, seed=None)
  • Return a TABLESAMPLE alias of this FromClause.

The return value is the TableSample construct alsoprovided by the top-level tablesample() function.

New in version 1.1.

See also

tablesample() - usage guidelines and parameters

  • class sqlalchemy.sql.expression.GenerativeSelect(use_labels=False, for_update=False, limit=None, offset=None, order_by=None, group_by=None, bind=None, autocommit=None)
  • Bases: sqlalchemy.sql.expression.SelectBase

Base class for SELECT statements where additional elements can beadded.

This serves as the base for Select and CompoundSelectwhere elements such as ORDER BY, GROUP BY can be added and columnrendering can be controlled. Compare to TextAsFrom, which,while it subclasses SelectBase and is also a SELECT construct,represents a fixed textual string which cannot be altered at this level,only wrapped as a subquery.

New in version 0.9.0: GenerativeSelect was added toprovide functionality specific to Select andCompoundSelect while allowing SelectBase to beused for other SELECT-like objects, e.g. TextAsFrom.

  • eq()

inherited from the eq() method of object

Return self==value.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

  • class sqlalchemy.sql.expression.HasCTE
  • Mixin that declares a class to include CTE support.

New in version 1.1.

  • cte(name=None, recursive=False)
  • Return a new CTE, or Common Table Expression instance.

Common table expressions are a SQL standard whereby SELECTstatements can draw upon secondary statements specified alongwith the primary statement, using a clause called “WITH”.Special semantics regarding UNION can also be employed toallow “recursive” queries, where a SELECT statement can drawupon the set of rows that have previously been selected.

CTEs can also be applied to DML constructs UPDATE, INSERTand DELETE on some databases, both as a source of CTE rowswhen combined with RETURNING, as well as a consumer ofCTE rows.

SQLAlchemy detects CTE objects, which are treatedsimilarly to Alias objects, as special elementsto be delivered to the FROM clause of the statement as wellas to a WITH clause at the top of the statement.

Changed in version 1.1: Added support for UPDATE/INSERT/DELETE asCTE, CTEs added to UPDATE/INSERT/DELETE.

  1. - Parameters
  2. - 
  3.   -

name – name given to the common table expression. Like_FromClause.alias(), the name can be left as Nonein which case an anonymous symbol will be used at querycompile time.

  1.   -

recursive – if True, will render WITH RECURSIVE.A recursive common table expression is intended to be used inconjunction with UNION ALL in order to derive rowsfrom those already selected.

The following examples include two from PostgreSQL’s documentation athttp://www.postgresql.org/docs/current/static/queries-with.html,as well as additional examples.

Example 1, non recursive:

  1. from sqlalchemy import (Table, Column, String, Integer,
  2.                         MetaData, select, func)
  3.  
  4. metadata = MetaData()
  5.  
  6. orders = Table('orders', metadata,
  7.     Column('region', String),
  8.     Column('amount', Integer),
  9.     Column('product', String),
  10.     Column('quantity', Integer)
  11. )
  12.  
  13. regional_sales = select([
  14.                     orders.c.region,
  15.                     func.sum(orders.c.amount).label('total_sales')
  16.                 ]).group_by(orders.c.region).cte("regional_sales")
  17.  
  18.  
  19. top_regions = select([regional_sales.c.region]).\
  20.         where(
  21.             regional_sales.c.total_sales >
  22.             select([
  23.                 func.sum(regional_sales.c.total_sales)/10
  24.             ])
  25.         ).cte("top_regions")
  26.  
  27. statement = select([
  28.             orders.c.region,
  29.             orders.c.product,
  30.             func.sum(orders.c.quantity).label("product_units"),
  31.             func.sum(orders.c.amount).label("product_sales")
  32.     ]).where(orders.c.region.in_(
  33.         select([top_regions.c.region])
  34.     )).group_by(orders.c.region, orders.c.product)
  35.  
  36. result = conn.execute(statement).fetchall()

Example 2, WITH RECURSIVE:

  1. from sqlalchemy import (Table, Column, String, Integer,
  2.                         MetaData, select, func)
  3.  
  4. metadata = MetaData()
  5.  
  6. parts = Table('parts', metadata,
  7.     Column('part', String),
  8.     Column('sub_part', String),
  9.     Column('quantity', Integer),
  10. )
  11.  
  12. included_parts = select([
  13.                     parts.c.sub_part,
  14.                     parts.c.part,
  15.                     parts.c.quantity]).\
  16.                     where(parts.c.part=='our part').\
  17.                     cte(recursive=True)
  18.  
  19.  
  20. incl_alias = included_parts.alias()
  21. parts_alias = parts.alias()
  22. included_parts = included_parts.union_all(
  23.     select([
  24.         parts_alias.c.sub_part,
  25.         parts_alias.c.part,
  26.         parts_alias.c.quantity
  27.     ]).
  28.         where(parts_alias.c.part==incl_alias.c.sub_part)
  29. )
  30.  
  31. statement = select([
  32.             included_parts.c.sub_part,
  33.             func.sum(included_parts.c.quantity).
  34.               label('total_quantity')
  35.         ]).\
  36.         group_by(included_parts.c.sub_part)
  37.  
  38. result = conn.execute(statement).fetchall()

Example 3, an upsert using UPDATE and INSERT with CTEs:

  1. from datetime import date
  2. from sqlalchemy import (MetaData, Table, Column, Integer,
  3.                         Date, select, literal, and_, exists)
  4.  
  5. metadata = MetaData()
  6.  
  7. visitors = Table('visitors', metadata,
  8.     Column('product_id', Integer, primary_key=True),
  9.     Column('date', Date, primary_key=True),
  10.     Column('count', Integer),
  11. )
  12.  
  13. # add 5 visitors for the product_id == 1
  14. product_id = 1
  15. day = date.today()
  16. count = 5
  17.  
  18. update_cte = (
  19.     visitors.update()
  20.     .where(and_(visitors.c.product_id == product_id,
  21.                 visitors.c.date == day))
  22.     .values(count=visitors.c.count + count)
  23.     .returning(literal(1))
  24.     .cte('update_cte')
  25. )
  26.  
  27. upsert = visitors.insert().from_select(
  28.     [visitors.c.product_id, visitors.c.date, visitors.c.count],
  29.     select([literal(product_id), literal(day), literal(count)])
  30.         .where(~exists(update_cte.select()))
  31. )
  32.  
  33. connection.execute(upsert)

See also

orm.query.Query.cte() - ORM version ofHasCTE.cte().

  • class sqlalchemy.sql.expression.HasPrefixes
    • prefixwith(expr, *kw_)
    • Add one or more expressions following the statement keyword, i.e.SELECT, INSERT, UPDATE, or DELETE. Generative.

This is used to support backend-specific prefix keywords such as thoseprovided by MySQL.

E.g.:

  1. stmt = table.insert().prefix_with("LOW_PRIORITY", dialect="mysql")
  2.  
  3. # MySQL 5.7 optimizer hints
  4. stmt = select([table]).prefix_with(
  5.     "/*+ BKA(t1) */", dialect="mysql")

Multiple prefixes can be specified by multiple callsto prefix_with().

  1. - Parameters
  2. - 
  3.   -

*expr –

textual or ClauseElement construct whichwill be rendered following the INSERT, UPDATE, or DELETEkeyword.

Warning

The HasPrefixes.prefix_with.*expr argument to HasPrefixes.prefix_with() can be passed as a Python string argument, which will be treated as trusted SQL text and rendered as given. DO NOT PASS UNTRUSTED INPUT TO THIS PARAMETER.

  1.   -

**kw – A single keyword ‘dialect’ is accepted. This is anoptional string dialect name which willlimit rendering of this prefix to only that dialect.

  • class sqlalchemy.sql.expression.HasSuffixes
    • suffixwith(expr, *kw_)
    • Add one or more expressions following the statement as a whole.

This is used to support backend-specific suffix keywords oncertain constructs.

E.g.:

  1. stmt = select([col1, col2]).cte().suffix_with(
  2.     "cycle empno set y_cycle to 1 default 0", dialect="oracle")

Multiple suffixes can be specified by multiple callsto suffix_with().

  1. - Parameters
  2. - 
  3.   -

*expr –

textual or ClauseElement construct whichwill be rendered following the target clause.

Warning

The HasSuffixes.suffix_with.*expr argument to HasSuffixes.suffix_with() can be passed as a Python string argument, which will be treated as trusted SQL text and rendered as given. DO NOT PASS UNTRUSTED INPUT TO THIS PARAMETER.

  1.   -

**kw – A single keyword ‘dialect’ is accepted. This is anoptional string dialect name which willlimit rendering of this suffix to only that dialect.

represent a JOIN construct between two FromClauseelements.

The public constructor function for Join is the module-leveljoin() function, as well as the FromClause.join() methodof any FromClause (e.g. such as Table).

See also

join()

FromClause.join()

  • eq()

inherited from the eq() method of object

Return self==value.

  • init(left, right, onclause=None, isouter=False, full=False)
  • Construct a new Join.

The usual entrypoint here is the join()function or the FromClause.join() method of anyFromClause object.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

Represent a LATERAL subquery.

This object is constructed from the lateral() modulelevel function as well as the FromClause.lateral() method availableon all FromClause subclasses.

While LATERAL is part of the SQL standard, currently only more recentPostgreSQL versions provide support for this keyword.

New in version 1.1.

See also

LATERAL correlation - overview of usage.

  • eq()

inherited from the eq() method of object

Return self==value.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

  • class sqlalchemy.sql.expression.ScalarSelect(element)

  • Bases: sqlalchemy.sql.expression.Generative, sqlalchemy.sql.expression.Grouping

    • selfgroup(**kwargs_)
    • Apply a ‘grouping’ to this ClauseElement.

This method is overridden by subclasses to return a“grouping” construct, i.e. parenthesis. In particularit’s used by “binary” expressions to provide a groupingaround themselves when placed into a larger expression,as well as by select() constructs when placed intothe FROM clause of another select(). (Note thatsubqueries should be normally created using theSelect.alias() method, as many platforms requirenested SELECT statements to be named).

As expressions are composed together, the application ofself_group() is automatic - end-user code should neverneed to use this method directly. Note that SQLAlchemy’sclause constructs take operator precedence into account -so parenthesis might not be needed, for example, inan expression like x OR (y AND z) - AND takes precedenceover OR.

The base self_group() method of ClauseElementjust returns self.

  • where(crit)
  • Apply a WHERE clause to the SELECT statement referred toby this ScalarSelect.

Represents a SELECT statement.

  • eq()

inherited from the eq() method of object

Return self==value.

  • init(columns=None, whereclause=None, from_obj=None, distinct=False, having=None, correlate=True, prefixes=None, suffixes=None, **kwargs)
  • Construct a new Select object.

This constructor is mirrored as a public API function; see select() for a full usage and argument description.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

mark a class as being selectable

Base class for SELECT statements.

This includes Select, CompoundSelect andTextAsFrom.

  • eq()

inherited from the eq() method of object

Return self==value.

  • init()

inherited from the init() method of object

Initialize self. See help(type(self)) for accurate signature.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

Represents a minimal “table” construct.

This is a lightweight table object that has only a name and acollection of columns, which are typically producedby the expression.column() function:

  1. from sqlalchemy import table, column
  2.  
  3. user = table("user",
  4.         column("id"),
  5.         column("name"),
  6.         column("description"),
  7. )

The TableClause construct serves as the base forthe more commonly used Table object, providingthe usual set of FromClause services includingthe .c. collection and statement generation methods.

It does not provide all the additional schema-level servicesof Table, including constraints, references to othertables, or support for MetaData-level services. It’s usefulon its own as an ad-hoc construct used to generate quick SQLstatements when a more fully fledged Tableis not on hand.

  • eq()

inherited from the eq() method of object

Return self==value.

  • init(name, *columns)
  • Construct a new TableClause object.

This constructor is mirrored as a public API function; see table() for a full usage and argument description.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

Represent a TABLESAMPLE clause.

This object is constructed from the tablesample() modulelevel function as well as the FromClause.tablesample() methodavailable on all FromClause subclasses.

New in version 1.1.

See also

tablesample()

  • eq()

inherited from the eq() method of object

Return self==value.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

Wrap a TextClause construct within a SelectBaseinterface.

This allows the TextClause object to gain a .c collectionand other FROM-like capabilities such as FromClause.alias(),SelectBase.cte(), etc.

The TextAsFrom construct is produced via theTextClause.columns() method - see that method for details.

New in version 0.9.0.

See also

text()

TextClause.columns()

  • eq()

inherited from the eq() method of object

Return self==value.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self