sqlalchemy_singlestoredb.PersistedColumn¶

class sqlalchemy_singlestoredb.PersistedColumn(*args: Any, persisted_expression: Any = None, **kwargs: Any)¶

SingleStore computed column that is automatically calculated and stored.

PersistedColumn defines computed columns based on an expression. The computed value is persisted to disk and updated when dependent columns change.

Parameters:

name (str) – Column name
type (TypeEngine) – SQLAlchemy type for the computed value
persisted_expression (str or TextClause) – SQL expression to compute the column value
*args – Additional arguments passed to Column
**kwargs – Additional arguments passed to Column

Examples

Basic computed column:

>>> PersistedColumn('total', Integer, persisted_expression='price * quantity')

Using TextClause for complex expressions:

>>> from sqlalchemy import text
>>> PersistedColumn('full_name', String(101),
...                 persisted_expression=text("CONCAT(first_name, ' ', last_name)"))

Table Usage:

from sqlalchemy import Column, Integer, MetaData, String, Table, text
from sqlalchemy_singlestoredb import PersistedColumn
metadata = MetaData()
products = Table(
    'products', metadata,
    Column('id', Integer, primary_key=True),
    Column('price', Integer),
    Column('quantity', Integer),
    PersistedColumn(
        'total',
        Integer,
        persisted_expression='price * quantity',
    ),
)
users = Table(
    'users', metadata,
    Column('id', Integer, primary_key=True),
    Column('first_name', String(50)),
    Column('last_name', String(50)),
    PersistedColumn(
        'full_name',
        String(101),
        persisted_expression=text("CONCAT(first_name, ' ', last_name)"),
    ),
)

ORM Usage:

In ORM models, PersistedColumn is used directly as a class attribute, just like a regular Column:

from sqlalchemy import Column, Integer, String
from sqlalchemy.orm import declarative_base
from sqlalchemy_singlestoredb import PersistedColumn
Base = declarative_base()
class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    price = Column(Integer)
    quantity = Column(Integer)
    total = PersistedColumn(
        'total',
        Integer,
        persisted_expression='price * quantity',
    )
class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    first_name = Column(String(50))
    last_name = Column(String(50))
    full_name = PersistedColumn(
        'full_name',
        String(101),
        persisted_expression="CONCAT(first_name, ' ', last_name)",
    )

__init__(*args: Any, persisted_expression: Any = None, **kwargs: Any) → None¶

Construct a new Column object.

Parameters:

name –
The name of this column as represented in the database. This argument may be the first positional argument, or specified via keyword.

Names which contain no upper case characters will be treated as case insensitive names, and will not be quoted unless they are a reserved word. Names with any number of upper case characters will be quoted and sent exactly. Note that this behavior applies even for databases which standardize upper case names as case insensitive such as Oracle Database.

The name field may be omitted at construction time and applied later, at any time before the Column is associated with a _schema.Table. This is to support convenient usage within the declarative extension.
type_ –
The column’s type, indicated using an instance which subclasses TypeEngine. If no arguments are required for the type, the class of the type can be sent as well, e.g.:
```
# use a type with arguments
Column("data", String(50))
# use no arguments
Column("level", Integer)
```
The type argument may be the second positional argument or specified by keyword.

If the type is None or is omitted, it will first default to the special type NullType. If and when this _schema.Column is made to refer to another column using _schema.ForeignKey and/or _schema.ForeignKeyConstraint, the type of the remote-referenced column will be copied to this column as well, at the moment that the foreign key is resolved against that remote _schema.Column object.
*args – Additional positional arguments include various SchemaItem derived constructs which will be applied as options to the column. These include instances of Constraint, _schema.ForeignKey, ColumnDefault, Sequence, Computed Identity. In some cases an equivalent keyword argument is available such as server_default, default and unique.
autoincrement –
Set up “auto increment” semantics for an integer primary key column with no foreign key dependencies (see later in this docstring for a more specific definition). This may influence the DDL that will be emitted for this column during a table create, as well as how the column will be considered when INSERT statements are compiled and executed.

