Target¶

class bonobo_trans.target.DbTarget(*args, **kwargs)¶

The DbTarget transformation writes data to a SQL database.

Note

Database connectivity is provided by SQLAlchemy. The engine must be provided via a Bonobo Service, containing an ‘sqlalchemy.engine’.

Tip

From a Bonobo point of view this transformation is not a true “loader”, the tranformation allows (but does not require) transformations after it to keep processing the data.

Configuration options

Required:

table_name (str)

Optional:

operation (int)

upsert_strategy (int)

sql_insert (str or sqlalchemy.sql.insert) Override

sql_update (str or sqlalchemy.sql.update) Override

sql_delete (str or sqlalchemy.sql.delete) Override

sql_pre (str or sqlalchemy.sql) Pre-SQL

bp_update (dict)

bp_pre_sql (dict)

target_key (str or list of str) Override?

verbose_sql (bool) False

verbose_data (bool) False

truncate (bool) False

dry_run (bool) False

simulate (bool) False

Description of the options:

operation, upsert_strategy

operation can hold any of the following constants:

operation Description

TGT_OPER_DATA_DRIVEN Operation is determined by ‘__TARGET_OPERATION__’ field.

TGT_OPER_INSERT_ONLY Only insert into target table (default?)

TGT_OPER_UPDATE_ONLY Only update target table, based on target_key ? or an actual key ???????? TBD

TGT_OPER_UPSERT Update key in target, otherwise insert if key doesn’t exist yet.

TGT_OPER_DELETE Deletes by PK definition or by provided key. Target table must have a primary key, if no delete key specified.

TGT_OPER_REJECT Not implemented

If operation is TGT_OPER_UPSERT, the upsert_strategy option can be used to choose one of the following options, specifiying how to execute the upsert:

upsert_strategy Description

TGT_UPSERT_TRY_INSERT Try to insert, if fails try to update

TGT_UPSERT_EXISTS If exist update, else insert

TGT_UPSERT_EXISTS_SERIALIZABLE Same, but with: WITH(UPDLOCK) and SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

TGT_UPSERT_MERGE Upsert using merge statement

TGT_UPSERT_MERGE_SERIALIZABLE Same, but with: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

sql_insert, sql_update, sql_delete

Either provide a ‘raw’ SQL statement or a SQLAlchemy sql object.

The target SQL is normally generated based on the operation parameter. It is possible to provide a custom SQL statement via one of the three override parameters.

The provided override must still be in accordance with the operation in order to be executed. Would you, for example, provide a sql_insert override when the operation is TGT_OPER_DELETE, the sql_insert override would be ignored.

bp_select, bp_pre_sql

Bind parameters for the selection and pre-sql statements.

Attention

Bind parameters must be provided using a dictionary. The format for this is: {'parameter': 'value'}

The name of the parameter depends on the your wether you’re using a ‘raw’ SQL statement or an SQLAlchemy.sql object.

Raw SQL:

For ‘raw’ SQL, bound parameters are specified by name. For example: sql_pre = 'DELETE * FROM users WHERE id=:user_id' bp_select = { 'user_id':555 }

SQLAlchemy:

For an sqlalchemy.sql object, bound parameters can be specified in various different manners. Make sure the key in the dictionary matches. For example: sql_pre = delete().where(users.c.id == bindparam('user_id')) bp_select = { 'user_id':555 }

verbose_sql

If ‘verbose_sql’ is True, the generated SQL statement will be printed.

verbose_data

If ‘verbose_data’ is True, every outgoing row is printed.

truncate

Truncate target table before writing.

dry_run

Don’t actually write to target. Useful in combination with verbose_sql and/or verbose_data.

simulate

Not implemented. Possible future feature: copy target table into memory table to simulate a target load

Args:

d_row_in (dict)

d_row_in is a dictionary containing key for some or all of the target columns.

Additionally the following special keys may be added:

__TARGET_TABLE__ (str) Used in conjuction with “Data Driven” operation.

__TARGET_OPERATION__ (str) Override specified operation.

Returns:

d_row_out (dict)

Parameters:	engine (str) – table_name (str) – operation (int) – sql_insert – sql_update – sql_delete – sql_pre – bp_update (dict) – bp_pre_sql (dict) – name (str) – verbose_sql (bool) – verbose_data (bool) – truncate (bool) – dry_run (bool) – simulate (bool) –