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:
- engine
sqlalchemy.engine.Engine
Engine to use for this connection.
- 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 beNone
).- writeable
bool
, optional If
True
, allow write operations on the database, includingCREATE TABLE
.
- engine
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
Whether this database supports the
ANY_VALUE
aggregate function or something equivalent.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 existingsqlalchemy.engine.Engine
.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:
- column
sqlalchemy.ColumnElement
Original column to wrap.
- column
- Returns:
- wrapped
sqlalchemy.ColumnElement
A column element of the same SQL type that can appear in the
SELECT
clause even when this column does not appear in theGROUP BY
clause.
- wrapped
Notes
This method’s behavior is unspecified when
has_any_aggregate
isFalse
; the caller is responsible for checking that property first.
- clone() SqliteDatabase ¶
Make an independent copy of this
Database
object.- Returns:
- db
Database
A new
Database
instance with the same configuration as this instance.
- db
- 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:
- Returns:
- from_clause
sqlalchemy.sql.FromClause
SQLAlchemy object representing the given rows. This is guaranteed to be something that can be directly joined into a
SELECT
query’sFROM
clause, and will not involve a temporary table that needs to be cleaned up later.
- from_clause
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 tosuper
.
- declareStaticTables(*, create: bool) AbstractContextManager[StaticTablesContext] ¶
Return a context manager in which the database’s static DDL schema can be declared.
- Parameters:
- Returns:
- schema
StaticTablesContext
A helper object that is used to add new tables.
- schema
- Raises:
Notes
A database’s static DDL schema must be declared before any dynamic tables are managed via calls to
ensureTableExists
orgetExistingTable
. 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
instancedb
: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:
- 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.
- primary_key_only
bool
, 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.
- table
- Returns:
- count
int
The number of rows actually inserted.
- count
- Raises:
- ReadOnlyDatabaseError
Raised if
isWriteable
returnsFalse
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 existingsqlalchemy.engine.Engine
.- Parameters:
- engine
sqlalchemy.engine.Engine
The engine for 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, includingCREATE TABLE
.
- engine
- Returns:
- db
Database
A new
Database
instance.
- db
Notes
This method allows different
Database
instances to share the same engine, which is desirable when they represent different namespaces can be queried together.
- 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.
- 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:
- Returns:
- engine
sqlalchemy.engine.Engine
A database engine.
- 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:
- 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.
- table
- Raises:
- ReadOnlyDatabaseError
Raised if
isWriteable
returnsFalse
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.