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)