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.
Core functionality
All SQL dialects inherit these methods from DatabaseManager
:
delete_all_records_from_table()
: Delete all records from an existing table in the database.execute()
: Execute arbitrary SQL statements on the database.load_table()
: Load a table by name or SQL query into apandas.DataFrame
.manage_foreign_keys()
: Manage how the database handles foreign key constraints.merge_df()
: Merge data from apandas.DataFrame
into a table (OracleDb
only).save_df()
: Save apandas.DataFrame
to a table in the database.upsert_table()
: Update a table with data from apandas.DataFrame
and insert new rows if any.
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.