def runsql(self):
"""Runs arbitrary sql and returns and html table"""
session = db.session()
limit = 1000
data = json.loads(request.form.get('data'))
sql = data.get('sql')
database_id = data.get('database_id')
mydb = session.query(models.Database).filter_by(id=database_id).first()
if (not self.appbuilder.sm.has_access('all_datasource_access',
'all_datasource_access')):
raise utils.CaravelSecurityException(
_("This view requires the `all_datasource_access` permission"))
content = ""
if mydb:
eng = mydb.get_sqla_engine()
if limit:
sql = sql.strip().strip(';')
qry = (select('*').select_from(
TextAsFrom(text(sql),
['*']).alias('inner_qry')).limit(limit))
sql = str(
qry.compile(eng, compile_kwargs={"literal_binds": True}))
try:
df = pd.read_sql_query(sql=sql, con=eng)
content = df.to_html(
index=False,
na_rep='',
classes=("dataframe table table-striped table-bordered "
"table-condensed sql_results").split(' '))
except Exception as e:
content = ('<div class="alert alert-danger">'
"{}</div>").format(e.message)
session.commit()
return content
def check_ownership(obj, raise_if_false=True):
"""Meant to be used in `pre_update` hooks on models to enforce ownership
Admin have all access, and other users need to be referenced on either
the created_by field that comes with the ``AuditMixin``, or in a field
named ``owners`` which is expected to be a one-to-many with the User
model. It is meant to be used in the ModelView's pre_update hook in
which raising will abort the update.
"""
roles = (r.name for r in get_user_roles())
if 'Admin' in roles:
return True
session = db.create_scoped_session()
orig_obj = session.query(obj.__class__).filter_by(id=obj.id).first()
owner_names = (user.username for user in orig_obj.owners)
if (
hasattr(orig_obj, 'created_by') and
orig_obj.created_by and
orig_obj.created_by.username == g.user.username):
return True
if hasattr(orig_obj, 'owners') and g.user.username in owner_names:
return True
if raise_if_false:
raise utils.CaravelSecurityException(
"You don't have the rights to alter [{}]".format(obj))
else:
return False
def sql_json(self):
"""Runs arbitrary sql and returns and json"""
session = db.session()
limit = 1000
sql = request.form.get('sql')
database_id = request.form.get('database_id')
mydb = session.query(models.Database).filter_by(id=database_id).first()
if not (self.can_access(
'all_datasource_access', 'all_datasource_access') or
self.can_access('database_access', mydb.perm)):
raise utils.CaravelSecurityException(_(
"SQL Lab requires the `all_datasource_access` or "
"specific DB permission"))
error_msg = ""
if not mydb:
error_msg = "The database selected doesn't seem to exist"
else:
eng = mydb.get_sqla_engine()
if limit:
sql = sql.strip().strip(';')
qry = (
select('*')
.select_from(TextAsFrom(text(sql), ['*'])
.alias('inner_qry'))
.limit(limit)
)
sql = '{}'.format(qry.compile(
eng, compile_kwargs={"literal_binds": True}))
try:
df = pd.read_sql_query(sql=sql, con=eng)
df = df.fillna(0) # TODO make sure NULL
except Exception as e:
logging.exception(e)
error_msg = utils.error_msg_from_exception(e)
session.commit()
if error_msg:
return Response(
json.dumps({
'error': error_msg,
}),
status=500,
mimetype="application/json")
else:
data = {
'columns': [c for c in df.columns],
'data': df.to_dict(orient='records'),
}
return json.dumps(
data, default=utils.json_int_dttm_ser, allow_nan=False)
