laboro-database 0.0.7

Laboro Database module

---

gitea: none include_toc: true

laboro_database

This is the Laboro database module. Its purpose is to provide a simplified interface to all major relational database types.

It supports:

• PostgreSQL
• SQLite3
• MySQL

This module is intended to be used by the Laboro is a NO-Code / Low-Code workflow manager.

Classes

Public classes provided by this package:

• Class: Database

  Module: laboro_database

  Package: laboro_database

  Purpose: The laboro_database.Database class provides a simplified interface to all major relational database types.

  Arguments:

  Name	Type	Required	Implies	Exclude	Secret	Default
  username	str	False		- service	True	None
  password	str	False		- service	True	None
  host	str	False		- service	False	None
  port	int	False		- service	True	None
  database	str	False		- service	False	None
  service	str	False		- host - port - username - password - database	False	None
  db_type	str	True			False

  Notes:

  • The db_type argument must be one of pgsql, sqlite or mysql.
  • The service argument is used for Postgesql services can be used only with db_type set to pgsql (see PotsgreSQL connection service file for further details).
  • When db_type is set to sqlite:
    • username, password, host, port and service are not used
    • the database argument must be the full file path to the Sqlite3 database.

  Instantiation examples:

  • Postgresql

    packages:
    - laboro_database
    instances:
      - name: management_database
        module: laboro_database
        class: Database
        args:
          username: $crypt$gAAAAABi6IOvYj8lRMA76rCafoRBwjUk1SyHdCI0avhUyGsSUMHWhQw--nmJZSabdiKoQW4Yb4il6xtpB1jGdGqaNwmP0Lfn4A==
          password: $crypt$gAAAAABi6IOcLbBd1eAENaz8akV7yOagHxKyxlDATAkgcLHucD-uEHaZm-P7Ox1EN-6pkbEjm5lDFbkd860-kwgo-qT_qjM1QA==
          host: pgsql.example.com
          port: 5432
          database: management
          db_type: pgsql
    

  • Sqlite3

    packages:
    - laboro_database
    instances:
      - name: users_database
        module: laboro_database
        class: Database
        args:
          database: /opt/laboro/workspaces/my_workflow/users.db
          db_type: sqlite
    

  Methods provided by class laboro_database.Database :

  • Name: query

    Purpose: This method allow to query the database.

    Arguments:

    Name	Type	Required	Implies	Exclude	Secret	Default
    request	str	True			False
    params	dict	False			False
    commit	bool	False			False
    verbose	bool	False			False	False
    exit_on_error	bool	False			False	True

    Notes:

    • The request argument is a string that support variable substitution with named parameters. The variable substitution syntax depends on the chosen db_type.
    • The params dictionary may contain more parameters than needed but must at least contain all needed parameters to fullfil the request string.
    • The commit argument should be set to True when the specified request insert, update, delete database records.

    Method call examples:

    • Postgresql

      actions:
        instances:
          - name: users_database
            module: laboro_database
            class: Database
            args:
              service: users_database
              db_type: pgsql
      methods:
        - instance: users_database
          name: query
          args:
            request: select lastname, givenname, password from managers where role = %(role)s
            params:
              role: admin
          output: admins_info
      

    • Sqlite3

      actions:
        instances:
          - name: users_database
            module: laboro_database
            class: Database
            args:
              database: /opt/laboro/workspaces/my_workflow/users.db
              db_type: sqlite
      methods:
        - instance: users_database
          name: query
          args:
            request: select lastname, givenname, password from managers where role = :role
            params:
              role: admin
          output: admins_info
      

    • Mysql

      actions:
        instances:
          - name: users_database
            module: laboro_database
            class: Database
            args:
              username: $crypt$gAAAAABi6IOvYj8lRMA76rCafoRBwjUk1SyHdCI0avhUyGsSUMHWhQw--nmJZSabdiKoQW4Yb4il6xtpB1jGdGqaNwmP0Lfn4A==
              password:  $crypt$gAAAAABi6IOcLbBd1eAENaz8akV7yOagHxKyxlDATAkgcLHucD-uEHaZm-P7Ox1EN-6pkbEjm5lDFbkd860-kwgo-qT_qjM1QA==
              host: mysql.example.com
              port: 3306
              database: users
              db_type: mysql
      methods:
        - instance: users_database
          name: query
          args:
            request: select lastname, givenname, password from managers where role = %(role)s
            params:
              role: admin
          output: admins_info
      

  • Name: multi_query

    Purpose: This method allow to execute a query multiple times with different parameters.

    Arguments:

    Name	Type	Required	Implies	Exclude	Secret	Default
    request	str	True			False
    paramlist	list	False			False
    commit	bool	False			False
    verbose	bool	False			False
    exit_on_error	bool	False			False

    Notes:

    • The request argument is a string that support variable substitution with named parameters. The variable substitution syntax depends on the chosen db_type.
    • The paramlist is a list of dictionaries. Each dictionary in the list will be used once at a time with the request string. Each dictionary may contain more parameters than needed but must at least contain all needed parameters to fullfil the request string.
    • The commit argument should be set to True when the specified request insert, update, delete database records. When set to True, a commit statement is sent to the database after each request.

    Method call examples:

    methods:
      - instance: users_database
        name: multi_query
        args:
          request: select lastname, givenname, password from managers where role = %(role)s
          paramlist:
            - role: admin
            - role: user
        output: admins_and_users_info
    

  • Name: transaction

    Purpose: Prepare and execute as a single transaction multiple queries on the database.

    Arguments:

    Name	Type	Required	Implies	Exclude	Secret	Default
    requests	list	True			False
    verbose	bool	False			False
    exit_on_error	bool	False			False

    Notes:

    • The requests argument is a list of 2 keyed dictionaries specifying a single request string (query) and its parameters (params) as described at the Database.query method.
    • If one of the queries defined in the requests argument fail, all requests within the transaction are roll backed.

    Method call examples:

    methods:
      - instance: users_database
        name: transaction
        args:
          requests:
            - query: select lastname, givenname, password from managers where role = %(role)s
              params:
                - role: admin
            - query: update role from managers set role = %(new_role)s where role = %(old_role)s
              params:
                - old_role: user
                - new_role: admin