async def queryrow(self, sentence=""):
error = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
stmt = await self._connection.prepare(sentence)
self._attributes = stmt.get_attributes()
self._columns = [a.name for a in self._attributes]
self._result = await stmt.fetchrow()
except RuntimeError as err:
error = "Query Row Runtime Error: {}".format(str(err))
raise ProviderError(error)
except (PostgresSyntaxError, UndefinedColumnError,
PostgresError) as err:
error = "Statement Error: {}".format(str(err))
raise StatementError(error)
except (
asyncpg.exceptions.InvalidSQLStatementNameError,
asyncpg.exceptions.UndefinedTableError,
) as err:
error = "Invalid Statement Error: {}".format(str(err))
self._loop.call_exception_handler(err)
raise StatementError(error)
except Exception as err:
error = "Query Row Error: {}".format(str(err))
self._loop.call_exception_handler(err)
raise Exception(error)
# finally:
# await self.close()
return [self._result, error]
async def prepare(self, sentence=""):
error = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
try:
if not self._connection:
await self.connection()
try:
stmt = await asyncio.shield(self._connection.prepare(sentence))
try:
self._attributes = stmt.get_attributes()
self._columns = [a.name for a in self._attributes]
# self._columns = [a.name for a in stmt.get_attributes()]
self._prepared = stmt
self._parameters = stmt.get_parameters()
except TypeError:
self._columns = []
except FatalPostgresError as err:
error = "Fatal Runtime Error: {}".format(str(err))
raise StatementError(error)
except PostgresSyntaxError as err:
error = "Sentence Syntax Error: {}".format(str(err))
raise StatementError(error)
except PostgresError as err:
error = "PostgreSQL Error: {}".format(str(err))
raise StatementError(error)
except RuntimeError as err:
error = "Prepare Runtime Error: {}".format(str(err))
raise StatementError(error)
except Exception as err:
error = "Unknown Error: {}".format(str(err))
raise ProviderError(error)
finally:
return [self._prepared, error]
async def query(self, sentence=""):
error = None
self._result = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
startTime = datetime.now()
self._result = await self._connection.fetch(sentence)
if not self._result:
return [None, "Data was not found"]
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except (PostgresSyntaxError, UndefinedColumnError,
PostgresError) as err:
error = "Sentence Error: {}".format(str(err))
raise StatementError(error)
except (
asyncpg.exceptions.InvalidSQLStatementNameError,
asyncpg.exceptions.UndefinedTableError,
) as err:
error = "Invalid Statement Error: {}".format(str(err))
raise StatementError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
self._generated = datetime.now() - startTime
startTime = 0
return [self._result, error]
def _fetchall(self, sentence):
self._error = None
self._result = None
try:
stmt, error = self._loop.run_until_complete(self.prepare(sentence))
if stmt:
result = self._loop.run_until_complete(stmt.fetch())
self._result = asyncResult(result=result,
columns=self._columns)
except RuntimeError as err:
self._error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except (PostgresSyntaxError, UndefinedColumnError,
PostgresError) as err:
self._error = "Sentence Error: {}".format(str(err))
raise StatementError(error)
except (
asyncpg.exceptions.InvalidSQLStatementNameError,
asyncpg.exceptions.UndefinedTableError,
) as err:
self._error = "Invalid Statement Error: {}".format(str(err))
raise StatementError(error)
except Exception as err:
self._error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
return [self._result, self._error]
async def queryrow(self, sentence=""):
"""
queryrow.
Make a query to DB returning only one row
"""
error = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
stmt = await self._connection.prepare(sentence)
self._columns = [a.name for a in stmt.get_attributes()]
self._result = await stmt.fetchrow()
except RuntimeError as err:
error = "Runtime on Query Row Error: {}".format(str(err))
raise ProviderError(error)
except (PostgresSyntaxError, UndefinedColumnError,
PostgresError) as err:
error = "Sentence on Query Row Error: {}".format(str(err))
raise StatementError(error)
except (
asyncpg.exceptions.InvalidSQLStatementNameError,
asyncpg.exceptions.UndefinedTableError,
) as err:
error = "Invalid Statement Error: {}".format(str(err))
raise StatementError(error)
except Exception as err:
error = "Error on Query Row: {}".format(str(err))
raise Exception(error)
finally:
return [self._result, error]
async def columns(self, sentence, *args):
self._columns = []
if not self._connection:
await self.connection()
try:
stmt = await self._connection.prepare(sentence, *args)
self._columns = [a.name for a in stmt.get_attributes()]
except RuntimeError as err:
error = "Runtime on Query Row Error: {}".format(str(err))
raise ProviderError(error)
except (PostgresSyntaxError, UndefinedColumnError,
PostgresError) as err:
error = "Sentence on Query Row Error: {}".format(str(err))
raise StatementError(error)
except (
asyncpg.exceptions.InvalidSQLStatementNameError,
asyncpg.exceptions.UndefinedTableError,
) as err:
error = "Invalid Statement Error: {}".format(str(err))
raise StatementError(error)
except Exception as err:
error = "Error on Query Row: {}".format(str(err))
raise Exception(error)
finally:
return self._columns
async def insert(self, model: Model, fields: list = [], **kwargs):
"""
Inserting new object onto database.
"""
# TODO: option for returning more fields than PK in returning
if not self._connection:
await self.connection()
table = "{schema}.{table}".format(table=model.Meta.name,
schema=model.Meta.schema)
cols = []
source = []
pk = []
n = 1
for name, field in fields.items():
column = field.name
datatype = field.type
dbtype = field.get_dbtype()
val = getattr(model, field.name)
if is_dataclass(datatype):
value = json.loads(json.dumps(asdict(val), cls=BaseEncoder))
else:
value = val
if field.required is False and value is None or value == "None":
default = field.default
if callable(default):
value = default()
else:
continue
source.append(value)
cols.append(column)
n += 1
if field.primary_key is True:
pk.append(column)
try:
primary = "RETURNING {}".format(",".join(pk)) if pk else ""
columns = ",".join(cols)
values = ",".join(["${}".format(a) for a in range(1, n)])
insert = f"INSERT INTO {table} ({columns}) VALUES({values}) {primary}"
logging.debug(f"INSERT: {insert}")
stmt = await self._connection.prepare(insert)
result = await stmt.fetchrow(*source, timeout=2)
logging.debug(stmt.get_statusmsg())
if result:
# setting the values dynamically from returning
for f in pk:
setattr(model, f, result[f])
return result
except asyncpg.exceptions.UniqueViolationError as err:
raise StatementError("Constraint Error: {}".format(err))
except Exception as err:
print(traceback.format_exc())
raise Exception("Error on Insert over table {}: {}".format(
model.Meta.name, err))
async def copy_into_table(self,
table="",
schema="public",
source=None,
columns=None):
"""copy_into_table
get data from records (any iterable object) and save into table
returns: num of rows copied.
example: COPY 1470
"""
if not self._connection:
await self.connection()
if self._transaction:
# a transaction exists:
await self._transaction.commit()
try:
result = await self._connection.copy_records_to_table(
table_name=table,
schema_name=schema,
columns=columns,
records=source)
return result
except (asyncpg.exceptions.UndefinedTableError) as err:
error = "Error on Copy: {}, Table doesnt exists: {}".format(
str(err), str(table))
raise StatementError(error)
return False
except (InvalidSQLStatementNameError, UndefinedColumnError) as err:
error = "Error on Copy, Invalid Statement Error: {}".format(
str(err))
raise StatementError(error)
except (asyncpg.exceptions.UniqueViolationError) as err:
error = "Error on Copy, Constraint Violated: {}".format(str(err))
raise DataError(error)
except (asyncpg.exceptions.InterfaceError) as err:
error = "Error on Copy into Table Function: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Table Copy: {}".format(str(err))
raise Exception(error)
async def copy_to_table(self,
table="",
schema="public",
source=None,
type="csv",
columns=None):
"""copy_to_table
get data from a file, file-like object or a coroutine passed on "source" and copy into table
returns: num of rows copied.
example: COPY 1470
"""
if not self._connection:
await self.connection()
if self._transaction:
# a transaction exists:
await self._transaction.commit()
try:
result = await self._connection.copy_to_table(
table_name=table,
schema_name=schema,
columns=columns,
format=type,
source=source,
)
print(result)
return result
except (asyncpg.exceptions.UndefinedTableError):
error = "Error on Copy, Table doesnt exists: {}".format(str(table))
raise StatementError(error)
except (
asyncpg.exceptions.InvalidSQLStatementNameError,
asyncpg.exceptions.UndefinedTableError,
) as err:
error = "Error on Copy, Invalid Statement Error: {}".format(
str(err))
self._loop.call_exception_handler(err)
raise StatementError(error)
except Exception as err:
error = "Error on Table Copy: {}".format(str(err))
raise Exception(error)
async def create_rows(self, model: Model, rows: list):
"""
Create all records based on rows and returned
"""
if not self._connection:
await self.connection()
table = f"{model.Meta.schema}.{model.Meta.name}"
fields = model.columns(model)
results = []
stmt = None
for row in rows:
source = []
pk = []
cols = []
for col, field in fields.items():
print("HERE ", col, field)
if col not in row:
# field doesnt exists
default = field.default
if default is not None:
if callable(default):
source.append(default())
else:
source.append(default)
cols.append(col)
else:
# val = getattr(model, col)
# if val is not None:
# source.append(val)
# elif field.required is True or field.primary_key is True:
if field.required is True:
raise StatementError(
f"Missing Required Field: {col}")
else:
try:
val = row[col]
source.append(val)
cols.append(col)
except (KeyError, TypeError):
continue
try:
if field.primary_key is True:
pk.append(col)
except AttributeError:
pass
if not stmt:
columns = ", ".join(cols)
n = len(cols)
values = ",".join(["${}".format(a) for a in range(1, n + 1)])
primary = "RETURNING *"
insert = f"INSERT INTO {table} ({columns}) VALUES ({values}) {primary}"
logging.debug(f"INSERT: {insert}")
try:
stmt = await self._connection.prepare(insert)
except Exception as err:
print(traceback.format_exc())
raise Exception(
"Exception creating Prepared Sentence {}: {}".format(
model.Meta.name, err))
try:
result = await stmt.fetchrow(*source, timeout=2)
logging.debug(stmt.get_statusmsg())
if result:
results.append(result)
except asyncpg.exceptions.UniqueViolationError as err:
raise StatementError("Constraint Error: {}".format(err))
except Exception as err:
print(traceback.format_exc())
raise Exception("Error Bulk Insert {}: {}".format(table, err))
else:
return results