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 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 topic_data_find_by_id(id_: str, topic_name: str) -> any: ''' table = Table('topic_' + topic_name, metadata, extend_existing=True, autoload=True, autoload_with=engine) ''' start_time = time.time() table_name = 'topic_' + topic_name table = get_topic_table_by_name(table_name) elapsed_time = time.time() - start_time # print("elapsed_time topic_data_update_one", elapsed_time) stmt = select(table).where(eq(table.c['id_'], id_)) with engine.connect() as conn: cursor = conn.execute(stmt).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) result = cursor.fetchone() if result is None: return None else: # return capital_to_lower(result) return convert_dict_key(result, topic_name)
def topic_data_page_(where, sort, pageable, model, name) -> DataPage: if name == "topic_raw_pipeline_monitor": return raw_pipeline_monitor_page_(where, sort, pageable, model, name) else: count = count_topic_data_table(name) table = get_topic_table_by_name(name) stmt = select(table).where(build_oracle_where_expression(table, where)) orders = build_oracle_order(table, sort) for order in orders: stmt = stmt.order_by(order) offset = pageable.pageSize * (pageable.pageNumber - 1) # stmt = stmt.offset(offset).limit(pageable.pageSize) stmt = text( str(stmt.compile(compile_kwargs={"literal_binds": True})) + " OFFSET :offset ROWS FETCH NEXT :maxnumrows ROWS ONLY") result = [] with engine.connect() as conn: cursor = conn.execute(stmt, { "offset": offset, "maxnumrows": pageable.pageSize }).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) res = cursor.fetchall() for row in res: if model is not None: result.append(parse_obj(model, row, table)) else: result.append(row) return build_data_pages(pageable, result, count)
def delete_(where, model, name): table = get_table_by_name(name) if where is None: stmt = delete(table) else: stmt = delete(table).where(build_oracle_where_expression(table, where)) with engine.connect() as conn: conn.execute(stmt)
def count_topic_data_table(table_name): stmt = 'SELECT count(%s) AS count FROM %s' % ('id_', table_name) with engine.connect() as conn: cursor = conn.execute(text(stmt)).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) result = cursor.fetchone() return result['COUNT']
def list_(where, model, name) -> list: table = get_table_by_name(name) stmt = select(table).where(build_oracle_where_expression(table, where)) with engine.connect() as conn: cursor = conn.execute(stmt).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) res = cursor.fetchall() result = [] for row in res: result.append(parse_obj(model, row, table)) return result
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 find_by_id(id_, model, name): table = get_table_by_name(name) primary_key = get_primary_key(name) stmt = select(table).where(eq(table.c[primary_key.lower()], id_)) with engine.connect() as conn: cursor = conn.execute(stmt).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) result = cursor.fetchone() if result is None: return else: return parse_obj(model, result, table)
def convert_dict_key(dict_info, topic_name): new_dict = {} stmt = "select t.factors from topics t where t.name=:topic_name" with engine.connect() as conn: cursor = conn.execute(stmt, {"topic_name": topic_name}).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) row = cursor.fetchone() factors = json.loads(row['FACTORS']) for factor in factors: new_dict[factor['name']] = dict_info[factor['name'].upper()] new_dict['id_'] = dict_info['ID_'] return new_dict
def topic_data_delete_(where, topic_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) if where is None: stmt = delete(table) else: stmt = delete(table).where(build_oracle_where_expression(table, where)) with engine.connect() as conn: conn.execute(stmt)
def topic_data_find_(where, topic_name): table_name = 'topic_' + topic_name table = get_topic_table_by_name(table_name) stmt = select(table).where(build_oracle_where_expression(table, where)) with engine.connect() as conn: cursor = conn.execute(stmt).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) result = cursor.fetchall() if result is None: return None else: # return capital_to_lower(result) return convert_dict_key(result, topic_name)
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 check_topic_type_is_raw(topic_name): table = get_table_by_name("topics") select_stmt = select(table).where( build_oracle_where_expression(table, {"name": topic_name})) with engine.connect() as conn: 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 None: raise else: if result['TYPE'] == "raw": return True else: return False
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 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 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 find_one(where, model, name): table = get_table_by_name(name) check_result = check_where_column_type(name, where) if check_result is not None: stmt = text(build_raw_sql_with_json_table(check_result, where, name)) else: stmt = select(table) stmt = stmt.where(build_oracle_where_expression(table, where)) with engine.connect() as conn: cursor = conn.execute(stmt).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) result = cursor.fetchone() if result is None: return else: return parse_obj(model, result, table)
def topic_data_find_one(where, topic_name) -> any: ''' 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 = select(table).where(build_oracle_where_expression(table, where)) with engine.connect() as conn: cursor = conn.execute(stmt).cursor columns = [col[0] for col in cursor.description] cursor.rowfactory = lambda *args: dict(zip(columns, args)) result = cursor.fetchone() if result is None: return None else: # return capital_to_lower(result) return convert_dict_key(result, topic_name)
def update_one(one, model, name) -> any: table = get_table_by_name(name) stmt = update(table) one_dict: dict = convert_to_dict(one) primary_key = get_primary_key(name) stmt = stmt.where( eq(table.c[primary_key.lower()], one_dict.get(primary_key))) 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 = stmt.values(values) with engine.connect() as conn: with conn.begin(): conn.execute(stmt) return model.parse_obj(one)
def topic_data_insert_one(one, topic_name): if check_topic_type_is_raw(topic_name): raw_topic_data_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) one_dict: dict = capital_to_lower(convert_to_dict(one)) value = {} for key in table.c.keys(): if key == "id_": value[key] = get_surrogate_key() else: value[key] = one_dict.get(key) stmt = insert(table) with engine.connect() as conn: conn.execute(stmt, value)
def raw_pipeline_monitor_insert_one(one, topic_name): table_name = 'topic_' + topic_name table = get_topic_table_by_name(table_name) one_dict: dict = convert_to_dict(one) one_lower_dict = capital_to_lower(one_dict) value = {} for key in table.c.keys(): if key == "id_": value[key] = get_surrogate_key() elif key == "data_": value[key] = dumps(one_dict) else: if isinstance(table.c[key].type, CLOB): if one_lower_dict.get(key) is not None: value[key] = dumps(one_lower_dict.get(key)) else: value[key] = None else: value[key] = one_lower_dict.get(key) stmt = insert(table) with engine.connect() as conn: conn.execute(stmt, value)
def topic_data_insert_(data, topic_name): if check_topic_type_is_raw(topic_name): raw_topic_data_insert_(data, topic_name) else: ''' table = Table('topic_' + topic_name, metadata, extend_existing=True, autoload=True, autoload_with=engine) ''' start_time = time.time() table_name = 'topic_' + topic_name table = get_topic_table_by_name(table_name) elapsed_time = time.time() - start_time values = [] for instance in data: 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: conn.execute(stmt, values)
def delete_by_id(id_, name): table = get_table_by_name(name) key = get_primary_key(name) stmt = delete(table).where(eq(table.c[key.lower()], id_)) with engine.connect() as conn: conn.execute(stmt)
def delete_one(where: dict, name: str): table = get_table_by_name(name) stmt = delete(table).where(build_oracle_where_expression(table, where)) with engine.connect() as conn: conn.execute(stmt)