SqliteDatabase¶

class lsst.daf.butler.registry.databases.sqlite.SqliteDatabase(*, connection: sqlalchemy.engine.base.Connection, origin: int, namespace: Optional[str] = None, writeable: bool = True)¶

Bases: lsst.daf.butler.registry.interfaces.Database

An implementation of the Database interface for SQLite3.

Parameters:

connection : sqlalchemy.engine.Connection: An existing connection created by a previous call to connect.
origin : int: 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.
namespace : str, optional: The namespace (schema) this database is associated with. If None, the default schema for the connection is used (which may be None).
writeable : bool, 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.

Methods Summary

`connect`(uri, *, filename, writeable)	Create a `sqlalchemy.engine.Connection` from a SQLAlchemy URI or filename.
`declareStaticTables`(*, create)	Return a context manager in which the database’s static DDL schema can be declared.
`delete`(table, columns, *rows)	Delete one or more rows from a table.
`ensureTableExists`(name, spec)	Ensure that a table with the given name and specification exists, creating it if necessary.
`expandDatabaseEntityName`(shrunk)	Retrieve the original name for a database entity that was too long to fit within the database engine’s limits.
`fromConnection`(connection, *, origin, …)	Create a new `Database` from an existing `sqlalchemy.engine.Connection`.
`fromUri`(uri, *, origin, namespace, writeable)	Construct a database from a SQLAlchemy URI.
`getExistingTable`(name, spec)	Obtain an existing table with the given name and specification.
`insert`(table, *rows, returnIds)	Insert one or more rows into a table, optionally returning autoincrement primary key values.
`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.
`query`(sql, args, *kwds)	Run a SELECT query against the database.
`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.
`shrinkDatabaseEntityName`(original)	Return a version of the given name that fits within this database engine’s length limits for table, constraint, indexes, and sequence names.
`sync`(table, *, keys, Any], compared, …)	Insert into a table as necessary to ensure database contains values equivalent to the given ones.
`transaction`(*, interrupting)	Return a context manager that represents a transaction.
`update`(table, where, str], *rows)	Update one or more rows in a table.

Methods Documentation

classmethod connect(uri: Optional[str] = None, *, filename: Optional[str] = None, writeable: bool = True) → sqlalchemy.engine.base.Connection¶

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

Parameters:

uri : str: A SQLAlchemy URI connection string.
filename : str: Name of the SQLite database file, or None to use an in-memory database. Ignored if uri is not None.
origin : int: 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.
writeable : bool, optional: If True, allow write operations on the database, including CREATE TABLE.

Returns:

cs : sqlalchemy.engine.Connection: A database connection and transaction state.

declareStaticTables(*, create: bool) → AbstractContextManager[lsst.daf.butler.registry.interfaces._database.StaticTablesContext]¶

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

Parameters:	create : `bool` If `True`, attempt to create all tables at the end of the context. If `False`, they will be assumed to already exist.
Returns:	schema : `StaticTablesContext` 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(...))

delete(table: sqlalchemy.sql.schema.Table, columns: Iterable[str], *rows) → int¶

Delete one or more rows from a table.

Parameters:	table : `sqlalchemy.schema.Table` Table that rows should be deleted from. columns: `~collections.abc.Iterable` of `str` The names of columns that will be used to constrain the rows to be deleted; these will be combined via `AND` to form the `WHERE` clause of the delete query. *rows Positional arguments are the keys of rows to be deleted, as dictionaries mapping column name to value. The keys in all dictionaries must exactly the names in `columns`.
Returns:	count : `int` Number of rows deleted.
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.

The default implementation should be sufficient for most derived classes.

ensureTableExists(name: str, spec: lsst.daf.butler.core.ddl.TableSpec) → sqlalchemy.sql.selectable.FromClause¶

Ensure that a table with the given name and specification exists, creating it if necessary.

Parameters:	name : `str` Name of the table (not including namespace qualifiers). spec : `TableSpec` Specification for the table. This will be used when creating the table, and may be used when obtaining an existing table to check for consistency, but no such check is guaranteed.
Returns:	table : `sqlalchemy.schema.Table` SQLAlchemy representation of the table.
Raises:	ReadOnlyDatabaseError Raised if `isWriteable` returns `False`, and the table does not already exist. DatabaseConflictError Raised if the table exists but `spec` is inconsistent with its definition.

Notes

This method may not be called within transactions. It may be called on read-only databases if and only if the table does in fact already exist.

Subclasses may override this method, but usually should not need to.

expandDatabaseEntityName(shrunk: str) → str¶

Retrieve the original name for a database entity that was too long to fit within the database engine’s limits.

Parameters:	original : `str` The original name.
Returns:	shrunk : `str` The new, possibly shortened name.

classmethod fromConnection(connection: sqlalchemy.engine.base.Connection, *, origin: int, namespace: Optional[str] = None, writeable: bool = True) → lsst.daf.butler.registry.interfaces._database.Database¶

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

Parameters:

connection : sqllachemy.engine.Connection: The connection for the the database. May be shared between Database instances.
origin : int: 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.
namespace : str, 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.
writeable : bool, optional: If True, allow write operations on the database, including CREATE TABLE.

Returns:

db : Database: A new Database instance.

Notes

This method allows different Database instances to share the same connection, which is desirable when they represent different namespaces can be queried together. This also ties their transaction state, however; starting a transaction in any database automatically starts on in all other databases.

classmethod fromUri(uri: str, *, origin: int, namespace: Optional[str] = None, writeable: bool = True) → lsst.daf.butler.registry.interfaces._database.Database¶

Construct a database from a SQLAlchemy URI.

