def get(cls,
ctx: EasyApiContext = None,
query: dict = None,
sorter: Sorter = None):
"""
通用get查询
:param ctx:
:param query:
:param args:
:param kwargs:
:return:
"""
if ctx is None:
ctx = EasyApiContext()
if query is None:
query = {}
query = cls.reformatter(ctx=ctx, data=query)
table = cls.__db__[cls.__tablename__]
sql = select([table])
if query:
sql = search_sql(sql, query, table)
sql = sql.order_by(table.c.id.desc())
if sorter:
order_by = sorter.sort_by
desc = sorter.desc
if desc:
sql = sql.order_by(
getattr(table.c, order_by, table.c.id).desc())
else:
sql = sql.order_by(getattr(table.c, order_by, table.c.id))
res = cls.__db__.execute(ctx=ctx, sql=sql)
data = res.first()
if not data:
return None
return cls.formatter(ctx, data)
def update(cls,
ctx: EasyApiContext = None,
where_dict: dict = None,
data: dict = None,
*args,
**kwargs):
"""
通用修改
:param ctx:
:param where_dict:
:param data:
:param args:
:param kwargs:
:return:
"""
if ctx is None:
ctx = EasyApiContext()
if where_dict is None:
where_dict = {}
where_dict = cls.reformatter(ctx, where_dict)
table = cls.__db__[cls.__tablename__]
data = cls.reformatter(ctx, data)
sql = table.update()
if where_dict is not None:
for key, value in where_dict.items():
if hasattr(table.c, key):
sql = sql.where(getattr(table.c, key) == value)
sql = sql.values(**data)
res = cls.__db__.execute(ctx=ctx, sql=sql)
return res.rowcount
def execute(cls,
ctx: EasyApiContext = None,
sql='SELECT 1',
*args,
**kwargs):
"""
直接执行sql
:param ctx:
:param sql:
:param args:
:param kwargs:
:return:
"""
if ctx is None:
ctx = EasyApiContext()
res = cls.__db__.execute(ctx=ctx, sql=sql, *args, **kwargs)
return res
def query(cls,
ctx: EasyApiContext = None,
query: Pager = None,
pager: Pager = None,
sorter: Sorter = None):
"""
通用query查询
:param ctx:
:param query:
:param pager:
:param sorter:
:param args:
:param kwargs:
:return:
"""
if ctx is None:
ctx = EasyApiContext()
if query is None:
query = {}
query = cls.reformatter(ctx=ctx, data=query)
table = cls.__db__[cls.__tablename__]
sql = select([table])
if query:
sql = search_sql(sql, query, table)
if pager is not None:
per_page = pager.per_page
page = pager.page
if per_page:
sql = sql.limit(per_page)
if page:
if per_page is None:
sql = sql.offset((page - 1) * 30).limit(30)
else:
sql = sql.offset((page - 1) * per_page)
if sorter:
order_by = sorter.sort_by
desc = sorter.desc
if desc:
sql = sql.order_by(
getattr(table.c, order_by, table.c.id).desc())
else:
sql = sql.order_by(getattr(table.c, order_by, table.c.id))
res = cls.__db__.execute(ctx=ctx, sql=sql)
data = res.fetchall()
return list(map(lambda d: cls.formatter(ctx=ctx, data=d), data))
def insert(cls, ctx: EasyApiContext = None, data: dict = None):
"""
通用插入
:param ctx:
:param data:
:param args:
:param kwargs:
:return:
"""
if ctx is None:
ctx = EasyApiContext()
if data is None:
return None
table = cls.__db__[cls.__tablename__]
data = cls.reformatter(ctx=ctx, data=data)
sql = table.insert().values(**data)
res = cls.__db__.execute(ctx=ctx, sql=sql)
return res.inserted_primary_key[0]
def count(cls, ctx: EasyApiContext = None, query: dict = None):
"""
插入
:param ctx:
:param query:
:param args:
:param kwargs:
:return:
"""
if ctx is None:
ctx = EasyApiContext()
if query is None:
query = {}
query = cls.reformatter(ctx=ctx, data=query)
table = cls.__db__[cls.__tablename__]
sql = select([func.count('*')], from_obj=table)
if query:
sql = search_sql(sql, query, table)
res = cls.__db__.execute(ctx=ctx, sql=sql)
return res.scalar()