async def delete_rows(self, model: Model, conditions: dict, **kwargs):
"""
Deleting some records and returned.
"""
if not self._connection:
await self.connection()
table = f"{model.Meta.schema}.{model.Meta.name}"
source = []
pk = {}
cols = []
fields = model.columns(model)
for name, field in fields.items():
column = field.name
datatype = field.type
cols.append(column)
if column in kwargs:
value = Entity.toSQL(kwargs[column], datatype)
source.append("{} = {}".format(
column, Entity.escapeLiteral(value, datatype)))
set_fields = ", ".join(source)
condition = self._where(fields, **conditions)
columns = ", ".join(cols)
sql = f"DELETE FROM {table} {condition}"
logging.debug(sql)
try:
result = await self._connection.execute(sql)
if result:
return result
except Exception as err:
print(traceback.format_exc())
raise Exception("Error on Deleting table {}: {}".format(
model.Meta.name, err))
async def filter(self, model: Model, **kwargs):
if not self._connection:
await self.connection()
pk = {}
cols = []
fields = model.columns(model)
table = f"{model.Meta.schema}.{model.Meta.name}"
for name, field in fields.items():
column = field.name
datatype = field.type
cols.append(column)
try:
if field.primary_key is True:
pk[column] = Entity.toSQL(getattr(model, field.name),
datatype)
except AttributeError:
pass
columns = ", ".join(cols)
condition = self._where(fields, **kwargs)
sql = f"SELECT {columns} FROM {table} {condition}"
logging.debug(sql)
try:
return await self._connection.fetch(sql)
except Exception as err:
print(traceback.format_exc())
raise Exception("Error on Insert over table {}: {}".format(
model.Meta.name, err))
async def select(self, model: Model, fields: list = [], **kwargs):
if not self._connection:
await self.connection()
pk = {}
cols = []
source = {}
# print('HERE: ', model.__dict__)
table = f"{model.Meta.schema}.{model.Meta.name}"
for name, field in fields.items():
column = field.name
datatype = field.type
value = getattr(model, field.name)
cols.append(column)
if value is not None:
source[column] = Entity.toSQL(value, datatype)
# if field.primary_key is True:
# pk[column] = value
columns = ", ".join(cols)
arguments = {**source, **kwargs}
# print(arguments)
condition = self._where(fields=fields, **arguments)
sql = f"SELECT {columns} FROM {table} {condition}"
print(sql)
try:
return await self._connection.fetch(sql)
except Exception as err:
print(traceback.format_exc())
raise Exception("Error on Insert over table {}: {}".format(
model.Meta.name, err))
async def get_one(self, model: Model, **kwargs):
# if not self._connection:
# await self.connection()
table = f"{model.Meta.schema}.{model.Meta.name}"
pk = {}
cols = []
source = []
fields = model.columns(model)
# print(fields)
for name, field in fields.items():
val = getattr(model, field.name)
# print(name, field, val)
column = field.name
datatype = field.type
value = Entity.toSQL(val, datatype)
cols.append(column)
try:
if field.primary_key is True:
pk[column] = value
except AttributeError:
pass
columns = ", ".join(cols)
condition = self._where(fields, **kwargs)
sql = f"SELECT {columns} FROM {table} {condition}"
try:
return await self._connection.fetchrow(sql)
except Exception as err:
print(traceback.format_exc())
raise Exception("Error on Get One over table {}: {}".format(
model.Meta.name, err))
async def delete(self, model: Model, fields: list = [], **kwargs):
"""
Deleting a row Model based on Primary Key.
"""
if not self._connection:
await self.connection()
result = None
tablename = f"{model.Meta.schema}.{model.Meta.name}"
source = []
pk = {}
cols = []
for name, field in fields.items():
column = field.name
datatype = field.type
value = Entity.toSQL(getattr(model, field.name), datatype)
if field.primary_key is True:
pk[column] = value
# TODO: work in an "update, delete, insert" functions on asyncdb to abstract data-insertion
sql = "DELETE FROM {table} {condition}"
condition = self._where(fields, **pk)
sql = sql.format_map(SafeDict(table=tablename))
sql = sql.format_map(SafeDict(condition=condition))
try:
result = await self._connection.execute(sql)
# DELETE 1
except Exception as err:
print(traceback.format_exc())
raise Exception("Error on Delete over table {}: {}".format(
model.Meta.name, err))
return result
def _where(self, fields: list, **where):
"""
TODO: add conditions for BETWEEN, NOT NULL, NULL, etc
"""
result = ""
if not where:
return result
elif type(where) == str:
result = "WHERE {}".format(where)
elif type(where) == dict:
where_cond = []
for key, value in where.items():
# print('HERE> ', fields)
f = fields[key]
datatype = f.type
if value is None or value == "null" or value == "NULL":
where_cond.append(f"{key} is NULL")
elif value == "!null" or value == "!NULL":
where_cond.append(f"{key} is NOT NULL")
elif type(value) == bool:
val = str(value)
where_cond.append(f"{key} is {value}")
elif isinstance(datatype, List):
val = ", ".join(
map(str,
[Entity.escapeLiteral(v, type(v)) for v in value]))
where_cond.append(
f"ARRAY[{val}]<@ {key}::character varying[]")
elif Entity.is_array(datatype):
val = ", ".join(
map(str,
[Entity.escapeLiteral(v, type(v)) for v in value]))
where_cond.append(f"{key} IN ({val})")
else:
# is an scalar value
val = Entity.escapeLiteral(value, datatype)
where_cond.append(f"{key}={val}")
result = "\nWHERE %s" % (" AND ".join(where_cond))
return result
else:
return result