def query(self, query: str,
chunksize: int) -> Generator[DataFrame, None, None]:
"""
Query DAO repo and returns a generator of DataFrames with query results.
Keyword Arguments:
query {str} -- Query string
chunksize {int} -- Number of rows of dataframe per chunk
Returns:
Generator[DataFrame] -- Generator to iterate over DataFrame results.
"""
if query is None:
raise DaoError
try:
offset = 0
# Remove semicolon if exists in original query to add ordering to query
query = query.strip(";")
while True:
add_query = f" ORDER BY {self._pkey} LIMIT {chunksize} OFFSET {offset};"
gbq_query = query + add_query
response_df = pandas_gbq.read_gbq(gbq_query)
if response_df.empty:
return
offset += chunksize
yield response_df
except Exception as gbq_exp:
raise DaoError(gbq_exp)
def save(self, payload: DataFrame):
"""
Save payload to database table.
Arguments:
payload {DataFrame} -- the payload to be stored in db
Raises:
DaoError: if cannot save payload to db
"""
try:
# Always append date column
# Todays date
date = datetime.today().strftime("%Y-%m-%d")
payload.insert(0, "date", date)
self.LOGGER.info(
f"Saving {len(payload)} records to: {self.tablename}")
pandas_gbq.to_gbq(
payload,
self._tablename,
if_exists="append",
table_schema=self._table,
progress_bar=False,
)
self.LOGGER.info(
f"{len(payload)} records saved successfully to: {self.tablename}"
)
except Exception as sql_exp:
self.LOGGER.exception(str(sql_exp))
raise DaoError(sql_exp)
def save(self, payload: DataFrame):
"""
Save payload to database table.
Arguments:
payload {DataFrame} -- the payload to be stored in db
Raises:
DaoError: if cannot save payload to db
"""
try:
pandas_gbq.to_gbq(
payload,
self._tablename,
if_exists="append",
table_schema=self._table,
)
except Exception as sql_exp:
raise DaoError(sql_exp)
def save(self, payload: DataFrame):
"""
Save payload to database table.
Arguments:
payload {DataFrame} -- the payload to be stored in db
Raises:
DaoError: if cannot save payload to db
"""
try:
payload.to_sql(
self._tablename,
con=self._engine,
if_exists="append",
index=False,
method="multi",
)
except Exception as sql_exp:
raise DaoError(sql_exp)
def query(self,
query: str,
chunksize: int,
exact=False) -> Generator[DataFrame, None, None]:
"""
Query DAO repo and returns a generator of DataFrames with query results.
Keyword Arguments:
query {str} -- Query string
chunksize {int} -- Number of rows of dataframe per chunk
exact {bool} -- If false, query orders results and returns chunks
Returns:
Generator[DataFrame] -- Generator to iterate over DataFrame results.
"""
if query is None:
raise DaoError
try:
if exact:
self.LOGGER.info(f"Querying {self.tablename}: {query}")
response_df = pandas_gbq.read_gbq(query,
progress_bar_type=None)
yield response_df
if not exact:
offset = 0
# Remove semicolon if exists in original query to add ordering to query
query = query.strip(";")
while True:
add_query = (
f" ORDER BY {self._pkey} LIMIT {chunksize} OFFSET {offset};"
)
gbq_query = query + add_query
self.LOGGER.info(f"Querying {self.tablename}: {gbq_query}")
response_df = pandas_gbq.read_gbq(gbq_query)
if response_df.empty:
return
offset += chunksize
yield response_df
except Exception as gbq_exp:
self.LOGGER.exception(str(gbq_exp))
raise DaoError(gbq_exp)
def save(self, payload: DataFrame):
"""
Save payload to database table.
Arguments:
payload {DataFrame} -- the payload to be stored in db
Raises:
DaoError: if cannot save payload to db
"""
try:
# Always append date column
# Todays date
date = datetime.today().strftime("%Y-%m-%d")
payload.insert(0, "date", date)
pandas_gbq.to_gbq(
payload,
self._tablename,
if_exists="append",
table_schema=self._table,
)
except Exception as sql_exp:
print(f"Exception in {self.tablename}")
raise DaoError(sql_exp)