The default value is the string "auto", which indicates that a single-column (i.e. non-composite) primary key that is of an INTEGER type with no other client-side or server-side default constructs indicated should receive auto increment semantics automatically. Other values include True (force this column to have auto-increment semantics for a composite primary key as well), False (this column should never have auto-increment semantics), and the string "ignore_fk" (special-case for foreign key columns, see below).

The term “auto increment semantics” refers both to the kind of DDL that will be emitted for the column within a CREATE TABLE statement, when methods such as MetaData.create_all() and Table.create() are invoked, as well as how the column will be considered when an INSERT statement is compiled and emitted to the database:
- DDL rendering (i.e. MetaData.create_all(), Table.create()): When used on a Column that has no other default-generating construct associated with it (such as a Sequence or Identity construct), the parameter will imply that database-specific keywords such as PostgreSQL SERIAL, MySQL AUTO_INCREMENT, or IDENTITY on SQL Server should also be rendered. Not every database backend has an “implied” default generator available; for example the Oracle Database backends alway needs an explicit construct such as Identity to be included with a Column in order for the DDL rendered to include auto-generating constructs to also be produced in the database.
- INSERT semantics (i.e. when a _sql.insert() construct is compiled into a SQL string and is then executed on a database using _engine.Connection.execute() or equivalent): A single-row INSERT statement will be known to produce a new integer primary key value automatically for this column, which will be accessible after the statement is invoked via the CursorResult.inserted_primary_key attribute upon the _result.Result object. This also applies towards use of the ORM when ORM-mapped objects are persisted to the database, indicating that a new integer primary key will be available to become part of the identity key for that object. This behavior takes place regardless of what DDL constructs are associated with the _schema.Column and is independent of the “DDL Rendering” behavior discussed in the previous note above.
The parameter may be set to True to indicate that a column which is part of a composite (i.e. multi-column) primary key should have autoincrement semantics, though note that only one column within a primary key may have this setting. It can also be set to True to indicate autoincrement semantics on a column that has a client-side or server-side default configured, however note that not all dialects can accommodate all styles of default as an “autoincrement”. It can also be set to False on a single-column primary key that has a datatype of INTEGER in order to disable auto increment semantics for that column.

The setting only has an effect for columns which are:
- Integer derived (i.e. INT, SMALLINT, BIGINT).
- Part of the primary key
- Not referring to another column via _schema.ForeignKey, unless the value is specified as 'ignore_fk':
```
# turn on autoincrement for this column despite
# the ForeignKey()
Column(
    "id",
    ForeignKey("other.id"),
    primary_key=True,
    autoincrement="ignore_fk",
)
```
It is typically not desirable to have “autoincrement” enabled on a column that refers to another via foreign key, as such a column is required to refer to a value that originates from elsewhere.

The setting has these effects on columns that meet the above criteria:
- DDL issued for the column, if the column does not already include a default generating construct supported by the backend such as Identity, will include database-specific keywords intended to signify this column as an “autoincrement” column for specific backends. Behavior for primary SQLAlchemy dialects includes:
  - AUTO INCREMENT on MySQL and MariaDB
  - SERIAL on PostgreSQL
  - IDENTITY on MS-SQL - this occurs even without the Identity construct as the `.Column.autoincrement` parameter pre-dates this construct.
  - SQLite - SQLite integer primary key columns are implicitly “auto incrementing” and no additional keywords are rendered; to render the special SQLite keyword AUTOINCREMENT is not included as this is unnecessary and not recommended by the database vendor. See the section sqlite_autoincrement for more background.
  - Oracle Database - The Oracle Database dialects have no default “autoincrement” feature available at this time, instead the Identity construct is recommended to achieve this (the Sequence construct may also be used).
  - Third-party dialects - consult those dialects’ documentation for details on their specific behaviors.
