Getting Started

echo-postgres is an interface to easily interact with the performance PostgreSQL database (PerformanceDB). It was completely rewritten in version 2.0 to allow for much easier and concise usability. Now all the methods are contained in a single class called PerfDB and the user only needs to import this class to use the functionalities of the package.

A lot of the code implemented here is based on the approach used by the Python Reddit API Wrapper PRAW. It's a very good example of how to implement an API wrapper in Python and it's recommended to take a look at it if you want to understand how this package works.

Usage

To interact with PerformanceDB using echo-postgres you just need to create an instance of the PerfDB class, like shown in the example below.

from echo_postgres import PerfDB
perfdb = PerfDB()

Tip

Usually no arguments are requires, but you can define different host, user, password etc. at connection time. If they are not defined, the default values will be used.

Also, one important option is the application_name, which can come in handy when trying to relate the SQL sessions within pgAdmin to the application that is using the database. The name you define here will be shown in pgAdmin.

Structure

The PerfDB class is the only top level class in the package. It holds objects that make it easy to access different types of data stored in PerformanceDB. The structure of the class is shown below.

PerfDB
├───DataTypes
├───Attributes
├───DataSources
│  ├───Types
│  └───Instances
│     └───Attributes
├───Objects
│  ├───Types
│  ├───Models
│  │  └───Attributes
│  ├───Instances
│  │  └───Attributes
│  └───Groups
│     ├───Types
│     └───Instances
├───Alarms
│  ├───Definitions
│  └───History
├───Features
│  ├───Definitions
│  │  └───Attributes
│  └───Values
│     ├───Series
│     ├───Latest
│     ├───Aggregations
│     └───WhenAtCondition
├───CalcModels
│  ├───Types
│  └───Instances
│     └───Files
│        ├───Definitions
│        └───Values
├───Forecasts
│  ├───Models
│  │  └───Attributes
│  └───Features
│     ├───Definitions
│     └───Values
├───Ons
│  ├───Limitations
│  └───SPEs
│     └───SiteMapping
├───Ccee
│  ├───Submarkets
│  ├───PLD
│  ├───IPCA
│  ├───PPA
│  │  ├───Definitions
│  │  └───Values
│  └───TUST
├───KPIs
│  ├───Availability
│  │  ├───Categories
│  │  ├───Types
│  │  ├───Subtypes
│  │  │  └───Categories
│  │  ├───Amounts
│  │  ├───Forecasts
│  │  ├───Targets
│  │  └───Values
│  ├───StoppedAssets
│  ├───TrackerAvailability
│  │  ├───Types
│  │  ├───Amounts
│  │  └───Values
│  ├───Energy
│  │  ├───MeasurementPoints
│  │  ├───Targets
│  │  ├───Values
│  │  ├───Waterfall
│  │  └───Losses
│  │     ├───Types
│  │     ├───Values
│  │     └───Targets
│  ├───CapacityFactor
│  │  ├───Targets
│  │  └───Values
│  ├───Resource
│  │  ├───Types
│  │  └───Values
│  ├───EnergyPerformanceIndex
│  │  └───Values
│  └───PerformanceRatio
│     └───Values
├───BazefieldUsers
├───Users
│  ├───Instances
│  └───Roles
│     ├───Instances
│     └───Permissions
│        ├───Types
│        └───Values
├───Settings
├───Comments
│  └───Events
├───Labels
│  ├───Types
│  └───Values
├───Companies
├───Components
│  ├───Types
│  ├───Models
│  │  └───Attributes
│  ├───Instances
│  │  ├───Attributes
│  │  ├───History
│  │  └───Latest
│  └───SubComponents
│     ├───Types
│     ├───Models
│     │  └───Attributes
│     └───Instances
│        ├───Attributes
│        ├───History
│        └───Latest
├───RootCauses
├───Events
│  ├───Types
│  ├───Instances
│  │  ├───Comments
│  │  └───Labels
│  ├───DetectionTypes
│  └───ConfirmationTypes
├───Documents
│  ├───Types
│  └───Instances
│     ├───Events
│     └───Labels
├───RawData
│  ├───Definitions
│  └───Values
├───Vibration
│  ├───Frequencies
│  ├───TimeSeries
│  └───Spectrum
├───ResourceAssessments
│ ├───Types
│ ├───Instances
│ ├───Values
│ └───Losses
│    ├───Types
│    └───Values
└───Jobs
    ├───Types
    ├───Instances
    └───Steps
    └───States