Parameters:

uri : str: A SQLAlchemy URI connection string.
origin : int: 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.
namespace : str, optional: A database namespace (i.e. schema) the new instance should be associated with. If None (default), the namespace (if any) is inferred from the URI.
writeable : bool, optional: If True, allow write operations on the database, including CREATE TABLE.

Returns:

db : Database: A new Database instance.

getExistingTable(name: str, spec: lsst.daf.butler.core.ddl.TableSpec) → Optional[sqlalchemy.sql.schema.Table]¶

Obtain an existing table with the given name and specification.

Parameters:	name : `str` Name of the table (not including namespace qualifiers). spec : `TableSpec` Specification for the table. This will be used when creating the SQLAlchemy representation of the table, and it is used to check that the actual table in the database is consistent.
Returns:	table : `sqlalchemy.schema.Table` or `None` SQLAlchemy representation of the table, or `None` if it does not exist.
Raises:	DatabaseConflictError Raised if the table exists but `spec` is inconsistent with its definition.

Notes

This method can be called within transactions and never modifies the database.

Subclasses may override this method, but usually should not need to.

insert(table: sqlalchemy.sql.schema.Table, *rows, returnIds: bool = False) → Optional[List[int]]¶

Insert one or more rows into a table, optionally returning autoincrement primary key values.

Parameters:	table : `sqlalchemy.schema.Table` Table rows should be inserted into. returnIds: `bool` If `True` (`False` is default), return the values of the table’s autoincrement primary key field (which much exist). *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.
Returns:	ids : `None`, or `list` of `int` If `returnIds` is `True`, a `list` containing the inserted values for the table’s autoincrement primary key.
Raises:	ReadOnlyDatabaseError Raised if `isWriteable` returns `False` when this method is called.

Notes

The default implementation uses bulk insert syntax when returnIds is False, and a loop over single-row insert operations when it is True.

Derived classes should reimplement when they can provide a more efficient implementation (especially for the latter case).

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

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

classmethod makeDefaultUri(root: str) → Optional[str]¶: Create a default connection URI appropriate for the given root directory, or None if there can be no such default.

query(sql: sqlalchemy.sql.selectable.FromClause, *args, **kwds) → sqlalchemy.engine.result.ResultProxy¶

Run a SELECT query against the database.

Parameters:	sql : `sqlalchemy.sql.FromClause` A SQLAlchemy representation of a `SELECT` query. args Additional positional arguments are forwarded to `sqlalchemy.engine.Connection.execute`. *kwds Additional keyword arguments are forwarded to `sqlalchemy.engine.Connection.execute`.
Returns:	result : `sqlalchemy.engine.ResultProxy` Query results.

Notes

The default implementation should be sufficient for most derived classes.

replace(table: sqlalchemy.sql.schema.Table, *rows) → 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:	table : `sqlalchemy.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.

shrinkDatabaseEntityName(original: str) → str¶

Return a version of the given name that fits within this database engine’s length limits for table, constraint, indexes, and sequence names.

Implementations should not assume that simple truncation is safe, because multiple long names often begin with the same prefix.

The default implementation simply returns the given name.

Parameters:	original : `str` The original name.
Returns:	shrunk : `str` The new, possibly shortened name.

sync(table: sqlalchemy.sql.schema.Table, *, keys: Dict[str, Any], compared: Optional[Dict[str, Any]] = None, extra: Optional[Dict[str, Any]] = None, returning: Optional[Sequence[str]] = None) → Tuple[Optional[Dict[str, Any]], bool]¶

Insert into a table as necessary to ensure database contains values equivalent to the given ones.

Parameters:	table : `sqlalchemy.schema.Table` Table to be queried and possibly inserted into. keys : `dict` Column name-value pairs used to search for an existing row; must be a combination that can be used to select a single row if one exists. If such a row does not exist, these values are used in the insert. compared : `dict`, optional Column name-value pairs that are compared to those in any existing row. If such a row does not exist, these rows are used in the insert. extra : `dict`, optional Column name-value pairs that are ignored if a matching row exists, but used in an insert if one is necessary. returning : `Sequence` of `str`, optional The names of columns whose values should be returned.
Returns:	row : `dict`, optional The value of the fields indicated by `returning`, or `None` if `returning` is `None`. inserted : `bool` If `True`, a new row was inserted.
Raises:	DatabaseConflictError Raised if the values in `compared` do not match the values in the database. ReadOnlyDatabaseError Raised if `isWriteable` returns `False`, and no matching record already exists.

Notes

This method may not be called within transactions. It may be called on read-only databases if and only if the matching row does in fact already exist.

transaction(*, interrupting: bool = False) → Iterator[T_co]¶

Return a context manager that represents a transaction.

Parameters:	interrupting : `bool` If `True`, this transaction block needs to be able to interrupt any existing one in order to yield correct behavior.

update(table: sqlalchemy.sql.schema.Table, where: Dict[str, str], *rows) → int¶

Update one or more rows in a table.

Parameters:	table : `sqlalchemy.schema.Table` Table containing the rows to be updated. where : `dict` [`str`, `str`] A mapping from the names of columns that will be used to search for existing rows to the keys that will hold these values in the `rows` dictionaries. Note that these may not be the same due to SQLAlchemy limitations. *rows Positional arguments are the rows to be updated. The keys in all dictionaries must be the same, and may correspond to either a value in the `where` dictionary or the name of a column to be updated.
Returns:	count : `int` Number of rows matched (regardless of whether the update actually modified them).
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.

The default implementation should be sufficient for most derived classes.

Navigation

SqliteDatabase¶