platypush.plugins.db

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

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

Requires:
  • sqlalchemy (pip install sqlalchemy)
__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, engine=None, *args, **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, engine=None, key_columns=None, on_duplicate_update=False, *args, **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.
  • args – Extra arguments that will be passed to sqlalchemy.create_engine (see http://docs.sqlalchemy.org/en/latest/core/engines.html)
  • kwargs – Extra kwargs that will be passed to sqlalchemy.create_engine (see http://docs.sqlalchemy.org/en/latest/core/engines.html)

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, *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)
  • args – Extra arguments that will be passed to sqlalchemy.create_engine (see http://docs.sqlalchemy.org/en/latest/core/engines.html)
  • kwargs – Extra kwargs that will be passed to sqlalchemy.create_engine (see http://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"
    }
}

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:

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
            }
        ]
    }
}