SqliteDatabase¶

class lsst.daf.butler.registry.databases.sqlite.SqliteDatabase(*, engine: Engine, origin: int, namespace: str | None = None, writeable: bool = True)¶

Bases: Database

An implementation of the Database interface for SQLite3.

Parameters:

enginesqlalchemy.engine.Engine: Engine to use for this connection.
originint: An integer ID that should be used as the default for any datasets, quanta, or other entities that use a (autoincrement, origin) compound primary key.
namespacestr, optional: The namespace (schema) this database is associated with. If None, the default schema for the connection is used (which may be None).
writeablebool, optional: If True, allow write operations on the database, including CREATE TABLE.

Notes

The case where namespace is not None is not yet tested, and may be broken; we need an API for attaching to different databases in order to write those tests, but haven’t yet worked out what is common/different across databases well enough to define it.

Attributes Summary

`has_any_aggregate`	Whether this database supports the `ANY_VALUE` aggregate function or something equivalent.
`has_distinct_on`	Whether this database supports the `DISTINCT ON` SQL construct.

Methods Summary

`apply_any_aggregate`(column)	Wrap the given SQLAlchemy column in the `ANY_VALUE` aggregate function or its equivalent.
`clone`()	Make an independent copy of this `Database` object.
`constant_rows`(fields, *rows[, name])	Return a SQLAlchemy object that represents a small number of constant-valued rows.
`declareStaticTables`(*, create)	Return a context manager in which the database's static DDL schema can be declared.
`ensure`(table, *rows[, primary_key_only])	Insert one or more rows into a table, skipping any rows for which insertion would violate a unique constraint.
`fromEngine`(engine, *, origin[, namespace, ...])	Create a new `Database` from an existing `sqlalchemy.engine.Engine`.
`get_constant_rows_max`()	Return the maximum number of rows that should be passed to `constant_rows` for this backend.
`isWriteable`()	Return `True` if this database can be modified by this client.
`makeDefaultUri`(root)	Create a default connection URI appropriate for the given root directory, or `None` if there can be no such default.
`makeEngine`([uri, filename, writeable])	Create a `sqlalchemy.engine.Engine` from a SQLAlchemy URI or filename.
`replace`(table, *rows)	Insert one or more rows into a table, replacing any existing rows for which insertion of a new row would violate the primary key constraint.

Attributes Documentation

has_any_aggregate¶

has_distinct_on¶

Methods Documentation

apply_any_aggregate(column: ColumnElement[Any]) → ColumnElement[Any]¶

Wrap the given SQLAlchemy column in the ANY_VALUE aggregate function or its equivalent.

Parameters:

columnsqlalchemy.ColumnElement: Original column to wrap.

Returns:

wrappedsqlalchemy.ColumnElement: A column element of the same SQL type that can appear in the SELECT clause even when this column does not appear in the GROUP BY clause.

Notes

This method’s behavior is unspecified when has_any_aggregate is False; the caller is responsible for checking that property first.

clone() → SqliteDatabase¶

Make an independent copy of this Database object.

Returns:

dbDatabase: A new Database instance with the same configuration as this instance.

constant_rows(fields: NamedValueAbstractSet[FieldSpec], *rows: dict, name: str | None = None) → FromClause¶

Return a SQLAlchemy object that represents a small number of constant-valued rows.

Parameters:

fieldsNamedValueAbstractSet [ ddl.FieldSpec ]: The columns of the rows. Unique and foreign key constraints are ignored.
*rowsdict: Values for the rows.
namestr, optional: If provided, the name of the SQL construct. If not provided, an opaque but unique identifier is generated.

Returns:

from_clausesqlalchemy.sql.FromClause: SQLAlchemy object representing the given rows. This is guaranteed to be something that can be directly joined into a SELECT query’s FROM clause, and will not involve a temporary table that needs to be cleaned up later.

Notes

The default implementation uses the SQL-standard VALUES construct, but support for that construct is varied enough across popular RDBMSs that the method is still marked abstract to force explicit opt-in via delegation to super.

declareStaticTables(*, create: bool) → AbstractContextManager[StaticTablesContext]¶

Return a context manager in which the database’s static DDL schema can be declared.

Parameters:

createbool: If True, attempt to create all tables at the end of the context. If False, they will be assumed to already exist.

