def alter_topic_data_table(topic): topic_dict: dict = convert_to_dict(topic) if topic_dict.get("type") == "raw": pass else: topic_name = topic_dict.get('name') table_name = 'topic_' + topic_name ''' table = Table(table_name, metadata, extend_existing=True, autoload=True, autoload_with=engine) ''' table = get_topic_table_by_name(table_name) factors = topic_dict.get('factors') existed_cols = [] for col in table.columns: existed_cols.append(col.name) for factor in factors: factor_name = factor.get('name').lower() factor_type = get_datatype_by_factor_type(factor.get('type')) if factor_name in existed_cols: continue else: column = Column(factor_name, factor_type) column_name = column.compile(dialect=engine.dialect) column_type = column.type.compile(engine.dialect) stmt = 'ALTER TABLE %s ADD %s %s' % (table_name, column_name, column_type) with engine.connect() as conn: conn.execute(text(stmt))
def update_one(collection_name, query_dict, instance, base_model): table = get_table_model(collection_name) session = Session(engine, future=True) stmt = update(table) for key, value in query_dict.items(): stmt = stmt.where(eq(getattr(table, key), value)) instance_dict: dict = convert_to_dict(instance) values = {} for key, value in instance_dict.items(): if key != get_primary_key(collection_name): values[key] = value stmt = stmt.values(values) try: session.execute(stmt) session.commit() except: session.rollback() raise finally: session.close() return base_model.parse_obj(instance)
def pull_update(where, updates, model, name): results = find_(where, model, name) updates_dict = convert_to_dict(updates) for key, value in updates_dict.items(): for res in results: if isinstance(getattr(res, key), list): setattr(res, key, getattr(res, key).remove(value["in"][0])) update_one(res, model, name)
def create_raw_topic_data_table(topic): topic_dict: dict = convert_to_dict(topic) topic_name = topic_dict.get('name') table = Table('topic_' + topic_name.lower(), metadata) key = Column(name="id_", type_=String(60), primary_key=True) table.append_column(key) col = Column(name="data_", type_=CLOB, nullable=True) table.append_column(col) table.create(engine)
def topic_data_update_one(self, id_: str, one: any, topic_name: str): table_name = build_collection_name(topic_name) table = self.get_topic_table_by_name(table_name) stmt = update(table).where(eq(table.c['id_'], id_)) one_dict = capital_to_lower(convert_to_dict(one)) value = self.build_oracle_updates_expression(table, one_dict, "update") stmt = stmt.values(value) with self.engine.begin() as conn: result = conn.execute(stmt) return result.rowcount
def upsert(where, updates, model, name): table = get_table_model(name) instance_dict: dict = convert_to_dict(updates) stmt = insert(table) stmt = stmt.values(updates) stmt = stmt.on_duplicate_key_update(instance_dict) with engine.connect() as conn: conn.execute(stmt) conn.commit() return model.parse_obj(updates)
def build_topic_schema_model(instance): instance_dict: dict = convert_to_dict(instance) topic_schema = TopicSchema() topic_schema.topicId = instance_dict['topicId'] topic_schema.name = instance_dict['name'] topic_schema.code = instance_dict.get('code', None) topic_schema.description = instance_dict['description'] topic_schema._source = json.dumps(instance_dict, cls=DateTimeEncoder) topic_schema.createTime = datetime.utcnow() topic_schema.last_modified = datetime.utcnow() return topic_schema
def topic_data_insert_(self, data, topic_name): table_name = build_collection_name(topic_name) table = self.get_topic_table_by_name(table_name) values = [] for instance in data: one_dict: dict = capital_to_lower(convert_to_dict(instance)) value = self.build_oracle_updates_expression( table, one_dict, "insert") values.append(value) stmt = insert(table) with self.engine.connect() as conn: result = conn.execute(stmt, values)
def create_topic_table(instance): metadata = MetaData() instance_dict: dict = convert_to_dict(instance) topic_name = instance_dict.get('name') factors = instance_dict.get('factors') table = Table('topic_' + topic_name, metadata) key = Column(name="id", type_=DECIMAL(50), primary_key=True) table.append_column(key) for factor in factors: col = Column(name=factor.get('name'), type_=String(20), nullable=True) table.append_column(col) table.create(engine)
def insert_all(data, model, name): table = get_table_by_name(name) stmt = insert(table) value_list = [] for item in data: instance_dict: dict = convert_to_dict(item) values = {} for key in table.c.keys(): values[key] = instance_dict.get(key) value_list.append(values) with engine.connect() as conn: conn.execute(stmt, value_list)
def topic_data_update_one(self, id_: int, one: any, topic_name: str): table_name = 'topic_' + topic_name table = self.get_topic_table_by_name(table_name) stmt = self.build_stmt("update", table_name, table) stmt = stmt.where(eq(table.c['id_'], id_)) one_dict = convert_to_dict(one) values = self.build_mysql_updates_expression( table, capital_to_lower(one_dict), "update") stmt = stmt.values(values) with self.engine.begin() as conn: conn.execute(stmt)
def topic_find_one_and_update(where, updates, name): ''' table = Table('topic_' + name, metadata, extend_existing=True, autoload=True, autoload_with=engine) ''' table_name = 'topic_' + name table = get_topic_table_by_name(table_name) data_dict: dict = convert_to_dict(updates) select_for_update_stmt = select(table). \ with_for_update(nowait=False). \ where(build_oracle_where_expression(table, where)) # if "id_" not in updates: # updates["id_"] = get_surrogate_key() insert_stmt = insert(table).values( build_oracle_updates_expression_for_insert(table, data_dict)) update_stmt = update(table).where( build_oracle_where_expression(table, where)).values( build_oracle_updates_expression_for_update(table, data_dict)) select_new_stmt = select(table). \ where(build_oracle_where_expression(table, where)) with engine.connect() as conn: with conn.begin(): row = conn.execute(select_for_update_stmt).fetchone() if row is not None: conn.execute(update_stmt) else: conn.execute(insert_stmt) ''' with engine.connect() as conn: with conn.begin(): cursor = conn.execute(select_stmt).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) result = cursor.fetchone() if result is not None: conn.execute(update_stmt) else: conn.execute(insert_stmt) ''' with engine.connect() as conn: with conn.begin(): cursor = conn.execute(select_new_stmt).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) result = cursor.fetchone() return convert_dict_key(result, name)
def topic_data_insert_one(self, one, topic_name): table_name = build_collection_name(topic_name) table = self.get_topic_table_by_name(table_name) one_dict: dict = capital_to_lower(convert_to_dict(one)) value = self.build_oracle_updates_expression(table, one_dict, "insert") stmt = insert(table) with self.engine.connect() as conn: with conn.begin(): try: result = conn.execute(stmt, value) except IntegrityError as e: raise InsertConflictError("InsertConflict") return result.rowcount
def topic_data_update_one_with_version(self, id_: str, version_: int, one: any, topic_name: str): table_name = build_collection_name(topic_name) table = self.get_topic_table_by_name(table_name) stmt = update(table).where( and_(eq(table.c['id_'], id_), eq(table.c['version_'], version_))) one_dict = capital_to_lower(convert_to_dict(one)) value = self.build_oracle_updates_expression(table, one_dict, "update") stmt = stmt.values(value) with self.engine.begin() as conn: result = conn.execute(stmt) if result.rowcount == 0: raise OptimisticLockError("Optimistic lock error")
def insert_all(data, model, name): metadata = MetaData() table = Table(name, metadata, autoload=True, autoload_with=engine) stmt = insert(table) value_list = [] for item in data: instance_dict: dict = convert_to_dict(item) values = {} for key in table.c.keys(): values[key] = instance_dict.get(key) value_list.append(values) with engine.connect() as conn: result = conn.execute(stmt, value_list) conn.commit()
def topic_data_update_(self, query_dict, instances: list, topic_name): table_name = build_collection_name(topic_name) table = self.get_topic_table_by_name(table_name) stmt = (update(table).where( self.build_oracle_where_expression(table, query_dict))) values = [] for instance in instances: one_dict = capital_to_lower(convert_to_dict(instance)) value = self.build_oracle_updates_expression( table, one_dict, "update") values.append(value) stmt = stmt.values(values) with self.engine.begin() as conn: result = conn.execute(stmt)
def raw_topic_data_insert_one(one, topic_name): if topic_name == "raw_pipeline_monitor": raw_pipeline_monitor_insert_one(one, topic_name) else: ''' table = Table('topic_' + topic_name, metadata, extend_existing=True, autoload=True, autoload_with=engine) ''' table_name = 'topic_' + topic_name table = get_topic_table_by_name(table_name) one_dict: dict = convert_to_dict(one) value = {'id_': get_surrogate_key(), 'data_': dumps(one_dict)} stmt = insert(table) with engine.connect() as conn: conn.execute(stmt, value)
def topic_data_insert_(self, data, topic_name): table_name = f"topic_{topic_name}" table = self.get_topic_table_by_name(table_name) values = [] for instance in data: instance_dict: dict = convert_to_dict(instance) instance_dict['id_'] = get_int_surrogate_key() value = {} for key in table.c.keys(): value[key] = instance_dict.get(key) values.append(value) stmt = self.build_stmt("insert", table_name, table) with self.engine.connect() as conn: with conn.begin(): conn.execute(stmt, values)
def create(collection_name, instance, base_model): table_instance = get_table_model(collection_name)() instance_dict: dict = convert_to_dict(instance) for key, value in instance_dict.items(): setattr(table_instance, key, value) session = Session(engine, future=True) try: session.add(table_instance) session.commit() except: session.rollback() raise finally: session.close() return base_model.parse_obj(instance)
def update_topic_instance(topic_name, query_dict, instance): metadata = MetaData() table = Table('topic_' + topic_name, metadata, autoload=True, autoload_with=engine) stmt = (update(table).where(*build_where_expression(table, query_dict))) instance_dict: dict = convert_to_dict(instance) values = {} for key, value in instance_dict.items(): if key != 'id': values[key] = value stmt = stmt.values(values) with engine.begin() as conn: conn.execute(stmt)
def topic_data_update_one_with_version(self, id_: int, version_: int, one: any, topic_name: str): table_name = 'topic_' + topic_name table = self.get_topic_table_by_name(table_name) stmt = self.build_stmt("update", table_name, table) stmt = stmt.where( and_(eq(table.c['id_'], id_), eq(table.c['version_'], version_))) one_dict = convert_to_dict(one) one_dict['version_'] = version_ values = self.build_mysql_updates_expression( table, capital_to_lower(one_dict), "update") stmt = stmt.values(values) with self.engine.begin() as conn: result = conn.execute(stmt) if result.rowcount == 0: raise OptimisticLockError("Optimistic lock error")
def topic_data_update_(self, query_dict, instance, topic_name): table_name = 'topic_' + topic_name table = self.get_topic_table_by_name(table_name) stmt = self.build_stmt("update", table_name, table) stmt = (stmt.where(self.build_mysql_where_expression( table, query_dict))) instance_dict: dict = convert_to_dict(instance) values = {} for key, value in instance_dict.items(): if key != 'id_': if key.lower() in table.c.keys(): values[key.lower()] = value stmt = stmt.values(values) with self.engine.begin() as conn: # with conn.begin(): conn.execute(stmt)
def raw_topic_data_insert_(data, topic_name): ''' table = Table('topic_' + topic_name, metadata, extend_existing=True, autoload=True, autoload_with=engine) ''' table_name = 'topic_' + topic_name table = get_topic_table_by_name(table_name) values = [] for instance in data: instance_dict: dict = convert_to_dict(instance) value = {'id_': get_surrogate_key(), 'data_': dumps(instance_dict)} values.append(value) stmt = insert(table) with engine.connect() as conn: conn.execute(stmt, values)
def create_topic_data_table(topic): topic_dict: dict = convert_to_dict(topic) topic_type = topic_dict.get("type") if topic_type == "raw": create_raw_topic_data_table(topic) else: topic_name = topic_dict.get('name') factors = topic_dict.get('factors') table = Table('topic_' + topic_name.lower(), metadata) key = Column(name="id_", type_=String(60), primary_key=True) table.append_column(key) for factor in factors: name_ = factor.get('name').lower() type_ = get_datatype_by_factor_type(factor.get('type')) col = Column(name=name_, type_=type_, nullable=True) table.append_column(col) table.create(engine)
def upsert_(where, updates, model, name): table = get_table_by_name(name) instance_dict: dict = convert_to_dict(updates) select_stmt = select(func.count(1).label("count")). \ select_from(table). \ with_for_update(nowait=True). \ where(build_oracle_where_expression(where)) insert_stmt = insert(table).values(instance_dict) update_stmt = update(table).values(instance_dict) with engine.connect() as conn: with conn.begin(): row = conn.execute(select_stmt).fetchone() if row._mapping['count'] == 0: conn.execute(insert_stmt) if row._mapping['count'] == 1: conn.execute(update_stmt) return model.parse_obj(updates)
def insert_one(one, model, name): table = get_table_by_name(name) one_dict: dict = convert_to_dict(one) values = {} for key, value in one_dict.items(): if isinstance(table.c[key.lower()].type, CLOB): if value is not None: values[key.lower()] = dumps(value) else: values[key.lower()] = None else: values[key.lower()] = value stmt = insert(table).values(values) with engine.connect() as conn: conn.execute(stmt) # conn.commit() return model.parse_obj(one)
def insert_one(one, model, name): table = get_table_model(name) session = Session(engine, future=True) stmt = insert(table) instance_dict: dict = convert_to_dict(one) values = {} for key, value in instance_dict.items(): values[key] = value try: session.execute(stmt, values) session.commit() except: session.rollback() raise finally: session.close() return model.parse_obj(one)
def insert_topic_instances(topic_name, instances): metadata = MetaData() table = Table('topic_' + topic_name, metadata, autoload=True, autoload_with=engine) values = [] for instance in instances: instance_dict: dict = convert_to_dict(instance) value = {} for key in table.c.keys(): value[key] = instance_dict.get(key) values.append(value) stmt = insert(table) with engine.connect() as conn: result = conn.execute(stmt, values) conn.commit()
def topic_data_update_(topic_name, query_dict, instance): ''' table = Table('topic_' + topic_name, metadata, extend_existing=True, autoload=True, autoload_with=engine) ''' table_name = 'topic_' + topic_name table = get_topic_table_by_name(table_name) stmt = (update(table).where( build_oracle_where_expression(table, query_dict))) instance_dict: dict = convert_to_dict(instance) values = {} for key, value in instance_dict.items(): if key != 'id_': if key.lower() in table.c.keys(): values[key.lower()] = value stmt = stmt.values(values) with engine.begin() as conn: conn.execute(stmt)
def update_(where, updates, model, name): table = get_table_by_name(name) stmt = update(table) stmt = stmt.where(build_oracle_where_expression(table, where)) instance_dict: dict = convert_to_dict(updates) values = {} for key, value in instance_dict.items(): if key != get_primary_key(name): values[key] = value stmt = stmt.values(values) session = Session(engine, future=True) try: session.execute(stmt) session.commit() except: session.rollback() raise finally: session.close()