def insert_saved_query(
self,
label: str,
sql: str,
db_id: Optional[int] = None,
created_by=None,
schema: Optional[str] = "",
description: Optional[str] = "",
) -> SavedQuery:
database = None
if db_id:
database = db.session.query(Database).get(db_id)
query = SavedQuery(
database=database,
created_by=created_by,
sql=sql,
label=label,
schema=schema,
description=description,
)
db.session.add(query)
db.session.commit()
return query
def test_sql_json_to_saved_query_info(self):
"""
SQLLab: Test SQLLab query execution info propagation to saved queries
"""
from freezegun import freeze_time
self.login("admin")
sql_statement = "SELECT * FROM birth_names LIMIT 10"
examples_db_id = get_example_database().id
saved_query = SavedQuery(db_id=examples_db_id, sql=sql_statement)
db.session.add(saved_query)
db.session.commit()
with freeze_time("2020-01-01T00:00:00Z"):
self.run_sql(sql_statement, "1")
saved_query_ = (db.session.query(SavedQuery).filter(
SavedQuery.db_id == examples_db_id,
SavedQuery.sql == sql_statement).one_or_none())
assert saved_query_.rows is not None
assert saved_query_.last_run == datetime.now()
# Rollback changes
db.session.delete(saved_query_)
db.session.commit()
def session_with_data(session: Session) -> Iterator[Session]:
from superset.columns.models import Column
from superset.connectors.sqla.models import SqlaTable, TableColumn
from superset.datasets.models import Dataset
from superset.models.core import Database
from superset.models.sql_lab import Query, SavedQuery
from superset.tables.models import Table
engine = session.get_bind()
SqlaTable.metadata.create_all(engine) # pylint: disable=no-member
db = Database(database_name="my_database", sqlalchemy_uri="sqlite://")
columns = [
TableColumn(column_name="a", type="INTEGER"),
]
sqla_table = SqlaTable(
table_name="my_sqla_table",
columns=columns,
metrics=[],
database=db,
)
query_obj = Query(
client_id="foo",
database=db,
tab_name="test_tab",
sql_editor_id="test_editor_id",
sql="select * from bar",
select_sql="select * from bar",
executed_sql="select * from bar",
limit=100,
select_as_cta=False,
rows=100,
error_message="none",
results_key="abc",
)
saved_query = SavedQuery(database=db, sql="select * from foo")
table = Table(
name="my_table",
schema="my_schema",
catalog="my_catalog",
database=db,
columns=[],
)
dataset = Dataset(
database=table.database,
name="positions",
expression="""
SELECT array_agg(array[longitude,latitude]) AS position
FROM my_catalog.my_schema.my_table
""",
tables=[table],
columns=[
Column(
name="position",
expression="array_agg(array[longitude,latitude])",
),
],
)
session.add(dataset)
session.add(table)
session.add(saved_query)
session.add(query_obj)
session.add(db)
session.add(sqla_table)
session.flush()
yield session
def pre_add(self, item: SavedQuery) -> None:
item.user = g.user
