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.

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.

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.