calibpipe.database.connections package#

Connection utilities for the database.

The framework used here is sqlachemy that can be used with different engines and dialects. For now the calibration data is stored in a PostgreSQL database and accessed using the psycopg dialect.

The main connection object is the SQLConnection that provides the interface to a SQL database, not knowing which engine it is (can be Postgres, MySQL, Oracle etc.).

To use the SQLConnection with a different DB system or dialect, it is enough to change the uri and generate the relevant one following the example of the postgres uri.

class calibpipe.database.connections.SQLConnection(uri: str, autocommit: bool = False)[source]#

Bases: object

Interface to communicate with a SQL database.

Once an uri (str) has been generated, the connection can be open in a context to ensure proper closing (and commit if required):

uri: str = get_postgres_uri(user='api-owner', database='calibration')
with SQLConnection(uri=uri, autocommit=True) as connection:
    # e.g.
    # connection.execute(...)
Attributes:
autocommit: bool

Tell if the database changes must be committed automatically when closing the connection (can be done manually by calling the commit() method).

uri: str

Uri used to connect to the database. This attribute is not used anymore once the connection is open.

engine: sqlalchemy.engine.Engine

Engine used for the database connection. It can be of several kinds, the default one is postgres + psycopg. The engine is automatically connected at the initialization step.

session: sqlalchemy.orm.Session

Session (use the engine) used to execute queries to the database.

__init__(uri: str, autocommit: bool = False) → None[source]#

Initialize the session and engine, connect to the database.

Parameters:
uri: str

uri to connect to the database. See the calibpipe.database.connections.get_postgres_uri() to generate the uri connecting to a Postgres database.

autocommit: bool, optional (default=False)

Determines if the connection commits changes when the __exit__() method is called. If set to False (default), changes will not be committed and it is necessary to call commit() after modifications have been done.

close() → None[source]#

Close the session.

If the autocommit attribute is True, changes are committed before closing the connection.

commit() → None[source]#

Commit changes to the database.

execute(*args) → Result[source]#

Execute a query in the SQL session.

This methods forwards the arguments to the sqlalchemy.orm.Session.execute() method of session. Refer to the documentation of sqlalchemy to use queries.

calibpipe.database.connections.get_user_confirmation(prompt: str) → bool[source]#

Ask a confirmation from the user by displaying a prompt and asking yes or no.

Parameters:
prompt: str

Prompt to display

Returns:
bool

Answer from the user

calibpipe.database.connections.get_postgres_uri(user: str, database: str, passwd: str, host: str = 'postgres', port: str | None = None) → str[source]#

Generate a valid uri to connect to the postgres+psycopg database.

class calibpipe.database.connections.CalibPipeDatabase(user: str, database: str, password: str, host: str = 'localhost', port: int | None = None, autocommit: bool = False)[source]#

Bases: SQLConnection

CalibPipeDatabase connection. For now SQLConnection (PostgreSQL+psycopg).

This class simply creates a valid URI from named parameters to create the particular instance of DB used for CalibPipe data and provides no additional interface.

A few built-in queries can be found in the module queries.

Attributes:
user: str

Username used to connect to the database.

database: str

Name of the database with which the connection must be established.

password: str

Password for the given user.

host: str, default=`localhost`

Database host.

port: Optional[int], default=None

Database port.

autocommit: bool, default=False

Tell if the modifications to the DB must be committed automatically when the connection closes. Default is False, in this case the commit() method has to be called explicitly.

__init__(user: str, database: str, password: str, host: str = 'localhost', port: int | None = None, autocommit: bool = False) → None[source]#

Initialize the database connection.

Submodules#

calibpipe.database.connections.calibpipe_database module#

CalibPipeDatabase class.

class calibpipe.database.connections.calibpipe_database.CalibPipeDatabase(user: str, database: str, password: str, host: str = 'localhost', port: int | None = None, autocommit: bool = False)[source]#

Bases: SQLConnection

CalibPipeDatabase connection. For now SQLConnection (PostgreSQL+psycopg).

This class simply creates a valid URI from named parameters to create the particular instance of DB used for CalibPipe data and provides no additional interface.

A few built-in queries can be found in the module queries.

Attributes:
user: str

Username used to connect to the database.

