def runsql(self):
     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()
     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"))
         except Exception as e:
             content = (
                 '<div class="alert alert-danger">'
                 "{}</div>"
             ).format(e.message)
     session.commit()
     return content

    def dashboard(self, dashboard_id):
        session = db.session()
        qry = session.query(models.Dashboard)
        if dashboard_id.isdigit():
            qry = qry.filter_by(id=int(dashboard_id))
        else:
            qry = qry.filter_by(slug=dashboard_id)

        templates = session.query(models.CssTemplate).all()

        dash = qry.first()

        # Hack to log the dashboard_id properly, even when getting a slug
        @utils.log_this
        def dashboard(**kwargs):
            pass
        dashboard(dashboard_id=dash.id)

        pos_dict = {}
        if dash.position_json:
            pos_dict = {
                int(o['slice_id']):o
                for o in json.loads(dash.position_json)}
        return self.render_template(
            "panoramix/dashboard.html", dashboard=dash,
            templates=templates,
            pos_dict=pos_dict)

 def refresh_datasources(self):
     session = db.session()
     for cluster in session.query(models.Cluster).all():
         cluster.refresh_datasources()
         cluster.metadata_last_refreshed = datetime.now()
         flash(
             "Refreshed metadata from cluster "
             "[" + cluster.cluster_name + "]",
             'info')
     session.commit()
     return redirect("/datasourcemodelview/list/")

 def featured_datasets(self):
     session = db.session()
     datasets_sqla = (session.query(models.SqlaTable)
                                     .filter_by(is_featured=True).all())
     datasets_druid = (session.query(models.DruidDatasource)
                                      .filter_by(is_featured=True).all())
     featured_datasets = datasets_sqla + datasets_druid
     return self.render_template(
         'panoramix/featured_datasets.html',
         featured_datasets=featured_datasets,
         utils=utils)

 def refresh_datasources(self):
     session = db.session()
     for cluster in session.query(models.Cluster).all():
         try:
             cluster.refresh_datasources()
         except Exception as e:
             flash("Error while processing cluster '{}'".format(cluster), "alert")
             return redirect("/clustermodelview/list/")
         cluster.metadata_last_refreshed = datetime.now()
         flash("Refreshed metadata from cluster " "[" + cluster.cluster_name + "]", "info")
     session.commit()
     return redirect("/datasourcemodelview/list/")

    def dashboard(self, identifier):
        session = db.session()
        qry = session.query(models.Dashboard)
        if identifier.isdigit():
            qry = qry.filter_by(id=int(identifier))
        else:
            qry = qry.filter_by(slug=identifier)

        dashboard = qry.first()
        pos_dict = {}
        if dashboard.position_json:
            pos_dict = {int(o["slice_id"]): o for o in json.loads(dashboard.position_json)}
        return self.render_template("panoramix/dashboard.html", dashboard=dashboard, pos_dict=pos_dict)

 def save_dash(self, dashboard_id):
     data = json.loads(request.form.get('data'))
     positions = data['positions']
     slice_ids = [int(d['slice_id']) for d in positions]
     session = db.session()
     Dash = models.Dashboard
     dash = session.query(Dash).filter_by(id=dashboard_id).first()
     dash.slices = [o for o in dash.slices if o.id in slice_ids]
     dash.position_json = json.dumps(data['positions'], indent=4)
     dash.css = data['css']
     session.merge(dash)
     session.commit()
     session.close()
     return "SUCCESS"

