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()
