async def get(self, table, id):
"""
get
get only one row based on primary key or filtering, Get a document by primary key
-----
"""
error = None
if not id:
raise EmptyStatement("Rethink: Id for get cannot be empty")
if not table:
raise EmptyStatement("Rethink: Table name is an empty string")
if self._connection:
try:
data = await self._engine.table(table).get(id).run(self._connection)
if data:
self._result = data
else:
raise NoDataFound(message="Empty Result", code=404)
except ReqlNonExistenceError as err:
error = "Empty Result: {}".format(str(err))
raise NoDataFound(error)
except (ReqlRuntimeError, ReqlRuntimeError, ReqlError) as err:
error = "QueryRow Runtime Error: {}".format(str(err))
raise ProviderError(err)
return False
finally:
return [self._result, error]
async def query(self, sentence="", params: list = []):
"""
Making a Query and return result
"""
error = None
if not sentence:
raise EmptyStatement("Error: Empty Sentence")
if not self._connection:
await self.connection()
try:
self._connection.execute_query(sentence, params)
self._result = self._connection
except (_mssql.MSSQLDatabaseException) as err:
print(err)
error = "Database Error: {}".format(str(err))
raise ProviderError(error)
except pymssql.Warning as warn:
logging.warning(f"SQL Server Warning: {warn!s}")
error = warn
except (pymssql.StandardError, pymssql.Error) as err:
error = "SQL Server Error: {}".format(str(err))
raise ProviderError(error)
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
print(error)
return [self._result, error]
async def queryrow(self, sentence="", **kwargs):
# self._logger.debug("Start Query function")
error = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
startTime = datetime.now()
print(startTime)
params = {"chunked": True, "chunk_size": 1}
fn = partial(self._connection.query, sentence, **params, **kwargs)
self._result = await asyncio.get_running_loop().run_in_executor(
None, fn)
if not self._result:
raise NoDataFound("InfluxDB: No Data was Found")
return [None, "InfluxDB: No Data was Found"]
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
return [self._result, error]
async def query(self, sentence="", params: list = None):
"""
Making a Query and return result
"""
error = None
self._result = None
if not sentence:
raise EmptyStatement("Error: Empty Sentence")
if not self._connection:
await self.connection()
if isinstance(sentence, str):
sentence = sentence.encode(self._charset)
try:
startTime = datetime.now()
self._cursor = self._connection.cursor()
self._cursor.execute(sentence, params)
self._result = self._cursor.fetchall()
if not self._result:
raise NoDataFound("SQL Server: No Data was Found")
return [None, "SQL Server: No Data was Found"]
except (pymssql.StandardError, pymssql.Error) as err:
error = "SQL Server Error: {}".format(str(err))
raise ProviderError(error)
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
self._generated = datetime.now() - startTime
return [self._result, 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 executemany(self, sentence="", *args, timeout=None):
"""execute.
Execute a transaction
get a SQL sentence and execute
returns: results of the execution
"""
self._error = None
self._result = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
async with self._connection.transaction():
await self._connection.executemany(sentence,
timeout=timeout,
*args)
return [True, None]
except InterfaceWarning as err:
self._error = "Interface Warning: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
self._error = "Error on Execute: {}".format(str(err))
finally:
return [True, self._error]
async def queryrow(self, table, filter={}, id=0):
"""
queryrow
get only one row
"""
error = None
if not table:
raise EmptyStatement("Rethink: Table name is an empty string")
if self._connection:
# startTime = datetime.now()
try:
data = (
await self._engine.table(table)
.filter(filter)
.nth(id)
.run(self._connection)
)
if data:
self._result = data
else:
raise NoDataFound(message="Empty Result", code=404)
except ReqlNonExistenceError as err:
error = "Empty Result: {}".format(str(err))
raise NoDataFound(error)
except (ReqlRuntimeError, ReqlRuntimeError, ReqlError) as err:
error = "QueryRow Runtime Error: {}".format(str(err))
raise ProviderError(err)
return False
finally:
return [self._result, error]
else:
return [None, "Not Connected"]
async def execute(self, sentence=""):
"""Execute a transaction
get a SQL sentence and execute
returns: results of the execution
"""
error = None
result = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
result = await self._connection.execute(sentence)
return [result, None]
except InterfaceWarning as err:
error = "Interface Warning: {}".format(str(err))
raise ProviderError(error)
except asyncpg.exceptions.DuplicateTableError as err:
error = err
raise ProviderError(error)
except Exception as err:
error = "Error on Execute: {}".format(str(err))
# self._loop.call_exception_handler(err)
raise ProviderError(error)
finally:
return [result, error]
async def executemany(self, sentence="", params: list = []):
"""
Execute multiple sentences
"""
"""
Execute a sentence
"""
error = None
self._result = None
if not sentence:
raise EmptyStatement("Error: Empty Sentence")
if not self._connection:
await self.connection()
# getting a cursor
try:
self._cursor = self._connection.cursor()
self._result = self._cursor.executemany(sentence, params)
# self._connection.commit()
except pymssql.Warning as warn:
logging.warning(f"SQL Server Warning: {warn!s}")
error = warn
except (pymssql.StandardError, pymssql.Error) as err:
error = "SQL Server Error: {}".format(str(err))
raise ProviderError(error)
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
# self._connection.commit()
print(error)
return [self._result, error]
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 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]
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:
# print(stmt.get_attributes())
self._columns = [a.name for a in stmt.get_attributes()]
self._prepared = stmt
self._parameters = stmt.get_parameters()
except TypeError:
self._columns = []
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: Union[str, SimpleStatement, PreparedStatement],
params: list = [],
):
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 = self._connection.execute(sentence, params)
if not self._result:
raise NoDataFound("Cassandra: No Data was Found")
return [None, "Cassandra: No Data was Found"]
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
self._generated = datetime.now() - startTime
return [self._result, error]
async def cursor(self, sentence):
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
self._transaction = self._connection.transaction()
await self._transaction.start()
self._cursor = await self._connection.cursor(sentence)
return self
async def query(self, table, filter=None):
"""
query
get all rows from a table
-----
"""
error = None
if not table:
raise EmptyStatement("Rethink: Table name is an empty string")
if self._connection:
data = []
try:
# self._columns = await self._engine.table(table).get(1).keys().run(self._connection)
self._columns = (
await self._engine.table(table)
.nth(0)
.default(None)
.keys()
.run(self._connection)
)
# print(self._columns)
if not filter:
cursor = (
await self._engine.db(self._db)
.table(table)
.run(self._connection)
)
else:
cursor = (
await self._engine.db(self._db)
.table(table)
.filter(filter)
.run(self._connection)
)
while await cursor.fetch_next():
row = await cursor.next()
data.append(row)
if data:
self._result = data
else:
raise NoDataFound(message="Empty Result", code=404)
except ReqlNonExistenceError as err:
error = "Query Runtime Error: {}".format(str(err))
raise ReqlNonExistenceError(error)
except ReqlRuntimeError as err:
error = "Query Runtime Error: {}".format(str(err))
raise NoDataFound(error)
except ReqlResourceLimitError as error:
error = "Query Runtime Error: {}".format(str(err))
raise ReqlResourceLimitError(error)
except ReqlOpIndeterminateError as error:
error = "Query Runtime Error: {}".format(str(err))
raise ReqlOpIndeterminateError(error)
finally:
# self._generated = datetime.now() - startTime
return [self._result, error]
async def between(self, table, min=None, max=None, idx=""):
"""
between
Get all documents between two keys
-----
"""
if not table:
raise EmptyStatement("Rethink: Table name is an empty string")
if self._connection:
error = None
if min:
m = min
else:
m = self._engine.minval
if max:
mx = max
else:
mx = self._engine.maxval
try:
if idx:
cursor = (
await self._engine.table(table)
.order_by(index=idx)
.between(m, mx, index=idx)
.run(self._connection)
)
else:
cursor = (
await self._engine.table(table)
.between(m, mx)
.run(self._connection)
)
data = []
while await cursor.fetch_next():
item = await cursor.next()
data.append(item)
if data:
self._result = data
else:
raise NoDataFound(message="Empty Result", code=404)
except (ReqlRuntimeError, ReqlRuntimeError, ReqlError) as err:
error = str(err)
raise ProviderError(err)
return False
finally:
return [self._result, error]
else:
return None
async def executemany(self, sentence="", args=[]):
error = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
await self.begin()
await self._cursor.executemany(sentence, args)
await self.commit()
return False
except Exception as err:
await self.rollback()
error = "Error on Execute: {}".format(str(err))
raise Exception(error)
finally:
return error
async def executemany(self, sentence="", *args):
error = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
await self._connection.executemany(sentence, *args)
except InterfaceWarning as err:
error = "Interface Warning: {}".format(str(err))
raise ProviderError(error)
return False
except Exception as err:
error = "Error on Execute: {}".format(str(err))
# self._loop.call_exception_handler(err)
raise Exception(error)
finally:
return error
async def prepare(self, sentence=""):
error = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
self._prepared = self._connection.prepare(sentence)
self._prepared.consistency_level = ConsistencyLevel.QUORUM
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
print(error)
return [self._prepared, error]
async def execute(self, sentence="", method: str = "GET", **kwargs):
"""Execute a transaction
returns: results of the execution
"""
error = None
result = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
result = self._connection.request(sentence, method, **kwargs)
except Exception as err:
error = "Error on Execute: {}".format(str(err))
raise [None, error]
finally:
return [result, error]
async def delete(self, table, id=None, filter={}, changes=True):
"""
delete
delete a record based on id or filter search
-----
"""
if not table:
raise EmptyStatement("Rethink: Table name is an empty string")
if self._connection:
if id:
try:
self._result = (
await self._engine.table(table)
.get(id)
.delete(return_changes=changes)
.run(self._connection)
)
return self._result
except (ReqlRuntimeError, ReqlRuntimeError, ReqlError) as err:
raise ProviderError(err)
return False
elif isinstance(filter, dict):
try:
self._result = (
await self._engine.table(table)
.filter(filter)
.delete(return_changes=changes)
.run(self._connection)
)
return self._result
except (ReqlRuntimeError, ReqlRuntimeError, ReqlError) as err:
raise ProviderError(err)
return False
else:
# delete all documents in table
return (
await self._engine.table(table)
.delete(return_changes=changes)
.run(self._connection)
)
else:
return False
async def execute(self, sentence=""):
"""Execute a transaction
get a SQL sentence and execute
returns: results of the execution
"""
error = None
result = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
result = await self._cursor.execute(sentence)
return [result, None]
return [None, error]
except Exception as err:
error = "Error on Execute: {}".format(str(err))
raise [None, error]
finally:
return [result, error]
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._columns = [a.name for a in stmt.get_attributes()]
await self._cursor.execute(sentence)
self._result = await self.fetchone()
except RuntimeError as err:
error = "Runtime on Query Row Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query Row: {}".format(str(err))
raise Exception(error)
# finally:
# await self.close()
return [self._result, error]
async def execute(self, sentence, params: List = []):
"""Execute a transaction
get a CQL sentence and execute
returns: results of the execution
"""
error = None
self._result = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
self._result = self._connection.execute(sentence, params)
if not self._result:
raise NoDataFound("Cassandra: No Data was Found")
return [None, "Cassandra: No Data was Found"]
return [self._result, None]
except Exception as err:
error = "Error on Execute: {}".format(str(err))
raise [None, error]
finally:
return [self._result, error]
async def query(self, sentence="", size=100000000000):
# self._logger.debug("Start Query function")
error = None
if not sentence:
raise EmptyStatement("Sentence is an empty string")
if not self._connection:
await self.connection()
try:
startTime = datetime.now()
await self._cursor.execute(sentence)
self._result = await self.fetchmany(size)
if not self._result:
raise NoDataFound("Mysql: No Data was Found")
return [None, "Mysql: No Data was Found"]
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
# self._generated = datetime.now() - startTime
# await self.close()
return [self._result, error]
async def match(self, table, field="id", regexp="(?i)^[a-z]+$"):
"""
match
get all rows where the given value matches with a regular expression
-----
"""
if not table:
raise EmptyStatement("Rethink: Table name is an empty string")
if self._connection:
try:
data = (
await self._engine.table(table)
.filter(lambda doc: doc[field].match(regexp))
.run(self._connection)
)
if data:
self._result = data
else:
raise NoDataFound(message="Empty Result", code=404)
return self._result
except ReqlRuntimeError as err:
error = "Query Get All Runtime Error: {}".format(str(err))
raise ProviderError(error)
return False
async def get_all(self, table, filter=[], index=""):
"""
get_all
get all rows where the given value matches the value of the requested index
-----
"""
if not table:
raise EmptyStatement("Rethink: Table name is an empty string")
if self._connection:
try:
if index:
cursor = (
await self._engine.table(table)
.get_all(filter, index=index)
.run(self._connection)
)
else:
cursor = (
await self._engine.table(table)
.get_all(filter)
.run(self._connection)
)
data = []
while await cursor.fetch_next():
item = await cursor.next()
data.append(item)
if data:
self._result = data
else:
raise NoDataFound(message="Empty Result", code=404)
return self._result
except ReqlRuntimeError as err:
error = "Query Get All Runtime Error: {}".format(str(err))
raise ProviderError(error)
return False
async def fetch(self, sentence="", size: int = 1, params: list = []):
error = None
self._result = None
if not sentence:
raise EmptyStatement("Error: Empty Sentence")
if not self._connection:
await self.connection()
try:
startTime = datetime.now()
self._cursor = self._connection.cursor()
self._cursor.execute(sentence, *params)
self._result = self._cursor.fetchmany(size)
if not self._result:
raise NoDataFound("SQL Server: No Data was Found")
return [None, "SQL Server: No Data was Found"]
except RuntimeError as err:
error = "Runtime Error: {}".format(str(err))
raise ProviderError(error)
except Exception as err:
error = "Error on Query: {}".format(str(err))
raise Exception(error)
finally:
self._generated = datetime.now() - startTime
return [self._result, error]