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)