def get_query(self):
"""
Returns a string representing an SQL query. The string will point
to the database cache of this query if it exists.
Returns
-------
str
SQL query string.
"""
try:
table_name = self.fully_qualified_table_name
schema, name = table_name.split(".")
with rlock(self.redis, self.md5):
if self.connection.has_table(schema=schema, name=name):
try:
touch_cache(self.connection, self.md5)
except ValueError:
pass # Cache record not written yet, which can happen for Models
# which will call through to this method from their `_make_query` method while writing metadata.
# In that scenario, the table _is_ written, but won't be visible from the connection touch_cache uses
# as the cache metadata transaction isn't complete!
return "SELECT * FROM {}".format(table_name)
except NotImplementedError:
pass
return self._make_query()
def do_query() -> ModelResult:
logger.debug("Getting storage lock.")
with rlock(self.redis, self.md5):
logger.debug("Obtained storage lock.")
con = self.connection.engine
if force:
self.invalidate_db_cache(name, schema=schema)
try:
with con.begin():
logger.debug("Using pandas to store.")
self._df.to_sql(name, con, schema=schema, index=False)
if schema == "cache":
self._db_store_cache_metadata(
compute_time=self._runtime)
except AttributeError:
logger.debug(
"No dataframe to store, presumably because this"
" was retrieved from the db.")
logger.debug("Released storage lock.")
return self
def do_query() -> Query:
logger.debug("Getting storage lock.")
with rlock(self.redis, self.md5):
logger.debug("Obtained storage lock.")
query_ddl_ops = self._make_sql(name,
schema=schema,
force=force)
logger.debug("Made SQL.")
con = self.connection.engine
if force:
self.invalidate_db_cache(name, schema=schema)
plan_time = 0
with con.begin():
ddl_op_results = []
for ddl_op in query_ddl_ops:
try:
ddl_op_result = con.execute(ddl_op)
except Exception as e:
logger.error(
f"Error executing SQL: '{ddl_op}'. Error was {e}"
)
raise e
try:
ddl_op_results.append(ddl_op_result.fetchall())
except ResourceClosedError:
pass # Nothing to do here
for ddl_op_result in ddl_op_results:
try:
plan = ddl_op_result[0][0][
0] # Should be a query plan
plan_time += plan["Execution Time"]
except (IndexError, KeyError):
pass # Not an explain result
logger.debug("Executed queries.")
if schema == "cache":
self._db_store_cache_metadata(compute_time=plan_time)
logger.debug("Released storage lock.")
return self
def invalidate_db_cache(self,
name=None,
schema=None,
cascade=True,
drop=True):
"""
Helper function for store, drops this table, and (by default) any
that depend on it, as well as removing them from
the cache metadata table.
Parameters
----------
name : str
Name of the table
schema : str
Schema of the table
cascade : bool
Set to false to remove only this table from cache
drop : bool
Set to false to remove the cache record without dropping the table
"""
with rlock(self.redis, self.md5):
con = self.connection.engine
try:
table_reference_to_this_query = self.get_table()
if table_reference_to_this_query is not self:
table_reference_to_this_query.invalidate_db_cache(
cascade=cascade,
drop=drop) # Remove any Table pointing as this query
except (ValueError, NotImplementedError) as e:
pass # This cache record isn't actually stored
try:
deps = self.connection.fetch(
"""SELECT obj FROM cache.cached LEFT JOIN cache.dependencies
ON cache.cached.query_id=cache.dependencies.query_id
WHERE depends_on='{}'""".format(self.md5))
with con.begin():
con.execute("DELETE FROM cache.cached WHERE query_id=%s",
(self.md5, ))
logger.debug("Deleted cache record for {}.".format(
self.fully_qualified_table_name))
if drop:
con.execute("DROP TABLE IF EXISTS {}".format(
self.fully_qualified_table_name))
logger.debug("Dropped cache for for {}.".format(
self.fully_qualified_table_name))
if cascade:
for rec in deps:
dep = pickle.loads(rec[0])
logger.debug(
"Cascading to {} from cache record for {}.".format(
dep.fully_qualified_table_name,
self.fully_qualified_table_name,
))
dep.invalidate_db_cache()
else:
logger.debug("Not cascading to dependents.")
except NotImplementedError:
logger.info("Table has no standard name.")
if schema is not None:
full_name = "{}.{}".format(schema, name)
else:
full_name = name
logger.debug("Dropping {}".format(full_name))
with con.begin():
con.execute("DROP TABLE IF EXISTS {}".format(full_name))
def unlock(timer):
with rlock(dl.redis, dl.md5):
for i in range(101):
timer.append(i)