database: str

Name of the database with which the connection must be established.

password: str

Password for the given user.

host: str, default=`localhost`

Database host.

port: Optional[int], default=None

Database port.

autocommit: bool, default=False

Tell if the modifications to the DB must be committed automatically when the connection closes. Default is False, in this case the commit() method has to be called explicitly.

__init__(user: str, database: str, password: str, host: str = 'localhost', port: int | None = None, autocommit: bool = False) → None[source]#

Initialize the database connection.

calibpipe.database.connections.postgres_utils module#

Adapter for psycopg types and database uri.

calibpipe.database.connections.postgres_utils.get_postgres_uri(user: str, database: str, passwd: str, host: str = 'postgres', port: str | None = None) → str[source]#

Generate a valid uri to connect to the postgres+psycopg database.

class calibpipe.database.connections.postgres_utils.NPInt16Dumper(cls: type, context: AdaptContext | None = None)[source]#

Bases: _NPIntDumper

Numpy int16 dumper.

oid = 21#

The oid to pass to the server, if known.

class calibpipe.database.connections.postgres_utils.NPInt32Dumper(cls: type, context: AdaptContext | None = None)[source]#

Bases: _NPIntDumper

Numpy int32 dumper.

oid = 23#

The oid to pass to the server, if known.

class calibpipe.database.connections.postgres_utils.NPInt64Dumper(cls: type, context: AdaptContext | None = None)[source]#

Bases: _NPIntDumper

Numpy int64 dumper.

oid = 20#

The oid to pass to the server, if known.

class calibpipe.database.connections.postgres_utils.NPNumericDumper(cls: type, context: AdaptContext | None = None)[source]#

Bases: _NPIntDumper

Numpy numeric dumper.

oid = 1700#

The oid to pass to the server, if known.

calibpipe.database.connections.postgres_utils.adapt_psycopg() → None[source]#

Adapt numpy numerical types for psycopg3.

Note

Required for psycopg3 < 3.2. Until the pyscopg-3.2.0 is released, we borrow their code. See this PR for details

calibpipe.database.connections.sql_connection module#

Interface to connect to the calibration DB stored in a SQL DB.

class calibpipe.database.connections.sql_connection.SQLConnection(uri: str, autocommit: bool = False)[source]#

Bases: object

Interface to communicate with a SQL database.

Once an uri (str) has been generated, the connection can be open in a context to ensure proper closing (and commit if required):

uri: str = get_postgres_uri(user='api-owner', database='calibration')
with SQLConnection(uri=uri, autocommit=True) as connection:
    # e.g.
    # connection.execute(...)
Attributes:
autocommit: bool

Tell if the database changes must be committed automatically when closing the connection (can be done manually by calling the commit() method).

uri: str

Uri used to connect to the database. This attribute is not used anymore once the connection is open.

engine: sqlalchemy.engine.Engine

Engine used for the database connection. It can be of several kinds, the default one is postgres + psycopg. The engine is automatically connected at the initialization step.

session: sqlalchemy.orm.Session

Session (use the engine) used to execute queries to the database.

__init__(uri: str, autocommit: bool = False) → None[source]#

Initialize the session and engine, connect to the database.

Parameters:
uri: str

uri to connect to the database. See the calibpipe.database.connections.get_postgres_uri() to generate the uri connecting to a Postgres database.

autocommit: bool, optional (default=False)

Determines if the connection commits changes when the __exit__() method is called. If set to False (default), changes will not be committed and it is necessary to call commit() after modifications have been done.

close() → None[source]#

Close the session.

If the autocommit attribute is True, changes are committed before closing the connection.

commit() → None[source]#

Commit changes to the database.

execute(*args) → Result[source]#

Execute a query in the SQL session.

This methods forwards the arguments to the sqlalchemy.orm.Session.execute() method of session. Refer to the documentation of sqlalchemy to use queries.

calibpipe.database.connections.user_confirmation module#

Function to ask for a user confirmation.

calibpipe.database.connections.user_confirmation.get_user_confirmation(prompt: str) → bool[source]#

Ask a confirmation from the user by displaying a prompt and asking yes or no.

Parameters:
prompt: str

Prompt to display

Returns:
bool

Answer from the user