The DatabaseManager

DatabaseManager is the base class that defines the interface of how to interact with the database and provides the methods to do so. Each SQL dialect will inherit from the DatabaseManager and define the specific details of how to connect to the database and create the database engine. The database engine is the core component that allows for connection and interaction with the database. The engine is created through the sqlalchemy.create_engine() function. The creation of the connection URL needed to create the engine is handled during the initialization of DatabaseManager. In cases where a subclass of DatabaseManager for the desired SQL dialect does not exist this class can be used on its own (starting in version 1.2.0) but with limited functionality. Some methods that require dialect specific SQL statements such as merge_df() will not be available. Using DatabaseManager on its own also requires initialization through a SQLAlchemy URL or Engine, which require some knowledge about SQLAlchemy.

SQL dialects

This section describes the available SQL dialects in Pandemy and the dialects planned for future releases.

  • SQLite: SQLiteDb.

  • Oracle: OracleDb (New in version 1.1.0).

  • Microsoft SQL Server: Planned.

Core functionality

All SQL dialects inherit these methods from DatabaseManager:

New in version 1.2.0: merge_df() and upsert_table()

Examples of using these methods are shown in the SQLite and Oracle sections, but they work the same regardless of the SQL dialect used.

The SQLContainer

When initializing a subclass of DatabaseManager it can optionally be passed a SQLContainer class to the container parameter. The purpose of the SQLContainer is to store SQL statements used by an application in one place where they can be easily accessed by the DatabaseManager. Just like the DatabaseManager the SQLContainer should be subclassed and not used directly. If your application supports multiple SQL databases you can write the SQL statements the application needs in each SQL dialect and store the statements in one SQLContainer per dialect. Examples of using the SQLContainer with the SQLite DatabaseManager SQLiteDb are shown in section Using the SQLContainer.