Each object below PerfDB is an instance of it's own specific class, but all inherit from the base class PerfDbRoot. This class contains the methods that are common to all objects, and specially, defines the constructor that should not be overridden in the child classes. The constructor is responsible instantiating common attributes to all objects, such as the connection handler to the database.

General Guidelines

• Key naming: The package maintains the same names used in the database for consistency. This means that when returning a dictionary or a DataFrame, the keys will be the same as the column names in the database. This is done to make it easier to relate the data returned by the package to the data in the database.
• Default keys: Methods that return values such as dicts or DataFrames will always use the names of the objects as keys/index. This is done to make it easier to use the returned values in other methods. The ids of those objects will be returned in the dict values or DataFrame columns as they are of secondary usage (very important to the database itself but not to the user or applications interacting with it).
• Queries: All queries should be executed within a with statement like the one in example below. This is done to ensure that the connection to the database is closed after the query is executed, avoiding issues due to too many open connections.

# suppose we are in a class that is a children of PerfDbRoot
with self._perfdb.conn.reconnect() as conn:
    # example of reading to pandas
    df = conn.read_to_pandas(query)
    # example of executing a query
    conn.execute(query)

Common Methods

Note

Most of the classes will have at least some of the methods below to interact with the database. Regardless of that, it's important to note that most of the classes will just have some of them, as they are not all applicable to all classes.

• get_ids

  Method used to list all instances of the desired class. For example, if perfdb.objects.types.get_ids() is called the id's of the possible object types would be returned.

  For consistency, it's expected that all get_ids methods return a dict containing the names as keys and the ids as values.

  There might be cases where arguments are required to filter the results. In case there are multiple arguments to filter, an argument called filter_type with possible values of and and or will be used to define how the filters are combined. The default value is and.

• get

  Method used to get the values of the desired class. For example, if perfdb.objects.types.get() is called all the possible object types would be returned, but this would include getting more data related to it than just the ids and names. This way this method can be seen as an extension of the get_ids method.

  As the return value of this can vary depending on the considered class, it's expected that all get methods have an argument called output_type to define what is the format of the output (a dict or a DataFrame for example).

  Like in the get_ids method, there might be cases where arguments are required to filter the results. In case there are multiple arguments to filter, an argument called filter_type with possible values of and and or will be used to define how the filters are combined. The default value is and.

• insert

  Method used to insert a new instance of the desired class. For example, if perfdb.objects.types.insert() is called a new object type would be created in PerformanceDB.

  In most cases it should have an argument called on_conflict to define how conflicts are handled. It's possible values will be:

  • raise: Raises an exception and is the default value.
  • ignore: Ignores the error and continues with the execution.
  • update: Updates the existing instance with the new values.

• update

  Method used to update an existing instance of the desired class. For example, if perfdb.objects.types.update() is called an existing object type would be updated in PerformanceDB.

• delete

  Method used to delete an existing instance of the desired class. For example, if perfdb.objects.types.delete() is called an existing object type would be deleted in PerformanceDB.

• export_file

  Method used to export the data of the desired class. For example, if perfdb.objects.types.export_file() is called all the object types would be exported to the desired file type.

  This method will infer the file type from the file extension.

• import_file

  Method used to import data of the desired class. For example, if perfdb.objects.types.import_file() is called all the object types would be imported from the desired file type.

Reference

The scope of this documentation does't go as far as explaining how the data is structured in the database. More details on this can be found in the PerformanceDB documentation, which can be read in the best way using Obsidian.