- When a single-row _sql.insert() construct is compiled and executed, which does not set the _sql.Insert.inline() modifier, newly generated primary key values for this column will be automatically retrieved upon statement execution using a method specific to the database driver in use:
  - MySQL, SQLite - calling upon cursor.lastrowid() (see https://www.python.org/dev/peps/pep-0249/#lastrowid)
  - PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent construct when rendering an INSERT statement, and then retrieving the newly generated primary key values after execution
  - PostgreSQL, Oracle Database for _schema.Table objects that set `_schema.Table.implicit_returning` to False - for a Sequence only, the Sequence is invoked explicitly before the INSERT statement takes place so that the newly generated primary key value is available to the client
  - SQL Server for _schema.Table objects that set `_schema.Table.implicit_returning` to False - the SELECT scope_identity() construct is used after the INSERT statement is invoked to retrieve the newly generated primary key value.
  - Third-party dialects - consult those dialects’ documentation for details on their specific behaviors.
- For multiple-row _sql.insert() constructs invoked with a list of parameters (i.e. “executemany” semantics), primary-key retrieving behaviors are generally disabled, however there may be special APIs that may be used to retrieve lists of new primary key values for an “executemany”, such as the psycopg2 “fast insertmany” feature. Such features are very new and may not yet be well covered in documentation.
default –
A scalar, Python callable, or _expression.ColumnElement expression representing the default value for this column, which will be invoked upon insert if this column is otherwise not specified in the VALUES clause of the insert. This is a shortcut to using ColumnDefault as a positional argument; see that class for full detail on the structure of the argument.

Contrast this argument to `_schema.Column.server_default` which creates a default generator on the database side.

See also

metadata_defaults_toplevel
insert_default –
An alias of `.Column.default` for compatibility with _orm.mapped_column().
doc – optional String that can be used by the ORM or similar to document attributes on the Python side. This attribute does not render SQL comments; use the `_schema.Column.comment` parameter for this purpose.
key – An optional string identifier which will identify this Column object on the _schema.Table. When a key is provided, this is the only identifier referencing the Column within the application, including ORM attribute mapping; the name field is used only when rendering SQL.
index –
When True, indicates that a _schema.Index construct will be automatically generated for this _schema.Column, which will result in a “CREATE INDEX” statement being emitted for the _schema.Table when the DDL create operation is invoked.

Using this flag is equivalent to making use of the _schema.Index construct explicitly at the level of the _schema.Table construct itself:
```
Table(
    "some_table",
    metadata,
    Column("x", Integer),
    Index("ix_some_table_x", "x"),
)
```
To add the `_schema.Index.unique` flag to the _schema.Index, set both the `_schema.Column.unique` and `_schema.Column.index` flags to True simultaneously, which will have the effect of rendering the “CREATE UNIQUE INDEX” DDL instruction instead of “CREATE INDEX”.

The name of the index is generated using the default naming convention which for the _schema.Index construct is of the form ix_<tablename>_<columnname>.

As this flag is intended only as a convenience for the common case of adding a single-column, default configured index to a table definition, explicit use of the _schema.Index construct should be preferred for most use cases, including composite indexes that encompass more than one column, indexes with SQL expressions or ordering, backend-specific index configuration options, and indexes that use a specific name.

Note

the _schema.Column.index attribute on _schema.Column does not indicate if this column is indexed or not, only if this flag was explicitly set here. To view indexes on a column, view the _schema.Table.indexes collection or use _reflection.Inspector.get_indexes().

See also

schema_indexes

constraint_naming_conventions

`_schema.Column.unique`
info – Optional data dictionary which will be populated into the SchemaItem.info attribute of this object.
nullable –
When set to False, will cause the “NOT NULL” phrase to be added when generating DDL for the column. When True, will normally generate nothing (in SQL this defaults to “NULL”), except in some very specific backend-specific edge cases where “NULL” may render explicitly. Defaults to True unless `_schema.Column.primary_key` is also True or the column specifies a _sql.Identity, in which case it defaults to False. This parameter is only used when issuing CREATE TABLE statements.

Note

When the column specifies a _sql.Identity this parameter is in general ignored by the DDL compiler. The PostgreSQL database allows nullable identity column by setting this parameter to True explicitly.
onupdate –
A scalar, Python callable, or ClauseElement representing a default value to be applied to the column within UPDATE statements, which will be invoked upon update if this column is not present in the SET clause of the update. This is a shortcut to using ColumnDefault as a positional argument with for_update=True.

See also

metadata_defaults - complete discussion of onupdate
primary_key – If True, marks this column as a primary key column. Multiple columns can have this flag set to specify composite primary keys. As an alternative, the primary key of a _schema.Table can be specified via an explicit PrimaryKeyConstraint object.
server_default –
A FetchedValue instance, str, Unicode or text() construct representing the DDL DEFAULT value for the column.

String types will be emitted as-is, surrounded by single quotes:
```
Column("x", Text, server_default="val")
```
will render:
```
x TEXT DEFAULT 'val'
```
A text() expression will be rendered as-is, without quotes:
```
Column("y", DateTime, server_default=text("NOW()"))
```
will render:
```
y DATETIME DEFAULT NOW()
```
Strings and text() will be converted into a DefaultClause object upon initialization.

This parameter can also accept complex combinations of contextually valid SQLAlchemy expressions or constructs:
```
from sqlalchemy import create_engine
from sqlalchemy import Table, Column, MetaData, ARRAY, Text
from sqlalchemy.dialects.postgresql import array
engine = create_engine(
    "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
)
metadata_obj = MetaData()
tbl = Table(
    "foo",
    metadata_obj,
    Column(
        "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
    ),
)
metadata_obj.create_all(engine)
```
The above results in a table created with the following SQL:
```
CREATE TABLE foo (
    bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
)
```
Use FetchedValue to indicate that an already-existing column will generate a default value on the database side which will be available to SQLAlchemy for post-fetch after inserts. This construct does not specify any DDL and the implementation is left to the database, such as via a trigger.

See also

server_defaults - complete discussion of server side defaults
server_onupdate –
A FetchedValue instance representing a database-side default generation function, such as a trigger. This indicates to SQLAlchemy that a newly generated value will be available after updates. This construct does not actually implement any kind of generation function within the database, which instead must be specified separately.

Warning

This directive does not currently produce MySQL’s “ON UPDATE CURRENT_TIMESTAMP()” clause. See mysql_timestamp_onupdate for background on how to produce this clause.

See also

triggered_columns
quote – Force quoting of this column’s name on or off, corresponding to True or False. When left at its default of None, the column identifier will be quoted according to whether the name is case sensitive (identifiers with at least one upper case character are treated as case sensitive), or if it’s a reserved word. This flag is only needed to force quoting of a reserved word which is not known by the SQLAlchemy dialect.
unique –
When True, and the `_schema.Column.index` parameter is left at its default value of False, indicates that a _schema.UniqueConstraint construct will be automatically generated for this _schema.Column, which will result in a “UNIQUE CONSTRAINT” clause referring to this column being included in the CREATE TABLE statement emitted, when the DDL create operation for the _schema.Table object is invoked.

When this flag is True while the `_schema.Column.index` parameter is simultaneously set to True, the effect instead is that a _schema.Index construct which includes the `_schema.Index.unique` parameter set to True is generated. See the documentation for `_schema.Column.index` for additional detail.

Using this flag is equivalent to making use of the _schema.UniqueConstraint construct explicitly at the level of the _schema.Table construct itself:
```
Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
```
The `_schema.UniqueConstraint.name` parameter of the unique constraint object is left at its default value of None; in the absence of a naming convention for the enclosing _schema.MetaData, the UNIQUE CONSTRAINT construct will be emitted as unnamed, which typically invokes a database-specific naming convention to take place.

As this flag is intended only as a convenience for the common case of adding a single-column, default configured unique constraint to a table definition, explicit use of the _schema.UniqueConstraint construct should be preferred for most use cases, including composite constraints that encompass more than one column, backend-specific index configuration options, and constraints that use a specific name.

Note

the _schema.Column.unique attribute on _schema.Column does not indicate if this column has a unique constraint or not, only if this flag was explicitly set here. To view indexes and unique constraints that may involve this column, view the _schema.Table.indexes and/or _schema.Table.constraints collections or use _reflection.Inspector.get_indexes() and/or _reflection.Inspector.get_unique_constraints()

See also

schema_unique_constraint

constraint_naming_conventions

`_schema.Column.index`
system –
When True, indicates this is a “system” column, that is a column which is automatically made available by the database, and should not be included in the columns list for a CREATE TABLE statement.

For more elaborate scenarios where columns should be conditionally rendered differently on different backends, consider custom compilation rules for CreateColumn.
comment –
Optional string that will render an SQL comment on table creation.

Added in version 1.2: Added the `_schema.Column.comment` parameter to _schema.Column.
insert_sentinel –
Marks this _schema.Column as an insert sentinel used for optimizing the performance of the insertmanyvalues feature for tables that don’t otherwise have qualifying primary key configurations.

Added in version 2.0.10.

See also

_schema.insert_sentinel() - all in one helper for declaring sentinel columns

engine_insertmanyvalues

engine_insertmanyvalues_sentinel_columns

Methods

`__init__`(*args[, persisted_expression])	Construct a new `Column` object.
`all_`()	Produce an `_expression.all_()` clause against the parent object.
`any_`()	Produce an `_expression.any_()` clause against the parent object.
`append_foreign_key`(fk)
`argument_for`(dialect_name, argument_name, ...)	Add a new kind of dialect-specific keyword argument for this class.
`asc`()	Produce a `_expression.asc()` clause against the parent object.
`between`(cleft, cright[, symmetric])	Produce a `_expression.between()` clause against the parent object, given the lower and upper range.
`bitwise_and`(other)	Produce a bitwise AND operation, typically via the `&` operator.
`bitwise_lshift`(other)	Produce a bitwise LSHIFT operation, typically via the `<<` operator.
`bitwise_not`()	Produce a bitwise NOT operation, typically via the `~` operator.
`bitwise_or`(other)	Produce a bitwise OR operation, typically via the `\|` operator.
`bitwise_rshift`(other)	Produce a bitwise RSHIFT operation, typically via the `>>` operator.
`bitwise_xor`(other)	Produce a bitwise XOR operation, typically via the `^` operator, or `#` for PostgreSQL.
`bool_op`(opstring[, precedence, python_impl])	Return a custom boolean operator.
`cast`(type_)	Produce a type cast, i.e. `CAST(<expression> AS <type>)`.
`collate`(collation)	Produce a `_expression.collate()` clause against the parent object, given the collation string.
`compare`(other, **kw)	Compare this `_expression.ClauseElement` to the given `_expression.ClauseElement`.
`compile`([bind, dialect])	Compile this SQL expression.
`concat`(other)	Implement the 'concat' operator.
`contains`(other, **kw)	Implement the 'contains' operator.
`copy`(**kw)
`desc`()	Produce a `_expression.desc()` clause against the parent object.
`distinct`()	Produce a `_expression.distinct()` clause against the parent object.
`endswith`(other[, escape, autoescape])	Implement the 'endswith' operator.
`get_children`(*[, column_tables])	Return immediate child `visitors.HasTraverseInternals` elements of this `visitors.HasTraverseInternals`.
`icontains`(other, **kw)	Implement the `icontains` operator, e.g. case insensitive version of `ColumnOperators.contains()`.
`iendswith`(other[, escape, autoescape])	Implement the `iendswith` operator, e.g. case insensitive version of `ColumnOperators.endswith()`.
`ilike`(other[, escape])	Implement the `ilike` operator, e.g. case insensitive LIKE.
`in_`(other)	Implement the `in` operator.
`is_`(other)	Implement the `IS` operator.
`is_distinct_from`(other)	Implement the `IS DISTINCT FROM` operator.
`is_not`(other)	Implement the `IS NOT` operator.
`is_not_distinct_from`(other)	Implement the `IS NOT DISTINCT FROM` operator.
`isnot`(other)	Implement the `IS NOT` operator.
`isnot_distinct_from`(other)	Implement the `IS NOT DISTINCT FROM` operator.
`istartswith`(other[, escape, autoescape])	Implement the `istartswith` operator, e.g. case insensitive version of `ColumnOperators.startswith()`.
`label`(name)	Produce a column label, i.e. `<columnname> AS <name>`.
`like`(other[, escape])	Implement the `like` operator.
`match`(other, **kwargs)	Implements a database-specific 'match' operator.
`memoized_instancemethod`(fn)	Decorate a method memoize its return value.
`not_ilike`(other[, escape])	implement the `NOT ILIKE` operator.
`not_in`(other)	implement the `NOT IN` operator.
`not_like`(other[, escape])	implement the `NOT LIKE` operator.
`notilike`(other[, escape])	implement the `NOT ILIKE` operator.
`notin_`(other)	implement the `NOT IN` operator.
`notlike`(other[, escape])	implement the `NOT LIKE` operator.
`nulls_first`()	Produce a `_expression.nulls_first()` clause against the parent object.
`nulls_last`()	Produce a `_expression.nulls_last()` clause against the parent object.
`nullsfirst`()	Produce a `_expression.nulls_first()` clause against the parent object.
`nullslast`()	Produce a `_expression.nulls_last()` clause against the parent object.
`op`(opstring[, precedence, is_comparison, ...])	Produce a generic operator function.
`operate`(op, other, *kwargs)	Operate on an argument.
`params`(optionaldict, *kwargs)	Return a copy with `_expression.bindparam()` elements replaced.
`references`(column)	Return True if this Column references the given column via foreign key.
`regexp_match`(pattern[, flags])	Implements a database-specific 'regexp match' operator.
`regexp_replace`(pattern, replacement[, flags])	Implements a database-specific 'regexp replace' operator.
`reverse_operate`(op, other, **kwargs)	Reverse operate on an argument.
`self_group`([against])	Apply a 'grouping' to this `_expression.ClauseElement`.
`shares_lineage`(othercolumn)	Return True if the given `_expression.ColumnElement` has a common ancestor to this `_expression.ColumnElement`.
`startswith`(other[, escape, autoescape])	Implement the `startswith` operator.
`unique_params`(optionaldict, *kwargs)	Return a copy with `_expression.bindparam()` elements replaced.

Attributes

`allows_lambda`
`anon_key_label`
`anon_label`
`base_columns`
`comparator`
`create_drop_stringify_dialect`
`default`
`description`
`dialect_kwargs`	A collection of keyword arguments specified as dialect-specific options to this construct.
`dialect_options`	A collection of keyword arguments specified as dialect-specific options to this construct.
`dispatch`
`entity_namespace`
`expression`	Return a column expression.
`foreign_keys`	A collection of all `_schema.ForeignKey` marker objects associated with this `_schema.Column`.
`info`	Info dictionary associated with the object, allowing user-defined data to be associated with this `SchemaItem`.
`inherit_cache`	Indicate if this `HasCacheKey` instance should make use of the cache key generation scheme used by its immediate superclass.
`is_clause_element`
`is_dml`
`is_literal`
`is_selectable`
`key`	The 'key' that in some circumstances refers to this object in a Python namespace.
`kwargs`	A synonym for `DialectKWArgs.dialect_kwargs`.
`onupdate`
`primary_key`
`proxy_set`	set of all columns we are proxying
`server_default`
`server_onupdate`
`stringify_dialect`
`supports_execution`
`table`
`timetuple`	Hack, allows datetime objects to be compared on the LHS.
`type`
`uses_inspection`
`constraints`
`index`	The value of the `_schema.Column.index` parameter.
`unique`	The value of the `_schema.Column.unique` parameter.
`computed`
`identity`
`name`
`negation_clause`