def all_as_pandas(self):
"""
Returns:
(pandas.DataFrame): The result of the query as a pandas DataFrame.
"""
return pd.read_sql(self.sqlalchemy_query.statement,
DatabaseContext.get_engine())
def query_raw(self, sql_statement):
"""
Execute a sql statement, attempt to convert the query
results to the appropriate ORM objects (passed to query constructor).
Args
sql_statement (str): A sql statement to execute
Returns:
(Iterable[Any]): An iterable of the query results.
"""
result_proxy = DatabaseContext.get_engine().execute(sql_statement)
return self.instances(result_proxy)
def parse_from_pandas(cls, df, relabel=None, if_exists='append'):
"""
Create instances from a pandas DataFrame. If the table does not
exist it will be created (see if_exists).
Args:
df (pandas.DataFrame): The data frame to be converted
to class instances. Column names must match the
column names in the ORM class. Will fail if the column
names do not match the names of the properties that
represent them in the ORM class.
relabel (Optional[Mapping[str, str]]):
A dictionary that maps pandas column names to names
of properties in the ORM. This is required if
the pandas column names differ from the property
names representing columns in this class.
if_exists (str): One of {'fail', 'replace', 'append'}, default 'append'.
How to behave if the table already exists.
fail: Raise a ValueError.
replace: Drop the table before inserting new values.
append: Insert new values to the existing table.
Returns:
(List[BigQueryCRUDMixin]): Returns a list
of class instances.
"""
if relabel is None:
relabel = {}
instances = []
def method(sqlalchemy_table, conn, keys, data_iter):
"""
Args:
sqlalchemy_table (): Ignored
conn (): Ignored
keys (Tuple): The column names.
data_iter (Iterable[Tuple]): An iterable
iterating over column values. Matches keys.
"""
keys = tuple(relabel.get(key, key) for key in keys)
for params in data_iter:
instances.append(cls(**dict(zip(keys, params))))
table_name = cls.__tablename__
df.to_sql(
name=table_name,
con=DatabaseContext.get_engine(),
if_exists=if_exists,
index=False,
method=method,
)
return instances
def create_from_query(cls, query, flatten_results=True):
"""
Load instances through a query job.
The job is asynchronous but this function will wait for the job to complete.
See https://cloud.google.com/bigquery/docs/writing-results
Note that this method must compile the sql query to a string.
It does so using sqlalchemy_query.statement.compile(compile_kwargs={"literal_binds": True}).
This will fail for certain queries and should not be used for queries which depend on untrusted input.
See https://docs.sqlalchemy.org/en/13/faq/sqlexpressions.html for more information.
Args:
query (BigQueryQuery): A query object whose results are
to be appended to the table.
flatten_results (Optional[bool]): If True, will flatten the query results.
Defaults to True.
"""
client = DatabaseContext.get_session().connection().connection._client
table_ref = _get_table_ref(cls.__table__.name, client)
job_config = bigquery_job.QueryJobConfig(
destination=table_ref,
create_disposition=bigquery_job.CreateDisposition.CREATE_NEVER,
write_disposition=bigquery_job.WriteDisposition.WRITE_APPEND,
flatten_results=flatten_results,
allow_large_results=not flatten_results,
)
dialect = DatabaseContext.get_engine().dialect
compiled_sql = query.sqlalchemy_query.statement.compile(
dialect=dialect, compile_kwargs={
'literal_binds': True,
})
raw_sql = str(compiled_sql)
query_job = client.query(raw_sql, job_config=job_config)
try:
query_job.result()
except Exception as e:
raise exceptions.DatabaseError('{}\n{}\n{}'.format(
query_job.errors,
'{}({})'.format(type(e), e),
query_job.error_result,
))
if ((query_job.error_result and len(query_job.error_result) > 0)
or (query_job.errors and len(query_job.errors) > 0)):
raise exceptions.DatabaseError('{}\n{}'.format(
query_job.errors, query_job.error_result))
def table_create(cls):
"""
Creates the table corresponding to this class
"""
engine = DatabaseContext.get_engine()
cls.__table__.create(engine)
def table_delete(cls):
"""
Deletes the table corresponding to this class
"""
engine = DatabaseContext.get_engine()
cls.__table__.drop(engine)