def drop_row_by_id(model_obj, t_id, column_name="id"):
try:
session.query(model_obj).filter(getattr(model_obj, column_name) == t_id).delete()
session.commit()
except:
print("rollback")
session.rollback()
def get_highest_row(model, col_name='Id'):
if force_check:
session.commit()
rows = session.query(model).order_by(col_name).all()
if len(rows) > 0:
return rows[-1]
def product_in_stores_by_id(p_id):
query = session.query(Shop.city, Shop.address_line_one, Storage.product_amount). \
join(ShopStorage, Shop.id == ShopStorage.ShopId). \
join(Storage, Storage.id == ShopStorage.ShopId). \
filter(ShopStorage.ProductId == p_id). \
all()
return query
def get_all_rows_ordered_by(model, col_name='Id'):
try:
if force_check:
session.commit()
result = session.query(model).order_by(col_name).all()
except exc.SQLAlchemyError as e:
print(e)
session.rollback()
result = []
return result
def get_all_rows(model):
# Return all rows in given table
try:
if force_check:
session.commit()
result = session.query(model).all()
except exc.SQLAlchemyError as e:
print(e)
session.rollback()
result = []
return result
def get_rows_by_column(model, row_id, col_name='Id'):
try:
if force_check:
session.commit()
result = session.query(model) \
.filter(getattr(model, col_name) == row_id).all()
except exc.SQLAlchemyError as e:
print(e)
session.rollback()
result = []
return result
def remove_rows_by_column_name(model, row_id, col_name='Id'):
try:
obj = session.query(model) \
.filter(getattr(model, col_name) == row_id) \
.delete()
session.commit()
except exc.SQLAlchemyError as e:
print(e)
session.rollback()
return None
return obj
def get_row_by_column(model, row_id, col_name='Id'):
# Returns First row that matches model.col_name == row_id
try:
if force_check:
session.commit()
result = session.query(model) \
.filter(getattr(model, col_name) == row_id) \
.one()
except exc.SQLAlchemyError as e:
print(e)
session.rollback()
result = None
return result
def remove_row_by_id(model, row_id, col_name='Id'):
# Removes row from table (model) that matches (model.col_name) == (row_id)
try:
obj = session.query(model) \
.filter(getattr(model, col_name) == row_id) \
.one()
session.delete(obj)
session.commit()
except exc.SQLAlchemyError as e:
print(e)
session.rollback()
return None
return obj
def get_rows_like_column_value(model, col_name, value):
"""Return all rows that contains model.col_name LIKE value
Good way to search database. """
try:
if force_check:
session.commit()
result = session.query(model) \
.filter(getattr(model, col_name).ilike(f'%{value}%')) \
.all()
except exc.SQLAlchemyError as e:
print(e)
session.rollback()
result = []
return result
def get_asset_by_id(model_obj, t_id, column_name="id"):
return session.query(model_obj).filter(getattr(model_obj, column_name) == t_id).first()
def get_all_assets(model_obj):
return session.query(model_obj).all()
def get_assets_by_columnvalue(model_obj, column_name, value):
return session.query(model_obj).filter(getattr(model_obj, column_name).ilike(f'%{value}%')).all()
def load_user(user_id):
return session.query(User).get(user_id)
def get_random_row(model):
if force_check:
session.commit()
return session.query(model).order_by(func.random()).first()