def init():
    """
    Inits the Panoramix application with security roles and such
    """
    from panoramix import appbuilder
    from panoramix import models
    from flask_appbuilder.security.sqla import models as ab_models
    sm = appbuilder.sm
    alpha = sm.add_role("Alpha")
    admin = sm.add_role("Admin")

    merge_perm(sm, 'all_datasource_access', 'all_datasource_access')

    perms = db.session.query(ab_models.PermissionView).all()
    for perm in perms:
        if perm.permission.name == 'datasource_access':
            continue
        if perm.view_menu.name not in (
                'UserDBModelView', 'RoleModelView', 'ResetPasswordView',
                'Security'):
            sm.add_permission_role(alpha, perm)
        sm.add_permission_role(admin, perm)
    gamma = sm.add_role("Gamma")
    for perm in perms:
        s = perm.permission.name
        if(
                perm.view_menu.name not in (
                    'ResetPasswordView',
                    'RoleModelView',
                    'UserDBModelView',
                    'Security') and
                perm.permission.name not in (
                    'all_datasource_access',
                    'can_add',
                    'can_download',
                    'can_delete',
                    'can_edit',
                    'can_save',
                    'datasource_access',
                    'muldelete',
                )):
            sm.add_permission_role(gamma, perm)
    session = db.session()
    table_perms = [
            table.perm for table in session.query(models.SqlaTable).all()]
    table_perms += [
            table.perm for table in session.query(models.DruidDatasource).all()]
    for table_perm in table_perms:
        merge_perm(sm, 'datasource_access', table.perm)

 def dashboard(self, id_):
     session = db.session()
     dashboard = (
         session
         .query(models.Dashboard)
         .filter(models.Dashboard.id == id_)
         .first()
     )
     pos_dict = {}
     if dashboard.position_json:
         pos_dict = {
             int(o['slice_id']):o for o in json.loads(dashboard.position_json)}
     return self.render_template(
         "panoramix/dashboard.html", dashboard=dashboard,
         pos_dict=pos_dict)

 def refresh_datasources(self):
     session = db.session()
     for cluster in session.query(models.DruidCluster).all():
         try:
             cluster.refresh_datasources()
         except Exception as e:
             flash(
                 "Error while processing cluster '{}'\n{}".format(
                     cluster, str(e)),
                 "danger")
             logging.exception(e)
             return redirect('/druidclustermodelview/list/')
         cluster.metadata_last_refreshed = datetime.now()
         flash(
             "Refreshed metadata from cluster "
             "[" + cluster.cluster_name + "]",
             'info')
     session.commit()
     return redirect("/datasourcemodelview/list/")

    def datasource(self, datasource_type, datasource_id):
        if datasource_type == "table":
            datasource = (
                db.session
                .query(models.SqlaTable)
                .filter_by(id=datasource_id)
                .first()
            )
        else:
            datasource = (
                db.session
                .query(models.Datasource)
                .filter_by(id=datasource_id)
                .first()
            )

            all_datasource_access = self.appbuilder.sm.has_access(
                'all_datasource_access', 'all_datasource_access')
            datasource_access = self.appbuilder.sm.has_access(
                'datasource_access', datasource.perm)
            if not (all_datasource_access or datasource_access):
                flash(
                    "You don't seem to have access to this datasource",
                    "danger")
                return redirect('/slicemodelview/list/')
        action = request.args.get('action')
        if action == 'save':
            session = db.session()

            # TODO use form processing form wtforms
            d = request.args.to_dict(flat=False)
            del d['action']
            del d['previous_viz_type']
            as_list = ('metrics', 'groupby')
            for k in d:
                v = d.get(k)
                if k in as_list and not isinstance(v, list):
                    d[k] = [v] if v else []
                if k not in as_list and isinstance(v, list):
                    d[k] = v[0]

            table_id = druid_datasource_id = None
            datasource_type = request.args.get('datasource_type')
            if datasource_type in ('datasource', 'druid'):
                druid_datasource_id = request.args.get('datasource_id')
            elif datasource_type == 'table':
                table_id = request.args.get('datasource_id')

            slice_name = request.args.get('slice_name')

            obj = models.Slice(
                params=json.dumps(d, indent=4, sort_keys=True),
                viz_type=request.args.get('viz_type'),
                datasource_name=request.args.get('datasource_name'),
                druid_datasource_id=druid_datasource_id,
                table_id=table_id,
                datasource_type=datasource_type,
                slice_name=slice_name,
            )
            session.add(obj)
            session.commit()
            flash("Slice <{}> has been added to the pie".format(slice_name), "info")
            return redirect(obj.slice_url)


        if not datasource:
            flash("The datasource seem to have been deleted", "alert")
        viz_type = request.args.get("viz_type")
        if not viz_type and datasource.default_endpoint:
            return redirect(datasource.default_endpoint)
        if not viz_type:
            viz_type = "table"
        obj = viz.viz_types[viz_type](
            datasource,
            form_data=request.args)
        if request.args.get("json") == "true":
            if config.get("DEBUG"):
                payload = obj.get_json()
            try:
                payload = obj.get_json()
                status=200
            except Exception as e:
                logging.exception(e)
                payload = str(e)
                status=500
            return Response(
                payload,
                status=status,
                mimetype="application/json")
        else:
            if config.get("DEBUG"):
                resp = self.render_template("panoramix/viz.html", viz=obj)
            try:
                resp = self.render_template("panoramix/viz.html", viz=obj)
            except Exception as e:
                if config.get("DEBUG"):
                    raise(e)
                return Response(
                    str(e),
                    status=500,
#.........这里部分代码省略.........

    def explore(self, datasource_type, datasource_id):
        if datasource_type == "table":
            datasource = db.session.query(models.SqlaTable).filter_by(id=datasource_id).first()
        else:
            datasource = db.session.query(models.Datasource).filter_by(id=datasource_id).first()

            all_datasource_access = self.appbuilder.sm.has_access("all_datasource_access", "all_datasource_access")
            datasource_access = self.appbuilder.sm.has_access("datasource_access", datasource.perm)
            if not (all_datasource_access or datasource_access):
                flash("You don't seem to have access to this datasource", "danger")
                return redirect("/slicemodelview/list/")
        action = request.args.get("action")
        if action in ("save", "overwrite"):
            session = db.session()

            # TODO use form processing form wtforms
            d = request.args.to_dict(flat=False)
            del d["action"]
            del d["previous_viz_type"]
            as_list = ("metrics", "groupby", "columns")
            for k in d:
                v = d.get(k)
                if k in as_list and not isinstance(v, list):
                    d[k] = [v] if v else []
                if k not in as_list and isinstance(v, list):
                    d[k] = v[0]

            table_id = druid_datasource_id = None
            datasource_type = request.args.get("datasource_type")
            if datasource_type in ("datasource", "druid"):
                druid_datasource_id = request.args.get("datasource_id")
            elif datasource_type == "table":
                table_id = request.args.get("datasource_id")

            slice_name = request.args.get("slice_name")

            if action == "save":
                slc = models.Slice()
                msg = "Slice [{}] has been saved".format(slice_name)
            elif action == "overwrite":
                slc = session.query(models.Slice).filter_by(id=request.args.get("slice_id")).first()
                msg = "Slice [{}] has been overwritten".format(slice_name)

            slc.params = json.dumps(d, indent=4, sort_keys=True)
            slc.datasource_name = request.args.get("datasource_name")
            slc.viz_type = request.args.get("viz_type")
            slc.druid_datasource_id = druid_datasource_id
            slc.table_id = table_id
            slc.datasource_type = datasource_type
            slc.slice_name = slice_name

            session.merge(slc)
            session.commit()
            flash(msg, "info")
            return redirect(slc.slice_url)

        if not datasource:
            flash("The datasource seem to have been deleted", "alert")
        viz_type = request.args.get("viz_type")
        if not viz_type and datasource.default_endpoint:
            return redirect(datasource.default_endpoint)
        if not viz_type:
            viz_type = "table"
        obj = viz.viz_types[viz_type](datasource, form_data=request.args)
        if request.args.get("json") == "true":
            status = 200
            if config.get("DEBUG"):
                payload = obj.get_json()
            else:
                try:
                    payload = obj.get_json()
                except Exception as e:
                    logging.exception(e)
                    payload = str(e)
                    status = 500
            return Response(payload, status=status, mimetype="application/json")
        else:
            if config.get("DEBUG"):
                resp = self.render_template("panoramix/viz.html", viz=obj)
            try:
                resp = self.render_template("panoramix/viz.html", viz=obj)
            except Exception as e:
                if config.get("DEBUG"):
                    raise (e)
                return Response(str(e), status=500, mimetype="application/json")
            return resp