def increase_row_integer( table_name: str, set_field: str, where_field: str, where_value, ): """Increase the integer in the row specified by the where fields. Given a primary key, a field set_field, and a pair (where_field, where_value), it increases the field in the appropriate row :param table_name: Primary key to detect workflow :param set_field: name of the field to be increased :param where_field: Field used to filter the row in the table :param where_value: Value of the previous field to filter the row :return: The table in the workflow pointed by PK is modified. """ query = sql.SQL('UPDATE {0} SET {1} = {1} + 1 WHERE {2} = %s').format( sql.Identifier(table_name), OnTaskDBIdentifier(set_field), OnTaskDBIdentifier(where_field), sql.Literal(where_value)) # Execute the query with connection.connection.cursor() as cursor: cursor.execute(query, [where_value]) connection.commit()
def not_equal(node, eval_type, given_variables): """Process the not equal operator. :param node: Formula node :param eval_type: Type of evaluation :param given_variables: Dictionary of var/values :return: Boolean result, SQL query, or text result """ constant = GET_CONSTANT.get(node['type'])(node['value']) if eval_type == EVAL_EXP: # Python evaluation varvalue = get_value(node, given_variables) return (not value_is_null(varvalue)) and varvalue != constant if eval_type == EVAL_SQL: # SQL evaluation query = sql.SQL('({0} != {1}) OR ({0} is null)').format( OnTaskDBIdentifier(node['field']), sql.Placeholder(), ) fields = [str(constant)] return query, fields # Text evaluation return '{0} ≠ {1} and not empty'.format( node['field'], constant, )
def is_not_null(node, eval_type, given_variables): """Process the is_not_null operator. :param node: Formula node :param eval_type: Type of evaluation :param given_variables: Dictionary of var/values :return: Boolean result, SQL query, or text result """ if eval_type == EVAL_EXP: # Python evaluation return not value_is_null(get_value(node, given_variables)) if eval_type == EVAL_SQL: # SQL evaluation query = sql.SQL('({0} is not null)').format( OnTaskDBIdentifier(node['field']), ) return query, [] # Text evaluation return '{0} is not null'.format(node['field'])
def not_ends_with(node, eval_type, given_variables): """Process the not_ends_width operator. :param node: Formula node :param eval_type: Type of evaluation :param given_variables: Dictionary of var/values :return: Boolean result, SQL query, or text result """ constant = GET_CONSTANT.get(node['type'])(node['value']) if eval_type == EVAL_EXP: # Python evaluation varvalue = get_value(node, given_variables) return ( (not value_is_null(varvalue)) and (not varvalue.endswith(constant)) ) if eval_type == EVAL_SQL: # SQL evaluation query = sql.SQL('({0} NOT LIKE {1}) OR ({0} is null)').format( OnTaskDBIdentifier(node['field']), sql.Placeholder(), ) fields = ['%' + node['value']] return query, fields # Text evaluation return '{0} does not end with {1}'.format(node['field'], constant)
def contains(node, eval_type, given_variables): """Process the contains operator. :param node: Formula node :param eval_type: Type of evaluation :param given_variables: Dictionary of var/values :return: Boolean result, SQL query, or text result """ constant = GET_CONSTANT.get(node['type'])(node['value']) if eval_type == EVAL_EXP: # Python evaluation varvalue = get_value(node, given_variables) return ( (not value_is_null(varvalue)) and varvalue.find(constant) != -1 ) if eval_type == EVAL_SQL: # SQL evaluation query = sql.SQL('({0} LIKE {1}) AND ({0} is not null)').format( OnTaskDBIdentifier(node['field']), sql.Placeholder(), ) fields = ['%' + node['value'] + '%'] return query, fields # Text evaluation return '{0} contains {1}'.format(node['field'], constant)
def insert_row( table_name: str, keys: List[str], values: List, ): """Insert a row with a set of pairs. Given a table and a list of (set_field, set_value) create a new row :param table_name: Table name :param keys: List of column names :param values: List of column values :return: Nothing. Effect reflected in the database. """ ncols = len(keys) query = sql.SQL('INSERT INTO {0} ({1}) VALUES ({2})').format( sql.Identifier(table_name), sql.SQL(', ').join([ OnTaskDBIdentifier(key) for key in keys ]), sql.SQL(', ').join([sql.Placeholder()] * ncols) ) # Execute the query with connection.connection.cursor() as cursor: cursor.execute(query, values)
def less_or_equal(node, eval_type, given_variables): """Process the less_or_equal operator. :param node: Formula node :param eval_type: Type of evaluation :param given_variables: Dictionary of var/values :return: Boolean result, SQL query, or text result """ constant = GET_CONSTANT.get(node['type'])(node['value']) if eval_type == EVAL_EXP: # Python evaluation varvalue = get_value(node, given_variables) if node['type'] in ('integer', 'double', 'datetime'): return (not value_is_null(varvalue)) and varvalue <= constant raise Exception( ugettext( 'Evaluation error: Type {0} not allowed ' + 'with operator LESS OR EQUAL', ).format(node['type']), ) if eval_type == EVAL_SQL: # SQL evaluation query = sql.SQL('({0} <= {1}) AND ({0} is not null)').format( OnTaskDBIdentifier(node['field']), sql.Placeholder(), ) fields = [str(constant)] return query, fields # Text evaluation return '{0} ⋜ {1} and not empty'.format( node['field'], constant, )
def is_not_empty(node, eval_type, given_variables): """Process the is_empty operator. :param node: Formula node :param eval_type: Type of evaluation :param given_variables: Dictionary of var/values :return: Boolean result, SQL query, or text result """ if eval_type == EVAL_EXP: # Python evaluation varvalue = get_value(node, given_variables) return (not value_is_null(varvalue)) and varvalue != '' if eval_type == EVAL_SQL: # SQL evaluation query = sql.SQL('({0} != \'\') AND ({0} is not null)').format( OnTaskDBIdentifier(node['field']), ) fields = [node['value']] return query, fields # Text evaluation return '{0} is not empty'.format(node['field'])
def get_select_query( table_name: str, column_names: Optional[List[str]] = None, filter_formula: Optional[Dict] = None, filter_pairs: Optional[Mapping] = None, ) -> Tuple[sql.Composed, List[Any]]: """Calculate pair query, fields to execute a select statement. :param table_name: Table to query :param column_names: list of columns to consider or None to consider all :param filter_formula: Text filter expression :param filter_pairs: Dictionary of key/value pairs. :return: (sql query, sql params) """ if column_names: query = sql.SQL('SELECT {0} FROM {1}').format( sql.SQL(', ').join( [OnTaskDBIdentifier(cname) for cname in column_names]), sql.Identifier(table_name), ) else: query = sql.SQL('SELECT * FROM {0}').format(sql.Identifier(table_name)) query_fields = [] if filter_formula or filter_pairs: bool_clause, query_fields = get_boolean_clause( filter_formula=filter_formula, filter_pairs=filter_pairs, ) if bool_clause: query = query + sql.SQL(' WHERE ') + bool_clause return query, query_fields
def update_row( table_name: str, keys: List[str], values: List, filter_dict: Optional[Mapping] = None, ): """Update table row with key,values in a row determined by a filter. Given a table, a list of (set_field, set_value), and pairs (where_field, where_value), it updates the row in the table selected with the list of ( where field = where value) with the values in the assignments in the list of (set_fields, set_values) :param table_name: Table name :param keys: List of column names :param values: List of column values :param filter_dict: Dictionary of key, value to select the row :return: """ query = sql.SQL('UPDATE {0} SET ').format( sql.Identifier(table_name), ) + sql.SQL(', ').join([ sql.SQL('{0} = {1}').format(OnTaskDBIdentifier(key), sql.Placeholder()) for key in keys ]) query_fields = values if filter_dict: query = query + sql.SQL(' WHERE ') query = query + sql.SQL(' AND ').join([ sql.SQL('{0} = {1}').format( OnTaskDBIdentifier(key), sql.Placeholder()) for key in filter_dict.keys() ]) query_fields += [lit_val for lit_val in filter_dict.values()] # Execute the query with connection.connection.cursor() as cursor: cursor.execute(query, query_fields)
def not_between(node, eval_type, given_variables): """Process the not_between operator. :param node: Formula node :param eval_type: Type of evaluation :param given_variables: Dictionary of var/values :return: Boolean result, SQL query, or text result """ if eval_type == EVAL_EXP: # Python evaluation varvalue = get_value(node, given_variables) if value_is_null(varvalue): return False if node['type'] not in ('integer', 'double', 'datetime'): raise Exception( ugettext( 'Evaluation error: Type {0} not allowed ' + 'with operator BETWEEN', ).format(node['type']), ) left = GET_CONSTANT[node['type']](node['value'][0]) right = GET_CONSTANT[node['type']](node['value'][1]) return not left <= varvalue <= right if eval_type == EVAL_SQL: # SQL evaluation query = sql.SQL( '({0} NOT BETWEEN {1} AND {2}) AND ({0} is not null)', ).format( OnTaskDBIdentifier(node['field']), sql.Placeholder(), sql.Placeholder(), ) fields = [str(number) for number in node['value']] return query, fields # Text evaluation return '{0} < {1} or {0} > {2} or {0} is empty'.format( node['field'], str(node['value'][0]), str(node['value'][1]), )
def is_column_unique(table_name: str, column_name: str) -> bool: """Return if a table column has all non-empty unique values. :param table_name: table :param column_name: column :return: Boolean (is unique) """ query = sql.SQL('SELECT COUNT(DISTINCT {0}) = count(*) from {1}').format( OnTaskDBIdentifier(column_name), sql.Identifier(table_name), ) # Get the result with connection.connection.cursor() as cursor: cursor.execute(query, []) return cursor.fetchone()[0]
def get_text_column_hash(table_name: str, column_name: str) -> str: """Calculate and return the MD5 hash of a text column. :param table_name: table to use :param column_name: column to pull the values :return: MD5 hash of the concatenation of the column values """ query = sql.SQL('SELECT MD5(STRING_AGG({0}, {1})) FROM {2}').format( OnTaskDBIdentifier(column_name), sql.Literal(''), sql.Identifier(table_name), ) with connection.connection.cursor() as cursor: cursor.execute(query) return cursor.fetchone()[0]
def get_boolean_clause( filter_formula: Optional[Dict] = None, filter_pairs: Optional[Mapping] = None, conjunction: bool = True, ) -> Tuple[sql.Composed, List]: """Create the boolean clause based on a formula and a list of pairs. Create the SQL boolean clause to be added to a query by combining a formula and a dictionary with key:value pairs. Both of them are optional and are combined through conjunction/disjunction depending on the conjunction variable. :param filter_formula: Boolean formula :param filter_pairs: Dictionary of key/value pairs. :param conjunction: Boolean stating if the clauses need to be in a conjunction. :return: SQL clause and list of fields. """ clause = None clause_fields = [] if filter_formula: # There is a filter clause, clause_fields = evaluate_formula(filter_formula, EVAL_SQL) if filter_pairs: c_txt = ' AND ' if conjunction else ' OR ' pairs_clause = sql.SQL(c_txt).join([ sql.SQL('{0} = {1}').format(OnTaskDBIdentifier(key), sql.Placeholder()) for key, __ in filter_pairs.items() ]) pairs_fields = [lit_val for __, lit_val in filter_pairs.items()] if clause: clause = clause + sql.SQL(' AND ') + pairs_clause clause_fields += pairs_fields else: clause = pairs_clause clause_fields = pairs_fields return clause, clause_fields
def search_table( table_name: str, search_value: str, columns_to_search: Optional[List] = None, filter_formula: Optional[Dict] = None, any_join: bool = True, order_col_name: str = None, order_asc: bool = True, ): """Search the content of all cells in the table. Select rows where for every (column, value) pair, column contains value ( as in LIKE %value%, these are combined with OR if any is TRUE, or AND if any is false, and the result is ordered by the given column and type (if given) :param table_name: table name :param filter_formula: Optional filter condition to pre filter the query :param columns_to_search: A column, value, type tuple to search the value in the column set. the query is built with these terms as requirement AND the cv_tuples. :param any_join: Boolean encoding if values should be combined with OR (or AND) :param order_col_name: Order results by this column :param order_asc: Order results in ascending values (or descending) :param search_value: String to search :return: The resulting query set """ # Create the query if columns_to_search: query = sql.SQL('SELECT {0} FROM {1}').format( sql.SQL(', ').join([ OnTaskDBIdentifier(colname) for colname in columns_to_search ]), sql.Identifier(table_name), ) else: query = sql.SQL('SELECT * from {1}').format(sql.Identifier(table_name)) query_fields = [] where_clause = sql.SQL('') # Add filter part if present if filter_formula: filter_query, filter_fields = evaluate_formula(filter_formula, EVAL_SQL) if filter_query: where_clause = filter_query query_fields += filter_fields # Add the CAST {0} AS TEXT LIKE ... if search_value: if where_clause != sql.SQL(''): where_clause = where_clause + sql.SQL(' AND ') # Combine the search subqueries if any_join: conn_txt = ' OR ' else: conn_txt = ' AND ' where_clause = where_clause + sql.SQL(conn_txt).join([ sql.SQL('(CAST ({0} AS TEXT) LIKE %s)').format( OnTaskDBIdentifier(cname), ) for cname in columns_to_search ]) query_fields += ['%' + search_value + '%'] * len(columns_to_search) if where_clause != sql.SQL(''): query = query + sql.SQL(' WHERE') + where_clause # Add the order if needed if order_col_name: query = query + sql.SQL(' ORDER BY {0}').format( OnTaskDBIdentifier(order_col_name)) if not order_asc: query = query + sql.SQL(' DESC') # Execute the query with connection.connection.cursor() as cursor: cursor.execute(query, query_fields) search_result = cursor.fetchall() return search_result