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)
