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 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 drop_topic_data_table(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)
table.drop(engine)
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_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 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 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 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 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 topic_data_update_one(id_: str, one: any, topic_name: str):
'''
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 = update(table).where(eq(table.c['id_'], id_))
one_dict = convert_to_dict(one)
one_dict_lower = capital_to_lower(one_dict)
value = {}
for key in table.c.keys():
if key != 'id_':
value[key] = one_dict_lower.get(key)
stmt = stmt.values(value)
with engine.begin() as conn:
conn.execute(stmt)
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)
