Skip to content

Latest commit

 

History

History
67 lines (42 loc) · 4.04 KB

databasemanager.rst

File metadata and controls

67 lines (42 loc) · 4.04 KB
Raw

The DatabaseManager

DatabaseManager <pandemy.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 <pandemy.DatabaseManager> and define the specific details of how to connect to the database and create the database engine <sqlalchemy.engine.Engine>. The database engine <sqlalchemy.engine.Engine> is the core component that allows for connection and interaction with the database. The engine <sqlalchemy.engine.Engine> is created through the sqlalchemy.create_engine function. The creation of the connection URL needed to create the engine <sqlalchemy.engine.Engine> is handled during the initialization of DatabaseManager <pandemy.DatabaseManager>. In cases where a subclass of ~pandemy.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 ~pandemy.DatabaseManager.merge_df will not be available. Using ~pandemy.DatabaseManager on its own also requires initialization through a SQLAlchemy URL <sqlalchemy.engine.URL> or Engine <sqlalchemy.engine.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 <pandemy.SQLiteDb>.
  • Oracle: OracleDb <pandemy.OracleDb> (New in version 1.1.0).
  • Microsoft SQL Server: Planned.

Core functionality

All SQL dialects inherit these methods from DatabaseManager <pandemy.DatabaseManager>:

  • ~pandemy.DatabaseManager.delete_all_records_from_table: Delete all records from an existing table in the database.
  • ~pandemy.DatabaseManager.execute: Execute arbitrary SQL statements on the database.
  • ~pandemy.DatabaseManager.load_table: Load a table by name or SQL query into a pandas.DataFrame.
  • ~pandemy.DatabaseManager.manage_foreign_keys: Manage how the database handles foreign key constraints.
  • ~pandemy.DatabaseManager.merge_df: Merge data from a pandas.DataFrame into a table (~pandemy.OracleDb only).
  • ~pandemy.DatabaseManager.save_df: Save a pandas.DataFrame to a table in the database.
  • ~pandemy.DatabaseManager.upsert_table: Update a table with data from a pandas.DataFrame and insert new rows if any.

1.2.0 ~pandemy.DatabaseManager.merge_df and ~pandemy.DatabaseManager.upsert_table

Examples of using these methods are shown in the sqlite/index and oracle/index sections, but they work the same regardless of the SQL dialect used.

The SQLContainer

When initializing a subclass of DatabaseManager <pandemy.DatabaseManager> it can optionally be passed a SQLContainer <pandemy.SQLContainer> class to the container parameter. The purpose of the SQLContainer <pandemy.SQLContainer> is to store SQL statements used by an application in one place where they can be easily accessed by the DatabaseManager <pandemy.DatabaseManager>. Just like the DatabaseManager <pandemy.DatabaseManager> the SQLContainer <pandemy.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 <pandemy.SQLContainer> per dialect. Examples of using the SQLContainer <pandemy.SQLContainer> with the SQLite DatabaseManager SQLiteDb <pandemy.SQLiteDb> are shown in section user_guide/sqlite/sqlcontainer:Using the SQLContainer.