Returns:

schemaStaticTablesContext: A helper object that is used to add new tables.

Raises:

ReadOnlyDatabaseError: Raised if create is True, Database.isWriteable is False, and one or more declared tables do not already exist.

Notes

A database’s static DDL schema must be declared before any dynamic tables are managed via calls to ensureTableExists or getExistingTable. The order in which static schema tables are added inside the context block is unimportant; they will automatically be sorted and added in an order consistent with their foreign key relationships.

Examples

Given a Database instance db:

with db.declareStaticTables(create=True) as schema:
    schema.addTable("table1", TableSpec(...))
    schema.addTable("table2", TableSpec(...))

ensure(table: Table, *rows: dict, primary_key_only: bool = False) → int¶

Insert one or more rows into a table, skipping any rows for which insertion would violate a unique constraint.

Parameters:

tablesqlalchemy.schema.Table: Table rows should be inserted into.
*rows: Positional arguments are the rows to be inserted, as dictionaries mapping column name to value. The keys in all dictionaries must be the same.
primary_key_onlybool, optional: If True (False is default), only skip rows that violate the primary key constraint, and raise an exception (and rollback transactions) for other constraint violations.

Returns:

countint: The number of rows actually inserted.

Raises:

ReadOnlyDatabaseError: Raised if isWriteable returns False when this method is called. This is raised even if the operation would do nothing even on a writeable database.

Notes

May be used inside transaction contexts, so implementations may not perform operations that interrupt transactions.

Implementations are not required to support ensure on tables with autoincrement keys.

classmethod fromEngine(engine: Engine, *, origin: int, namespace: str | None = None, writeable: bool = True) → Database¶

Create a new Database from an existing sqlalchemy.engine.Engine.

Parameters:

enginesqlalchemy.engine.Engine: The engine for the database. May be shared between Database instances.
originint: An integer ID that should be used as the default for any datasets, quanta, or other entities that use a (autoincrement, origin) compound primary key.
namespacestr, optional: A different database namespace (i.e. schema) the new instance should be associated with. If None (default), the namespace (if any) is inferred from the connection.
writeablebool, optional: If True, allow write operations on the database, including CREATE TABLE.

Returns:

dbDatabase: A new Database instance.

Notes

This method allows different Database instances to share the same engine, which is desirable when they represent different namespaces can be queried together.

get_constant_rows_max() → int¶

Return the maximum number of rows that should be passed to constant_rows for this backend.

Returns:

maxint: Maximum number of rows.

Notes

This should reflect typical performance profiles (or a guess at these), not just hard database engine limits.

isWriteable() → bool¶: Return True if this database can be modified by this client.

classmethod makeDefaultUri(root: str) → str | None¶

Create a default connection URI appropriate for the given root directory, or None if there can be no such default.

Parameters:

rootstr: Root string to use to build connection URI.

Returns:

uristr or None: The URI string or None.

classmethod makeEngine(uri: str | URL | None = None, *, filename: str | None = None, writeable: bool = True) → Engine¶

Create a sqlalchemy.engine.Engine from a SQLAlchemy URI or filename.

Parameters:

uristr or sqlalchemy.engine.URL, optional: A SQLAlchemy URI connection string.
filenamestr: Name of the SQLite database file, or None to use an in-memory database. Ignored if uri is not None.
writeablebool, optional: If True, allow write operations on the database, including CREATE TABLE.

Returns:

enginesqlalchemy.engine.Engine: A database engine.

replace(table: Table, *rows: dict) → None¶

Insert one or more rows into a table, replacing any existing rows for which insertion of a new row would violate the primary key constraint.

Parameters:

tablesqlalchemy.schema.Table: Table rows should be inserted into.
*rows: Positional arguments are the rows to be inserted, as dictionaries mapping column name to value. The keys in all dictionaries must be the same.

Raises:

ReadOnlyDatabaseError: Raised if isWriteable returns False when this method is called.

Notes

May be used inside transaction contexts, so implementations may not perform operations that interrupt transactions.

Implementations should raise a sqlalchemy.exc.IntegrityError exception when a constraint other than the primary key would be violated.

Implementations are not required to support replace on tables with autoincrement keys.

Navigation

SqliteDatabase¶