Db Sql

Base classes to wrap various SQL based databases in dffml.db abstraction.

class dffml.db.sql.SQLDatabaseContext(parent: BaseDatabase)

create_table_query(table_name: str, cols: Dict[str, str], *args, **kwargs) → None

Creates a create query. Table with name table_name will be created if it doesn’t exist.

Parameters:

• table_name (str) – Name of the table.

• cols (dict) – Mapping of column names to type of columns.

Returns:

query – CREATE query

Return type:

str

insert_query(table_name: str, data: Dict[str, Any], *args, **kwargs) → None

Creates insert query. Keys in data dict correspond to the columns in table_name.

Parameters:

• table_name (str) – Name of the table.

• data (dict, optional) – Columns names are keys, values are data to insert.

Returns:

• query (str) – INSERT query

• parameters (tuple) – Variables to bind

lookup_query(table_name: str, cols: Optional[List[str]] = None, conditions: Optional[Union[List[List[Condition]], List[List[Tuple[str]]]]] = None) → Tuple[str, Tuple[Any]]

Creates a query string and tuple of parameters used as bindings.

Parameters:

• table_name (str) – Name of the table.

• cols (list, optional) – Columns names to return

• conditions (Conditions, optional) – Nested array of conditions to satisfy, becomes WHERE.

Returns:

• query (str) – SELECT query

• parameters (tuple) – Variables to bind

classmethod make_condition_expression(conditions)

Returns a dict with keys ‘expression’,’values’ if conditions is not empty else returns None

example:

Input : conditions = [
    [["firstName", "=", "John"], ["lastName", "=", "Miles"]],
    [["age", "<", "38"]],
]

Output : {
    'expression':
        '((firstName = ? ) OR (lastName = ? )) AND ((age < ? ))',
    'values':
         ['John', 'Miles', '38']
    }

remove_query(table_name: str, conditions: Optional[Union[List[List[Condition]], List[List[Tuple[str]]]]] = None)

Creates a delete query to remove rows from table_name (satisfying conditions if provided).

Parameters:

• table_name (str) – Name of the table.

• conditions (Conditions, optional) – Nested array of conditions to satisfy, becomes WHERE.

Returns:

• query (str) – DELETE query

• parameters (tuple) – Variables to bind

update_query(table_name: str, data: Dict[str, Any], conditions: Optional[Union[List[List[Condition]], List[List[Tuple[str]]]]] = None) → None

Creates update query setting values of rows (satisfying conditions if provided) with data in table_name.

Parameters:

• table_name (str) – Name of the table.

• data (dict, optional) – Columns names to update mapped to value to set to.

• conditions (Conditions, optional) – Nested array of conditions to satisfy, becomes WHERE.

Returns:

• query (str) – UPDATE query

• parameters (tuple) – Variables to bind