db

db#

Description#

Database plugin. It allows you to programmatically select, insert, update and delete records on a database backend through requests, procedures and event hooks.

param args:

Extra arguments that will be passed to sqlalchemy.create_engine (see https://docs.sqlalchemy.org/en/latest/core/engines.html)

param kwargs:

Extra kwargs that will be passed to sqlalchemy.create_engine (see https:///docs.sqlalchemy.org/en/latest/core/engines.html)

Configuration#

db:
  # [Optional]
  # Default SQLAlchemy connection engine string (e.g.
  # ``sqlite:///:memory:`` or ``mysql://user:pass@localhost/test``)
  # that will be used. You can override the default engine in the db
  # actions.
  # engine:

Actions#

Module reference#

class platypush.plugins.db.DbPlugin(engine=None, *args, **kwargs)[source]#

Bases: Plugin

Database plugin. It allows you to programmatically select, insert, update and delete records on a database backend through requests, procedures and event hooks.

__init__(engine=None, *args, **kwargs)[source]#
Parameters:
delete(table, records, engine=None, *args, **kwargs)[source]#

Deletes records from a table.

Parameters:

Example:

Request:

{
    "type": "request",
    "target": "your_host",
    "action": "db.delete",
    "args": {
        "table": "table",
        "engine": "sqlite:///:memory:",
        "records": [
            { "id": 1 },
            { "id": 2 }
        ]
    }
}
execute(statement, *args, engine=None, **kwargs)[source]#

Executes a raw SQL statement.

Warning

Avoid calling this method directly if possible. Use insert, update and delete methods instead if possible. Don’t use this method if you need to select records, use the select method instead, as this method is mostly meant to execute raw SQL without returning anything.

Parameters:
insert(table, records, *args, engine=None, key_columns=None, on_duplicate_update=False, **kwargs)[source]#

Inserts records (as a list of hashes) into a table.

Parameters:

  • table (str) – Table name

  • records (list) – Records to be inserted (as a list of hashes)

  • engine (str) – Engine to be used (default: default class engine)

  • key_columns (list) – Set it to specify the names of the key columns for table. Set it if you want your statement to be executed with the on_duplicate_update flag.

  • on_duplicate_update (bool) – If set, update the records in case of duplicate rows (default: False). If set, you’ll need to specify key_columns as well. If key_columns is set, existing records are found but on_duplicate_update is false, then existing records will be ignored.

  • args – Extra arguments that will be passed to sqlalchemy.create_engine (see https://docs.sqlalchemy.org/en/latest/core/engines.html)

  • kwargs – Extra kwargs that will be passed to sqlalchemy.create_engine (see https:///docs.sqlalchemy.org/en/latest/core/engines.html)

Returns:

The inserted records, if the underlying engine supports the RETURNING statement, otherwise nothing.

Example:

Request:

{
    "type": "request",
    "target": "your_host",
    "action": "db.insert",
    "args": {
        "table": "table",
        "engine": "sqlite:///:memory:",
        "records": [
            {
                "id": 1,
                "name": foo
            },

            {
                "id": 2,
                "name": bar
            }
        ]
    }
}
select(query=None, table=None, filter=None, engine=None, data: dict | None = None, *args, **kwargs)[source]#

Returns rows (as a list of hashes) given a query.

Parameters:

  • query (str) – SQL to be executed

  • filter (dict) – Query WHERE filter expressed as a dictionary. This approach is preferred over specifying raw SQL in query as the latter approach may be prone to SQL injection, unless you need to build some complex SQL logic.

  • table (str) – If you specified a filter instead of a raw query, you’ll have to specify the target table

  • engine (str) – Engine to be used (default: default class engine)

  • data – If query is an SQL string, then you can use SQLAlchemy’s placeholders mechanism. You can specify placeholders in the query for values that you want to be safely serialized, and their values can be specified on the data attribute in a name ➡️ value mapping format.

  • args – Extra arguments that will be passed to sqlalchemy.create_engine (see https://docs.sqlalchemy.org/en/latest/core/engines.html)

  • kwargs – Extra kwargs that will be passed to sqlalchemy.create_engine (see https:///docs.sqlalchemy.org/en/latest/core/engines.html)

Returns:

List of hashes representing the result rows.

Examples:

Request:

{
    "type": "request",
    "target": "your_host",
    "action": "db.select",
    "args": {
        "engine": "sqlite:///:memory:",
        "query": "SELECT id, name FROM table WHERE name = :name",
        "data": {
            "name": "foobar"
        }
    }
}

or:

{
    "type": "request",
    "target": "your_host",
    "action": "db.select",
    "args": {
        "engine": "sqlite:///:memory:",
        "table": "table",
        "filter": {"id": 1}
    }
}

Response:

[
    {
        "id": 1,
        "name": foo
    }
]
update(table, records, key_columns, engine=None, *args, **kwargs)[source]#

Updates records on a table.

Parameters:
Returns:

The inserted records, if the underlying engine supports the RETURNING statement, otherwise nothing.

Example:

Request:

{
    "type": "request",
    "target": "your_host",
    "action": "db.update",
    "args": {
        "table": "table",
        "engine": "sqlite:///:memory:",
        "key_columns": ["id"],
        "records": [
            {
                "id": 1,
                "name": foo
            },

            {
                "id": 2,
                "name": bar
            }
        ]
    }
}