def delete_one(id: str, name: str):
table = get_table_model(name)
key = get_primary_key(name)
stmt = delete(table).where(table.c.get(key) == id)
with engine.connect() as conn:
conn.execute(stmt)
conn.commit()
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 find_one(where, model, name):
table = get_table_model(name)
stmt = select(table)
stmt = stmt.where(build_where_expression(table, where))
with engine.connect() as conn:
result = conn.execute(stmt).first()
conn.commit()
return parse_obj(model, result[0])
def find_by_id(id, model, name):
table = get_table_model(name)
primary_key = get_primary_key(name)
stmt = select(table).where(eq(getattr(table, primary_key), id))
with engine.connect() as conn:
result = conn.execute(stmt)
conn.commit()
return parse_obj(model, result[0])
def find_one(collection_name, query_dict, base_model):
table = get_table_model(collection_name)
stmt = select(table)
for key in query_dict.keys():
value = query_dict[key]
stmt = stmt.where(eq(getattr(table, key), value))
session = Session(engine, future=True)
res = session.execute(stmt).first()
return parse_obj(base_model, res[0])
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 list_(where, model, name) -> list:
table = get_table_model(name)
stmt = select(table).where(where)
with engine.connect() as conn:
res = conn.execute(stmt)
conn.commit()
result = []
for row in res:
for item in row:
result.append(parse_obj(model, item))
return result
def page(where, sort, pageable, model, name) -> DataPage:
count = count_table(name)
table = get_table_model(name)
stmt = select(table).where(build_where_expression(where)).order_by(sort)
offset = pageable.pageSize * (pageable.pageNumber - 1)
stmt = stmt.offset(offset).limit(pageable.pageSize)
result = []
with engine.connect() as conn:
res = conn.execute(stmt)
conn.commit()
for row in res:
for item in row:
result.append(parse_obj(model, item))
return build_data_pages(pageable, result, count)
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 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 query_with_pagination(collection_name,
pagination,
base_model,
query_dict=None,
sort_dict=None):
count = count_table(collection_name)
table = get_table_model(collection_name)
result = []
session = Session(engine, future=True)
stmt = select(table)
for key in query_dict.keys():
if isinstance(query_dict.get(key), regex.Regex):
value = query_dict.get(key)
pattern = getattr(value, 'pattern')
if len(pattern) > 0:
stmt = stmt.where(eq(getattr(table, key), pattern))
else:
stmt = stmt.where(eq(getattr(table, key), pattern))
if isinstance(sort_dict[0], str):
order_field = sort_dict[0]
if sort_dict[1] == pymongo.DESCENDING:
order_seq = "desc"
else:
order_seq = "asc"
if order_seq == "desc":
stmt = stmt.order_by(getattr(table, order_field).desc())
else:
for tup in sort_dict:
order_field = tup[0]
if tup[1] == pymongo.DESCENDING:
order_seq = "desc"
if tup[1] == pymongo.ASCENDING:
order_seq = "asc"
if order_seq == "desc":
stmt = stmt.order_by(order_field.desc())
offset = pagination.pageSize * (pagination.pageNumber - 1)
stmt = stmt.offset(offset).limit(pagination.pageSize)
res = session.execute(stmt)
for row in res:
for item in row:
result.append(parse_obj(base_model, item))
return build_data_pages(pagination, result, count)
def update_(where, updates, model, name):
table = get_table_model(name)
stmt = update(table)
stmt = stmt.where(build_where_expression(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()
def update_one(one, model, name) -> any:
table = get_table_model(name)
stmt = update(table)
instance_dict: dict = convert_to_dict(one)
primary_key = get_primary_key(name)
stmt = stmt.where(
eq(getattr(table, primary_key), instance_dict.get(primary_key)))
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()
return model.parse_obj(one)
def delete_(where, model, name):
table = get_table_model(name)
stmt = delete(table).where(where)
with engine.connect() as conn:
conn.execute(stmt)
conn.commit()
