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, sentence, where):
sql = ""
if sentence:
where_string = ""
if not where:
sql = sentence.format_map(SafeDict(where_cond=""))
elif type(where) == dict:
where_cond = []
for key, value in where.items():
# print("KEY {}, VAL: {}".format(key, value))
if type(value) == str or type(value) == int:
if value == "null" or value == "NULL":
where_string.append("%s IS NULL" % (key))
elif value == "!null" or value == "!NULL":
where_string.append("%s IS NOT NULL" % (key))
elif key.endswith("!"):
where_cond.append("%s != %s" % (key[:-1], value))
else:
if (
type(value) == str
and value.startswith("'")
and value.endswith("'")
):
where_cond.append("%s = %s" % (key, "{}".format(value)))
elif type(value) == int:
where_cond.append("%s = %s" % (key, "{}".format(value)))
else:
where_cond.append(
"%s = %s" % (key, "'{}'".format(value))
)
elif type(value) == bool:
val = str(value)
where_cond.append("%s = %s" % (key, val))
else:
val = ",".join(map(str, value))
if type(val) == str and "'" not in val:
where_cond.append("%s IN (%s)" % (key, "'{}'".format(val)))
else:
where_cond.append("%s IN (%s)" % (key, val))
# if 'WHERE ' in sentence:
# where_string = ' AND %s' % (' AND '.join(where_cond))
# else:
where_string = " WHERE %s" % (" AND ".join(where_cond))
print("WHERE cond is %s" % where_string)
sql = sentence.format_map(SafeDict(where_cond=where_string))
elif type(where) == str:
where_string = where
if not where.startswith("WHERE"):
where_string = " WHERE %s" % where
sql = sentence.format_map(SafeDict(where_cond=where_string))
else:
sql = sentence.format_map(SafeDict(where_cond=""))
del where
del where_string
return sql
else:
return False
def fields(self, sentence, fields=None):
_sql = False
if not fields:
_sql = sentence.format_map(SafeDict(fields="*"))
elif type(fields) == str:
_sql = sentence.format_map(SafeDict(fields=fields))
elif type(fields) == list:
_sql = sentence.format_map(SafeDict(fields=",".join(fields)))
return _sql
async def save(self, model: Model, fields: list = [], **kwargs):
"""
Updating a Model object based on primary Key or conditions
TODO: check if row doesnt exists, then, insert
"""
if not self._connection:
await self.connection()
table = f"{model.Meta.schema}.{model.Meta.name}"
source = []
pk = {}
values = []
n = 1
for name, field in fields.items():
column = field.name
datatype = field.type
# try:
# dbtype = field.get_dbtype()
# except AttributeError:
# dbtype = ''
value = getattr(model, field.name)
source.append("{} = {}".format(name, "${}".format(n)))
values.append(value)
n += 1
try:
if field.primary_key is True:
pk[column] = value
except AttributeError:
pass
# TODO: work in an "update, delete, insert" functions on asyncdb to abstract data-insertion
sql = "UPDATE {table} SET {set_fields} {condition}"
condition = self._where(fields, **pk)
sql = sql.format_map(SafeDict(table=table))
sql = sql.format_map(SafeDict(condition=condition))
# set the columns
sql = sql.format_map(SafeDict(set_fields=", ".join(source)))
print(sql)
try:
logging.debug(sql)
stmt = await self._connection.prepare(sql)
result = await stmt.fetchrow(*values, timeout=2)
logging.debug(stmt.get_statusmsg())
# result = await self._connection.fetchrow(sql)
return result
except Exception as err:
print(traceback.format_exc())
raise Exception("Error on Insert over table {}: {}".format(
model.Meta.name, err))
def insert(self, table, data, **kwargs):
"""
insert
insert the result onto a table
"""
sql = "INSERT INTO {table} ({fields}) VALUES ({values})"
sql = sql.format_map(SafeDict(table=table))
# set columns
sql = sql.format_map(SafeDict(fields=",".join(data.keys())))
values = ",".join(str(v) for v in data.values())
sql = sql.format_map(SafeDict(values=values))
try:
result = self._loop.run_until_complete(self._connection.execute(sql))
if not result:
print(result)
return False
else:
return result
except Exception as err:
# print(sql)
print(err)
return False
def get_query(self, sentence):
"""
get_query
Get formmated query
"""
sql = sentence
try:
# remove fields and where_cond
sql = sentence.format_map(SafeDict(fields="*", where_cond=""))
if not self.connected:
self.connection()
prepared, error = self._loop.run_until_complete(self.prepare(sql))
if not error:
self._columns = self.get_columns()
else:
return False
except (ProviderError, StatementError) as err:
return False
except Exception as e:
print(e)
return False
return sql
def table(self, table):
try:
return self._query_raw.format_map(SafeDict(table=table))
except Exception as e:
print(e)
return False