def create_upsert_statement(self, table, fields, constraint_fields, statement_string=False, coalesce=None): """returns update statement""" insert_fields, insert_statement = self.create_insert_statement( table, fields) update_statement = self._create_on_duplicate_key_update_statement( fields, constraint_fields, table=table, coalesce=coalesce, ) sql = SqlClause(phrases=[insert_statement, update_statement], phrase_indents=0, phrase_separator='\n') if statement_string: sql = sql.get_sql_clause() return insert_fields, sql
def _create_join_clauses(all_tables, all_join_keys): first = True join_clauses = list() for table, schema_join_key in zip(all_tables, all_join_keys): if not first: next_clause = SqlClause(header="INNER JOIN", phrases=["{}".format(table)]) join_clauses.append(next_clause) next_clause = SqlClause(header="ON", phrases=[ "{}.{} = {}.{}".format( previous_table, previous_schema_join_key, table, schema_join_key) ]) join_clauses.append(next_clause) else: next_clause = SqlClause(header="FROM", phrases=["{}".format(table)]) first = False previous_table = table previous_schema_join_key = schema_join_key join_clauses.append(next_clause) return join_clauses
def create_create_table_statement(cls, table, fields, primary_key=None, unique_keys=None, statement_string=False, if_not_exists=False): """returns create table statement""" schema_lines = ['{0} {1}'.format(field, datatype) for field, datatype in list(fields.items())] if primary_key: #primary key is list, but pass dictionary since create_constraints requires list of lists schema_lines.extend(cls.create_constraint_lines('primary', [['', primary_key]])) schema_lines.extend(cls.create_constraint_lines('unique', unique_keys)) if if_not_exists: create_table_header = 'CREATE TABLE IF NOT EXISTS {} (' else: create_table_header = 'CREATE TABLE {} (' if statement_string: return [], SqlClause(header=create_table_header.format(table), phrases=schema_lines, footer=')').get_sql_clause() else: return [], SqlClause(header=create_table_header.format(table), phrases=schema_lines, footer=')')
def create_drop_table_statement(table, statement_string=False): """stuff""" if statement_string: return [], SqlClause(header='DROP TABLE IF EXISTS {}'.format( table)).get_sql_clause() else: return [], SqlClause( header='DROP TABLE IF EXISTS {}'.format(table))
def create_truncate_table_statement(table, statement_string=False): """stuff""" sql_clause = SqlClause(header='TRUNCATE TABLE `{0}`'.format(table)) # Convert to string if statement_string: sql_clause = sql_clause.get_sql_clause() return [], sql_clause
def create_truncate_table_statement(table, statement_string=False): """stuff""" sql_clause = SqlClause(header='TRUNCATE TABLE `{0}`'.format(table)) # Convert to string if statement_string: sql_clause = sql_clause.get_sql_clause() return [], sql_clause
def create_composite_sql_table_delete_statement(self, component_schema_ids, join_key, row_deleted_field, time_cutoff_field=None, where_phrases=None): all_tables = list() all_join_keys = list() all_clauses = list() output_fields = list() if where_phrases is None: where_phrases = list() all_join_keys.append(join_key) for schema_id in component_schema_ids: component = self.component_schemas[schema_id] schema = component["config"] field_renames = component["field_renames"] reverse_field_renames = { value: key for key, value in list(field_renames.items()) } schema_join_key = reverse_field_renames.get(join_key, join_key) table = schema.table all_tables.append(table) all_join_keys.append(schema_join_key) delete_clause = SqlClause( header="DELETE", phrases=["{table}.*".format(table=self.table)]) all_clauses.append(delete_clause) all_clauses.extend( self._create_join_clauses([self.table] + all_tables, all_join_keys)) if row_deleted_field: where_phrases.extend([ "{}.{} = 1".format(table, row_deleted_field) for table in all_tables ]) where_fields, where_clauses = self._create_where_clauses( all_tables, time_cutoff_field, where_phrases) output_fields.extend(where_fields) all_clauses.extend(where_clauses) return output_fields, SqlClause(phrases=all_clauses, phrase_indents=0, phrase_separator="\n")
def create_upsert_statement(self, table, fields, statement_string=False): """returns update statement""" insert_fields, insert_statement = self.create_insert_statement(table, fields) update_statement = self._create_on_duplicate_key_update_statement(fields) sql = SqlClause(phrases=[insert_statement, update_statement], phrase_indents=0, phrase_separator='\n') if statement_string: sql = sql.get_sql_clause() return insert_fields, sql
def create_insert_statement(table, fields, statement_string=False): """returns insert statement""" insert_clause = SqlClause( header='INSERT INTO {} ('.format(table), footer=')', phrases=['`{0}`'.format(field) for field in fields]) insert_values = 'VALUES (' + ', '.join(['%s'] * len(fields)) + ')' if statement_string: return fields, SqlClause(phrases=[insert_clause, insert_values], phrase_indents=0, phrase_separator='\n').get_sql_clause() else: return fields, SqlClause(phrases=[insert_clause, insert_values], phrase_indents=0, phrase_separator='\n')
def create_update_statement(table, fields, where_fields=None, statement_string=False): """ returns update statement where_fields : list of dictionaries with format: dictionary {"field_name": "field1", "operator": "<"} """ update_field_phrases = [field + ' = %s' for field in fields] update_clause = SqlClause(header='UPDATE {}'.format(table)) update_values = SqlClause(header='SET', phrases=update_field_phrases) phrases = [update_clause, update_values] # Statement fields are fields + where_fields statement_fields = list(fields) if where_fields is not None: where_phrase = list() for field in where_fields: # Field values is an explicit override in a where clause, # used for more fine grained control of where_fields # Does not add a field to statement_fields. where_phrase.append(' '.join([ field['field_name'], field['operator'], field.get('field_value', '%s') ])) where_phrase = SqlClause(header='WHERE', phrases=where_phrase, phrase_separator=' AND\n') for field in where_fields: if field.get('field_value') is None: statement_fields.append(field["field_name"]) phrases.append(where_phrase) if statement_string: return statement_fields, SqlClause( phrases=phrases, phrase_indents=0, phrase_separator='\n').get_sql_clause() else: return statement_fields, SqlClause(phrases=phrases, phrase_indents=0, phrase_separator='\n')
def _create_on_duplicate_key_update_statement(cls, fields, coalesce=None): """returns update statement for upsert""" coalesce_string = cls.COALESCE_MAP[coalesce] sql_obj = SqlClause(header='ON DUPLICATE KEY UPDATE', phrases=[ '`{}` = '.format(field) + coalesce_string.format(field) for field in fields ]) return sql_obj
def _create_on_duplicate_key_update_statement(cls, fields, constraint_fields, coalesce=None, table=None): """returns update statement for upsert""" coalesce_string = cls.COALESCE_MAP[coalesce] header = ','.join(constraint_fields) header = 'ON CONFLICT (' + header + ') DO UPDATE SET ' sql_obj = SqlClause(header=header, phrases=[ '{} = '.format(field) + coalesce_string.format(field, table=table) for field in fields ]) return sql_obj
def _create_where_clauses(all_tables, time_cutoff_field, where_phrases): where_clauses = list() output_fields = list() if time_cutoff_field: greatest_list = [ table + "." + time_cutoff_field for table in all_tables ] greatest_list.append("0") where_phrases.append("GREATEST({greatest_phrase}) >= (%s)".format( greatest_phrase=", ".join(greatest_list))) output_fields.append(time_cutoff_field) if where_phrases: where_clause = SqlClause( header="WHERE", phrases=["({})".format(phrase) for phrase in where_phrases], phrase_separator=' AND\n') where_clauses.append(where_clause) return output_fields, where_clauses
def create_composite_sql_table_update_statement(self, component_schema_ids, join_key, time_cutoff_field=None, where_phrases=None): all_fields = list() all_mapped_fields = list() all_tables = list() all_join_keys = list() all_clauses = list() output_fields = list() if where_phrases is None: where_phrases = list() for schema_id in component_schema_ids: component = self.component_schemas[schema_id] schema = component["config"] ignored_fields = set(component["ignored_fields"]) field_renames = component["field_renames"] table = schema.table # NOTE we dont update the schema_join_key reverse_field_renames = { value: key for key, value in list(field_renames.items()) } schema_join_key = reverse_field_renames.get(join_key, join_key) fields, mapped_fields = list( zip(*[("{}.{}".format(table, field), field_renames.get(field, field)) for field in schema.fields if field not in ignored_fields and field != schema_join_key])) all_fields.extend(fields) all_mapped_fields.extend(mapped_fields) all_tables.append(table) all_join_keys.append(schema_join_key) update_clause = SqlClause( header="UPDATE {table}".format(table=self.table), phrases=all_mapped_fields) all_clauses.append(update_clause) for table, schema_join_key in zip(all_tables, all_join_keys): next_clause = SqlClause(header="INNER JOIN", phrases=["{}".format(table)]) all_clauses.append(next_clause) next_clause = SqlClause(header="ON", phrases=[ "{}.{} = {}.{}".format( self.table, join_key, table, schema_join_key) ]) all_clauses.append(next_clause) set_clause = SqlClause( header="SET", phrases=[ "{}.{} = VALUES({})".format(self.table, mapped_field, field) for field, mapped_field in zip(all_fields, all_mapped_fields) ]) all_clauses.append(set_clause) where_fields, where_clauses = self._create_where_clauses( all_tables, time_cutoff_field, where_phrases) output_fields.extend(where_fields) all_clauses.extend(where_clauses) return output_fields, SqlClause(phrases=all_clauses, phrase_indents=0, phrase_separator="\n")
def create_composite_sql_table_upsert_statement(self, component_schema_ids, join_key, time_cutoff_field=None, where_phrases=None): all_fields = list() all_mapped_fields = list() all_tables = list() all_join_keys = list() all_clauses = list() output_fields = list() if where_phrases is None: where_phrases = list() for schema_id in component_schema_ids: component = self.component_schemas[schema_id] schema = component["config"] ignored_fields = set(component["ignored_fields"]) field_renames = component["field_renames"] reverse_field_renames = { value: key for key, value in list(field_renames.items()) } schema_join_key = reverse_field_renames.get(join_key, join_key) table = schema.table # NOTE we ignore the join key in the fields table_fields, mapped_fields = list( zip(*[((table, field), field_renames.get(field, field)) for field in schema.fields if field not in ignored_fields and field != schema_join_key])) all_fields.extend(table_fields) all_mapped_fields.extend(mapped_fields) all_tables.append(table) all_join_keys.append(schema_join_key) # Add the join key to fields ONCE all_fields.append((table, schema_join_key)) all_mapped_fields.append(join_key) insert_clause = SqlClause( header="INSERT INTO {table} (".format(table=self.table), footer=')', phrases=all_mapped_fields) all_clauses.append(insert_clause) select_clause = SqlClause(header="SELECT", phrases=[ "{}.{}".format(table, field) for table, field in all_fields ]) all_clauses.append(select_clause) all_clauses.extend(self._create_join_clauses(all_tables, all_join_keys)) where_fields, where_clauses = self._create_where_clauses( all_tables, time_cutoff_field, where_phrases) output_fields.extend(where_fields) all_clauses.extend(where_clauses) duplicate_update_clause = SqlClause( header="ON DUPLICATE KEY UPDATE", phrases=[ "{} = VALUES({})".format(mapped_field, table_field[1]) for table_field, mapped_field in zip(all_fields, all_mapped_fields) ]) all_clauses.append(duplicate_update_clause) return output_fields, SqlClause(phrases=all_clauses, phrase_indents=0, phrase_separator="\n")