def run(self) -> Model: self.validate() try: # Test connection before starting create transaction TestConnectionDatabaseCommand(self._properties).run() except Exception as ex: event_logger.log_with_context( action=f"db_creation_failed.{ex.__class__.__name__}", engine=self._properties.get("sqlalchemy_uri", "").split(":")[0], ) raise DatabaseConnectionFailedError() from ex try: database = DatabaseDAO.create(self._properties, commit=False) database.set_sqlalchemy_uri(database.sqlalchemy_uri) # adding a new database we always want to force refresh schema list schemas = database.get_all_schema_names(cache=False) for schema in schemas: security_manager.add_permission_view_menu( "schema_access", security_manager.get_schema_perm(database, schema) ) db.session.commit() except DAOCreateFailedError as ex: db.session.rollback() event_logger.log_with_context( action=f"db_creation_failed.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseCreateFailedError() from ex return database
def run(self) -> Model: self.validate() try: database = DatabaseDAO.create(self._properties, commit=False) database.set_sqlalchemy_uri(database.sqlalchemy_uri) try: TestConnectionDatabaseCommand(self._actor, self._properties).run() except Exception as ex: # pylint: disable=broad-except db.session.rollback() event_logger.log_with_context( action=f"db_creation_failed.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseConnectionFailedError() # adding a new database we always want to force refresh schema list schemas = database.get_all_schema_names(cache=False) for schema in schemas: security_manager.add_permission_view_menu( "schema_access", security_manager.get_schema_perm(database, schema)) security_manager.add_permission_view_menu("database_access", database.perm) db.session.commit() except DAOCreateFailedError as ex: event_logger.log_with_context( action=f"db_creation_failed.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseCreateFailedError() return database
def run(self) -> None: self.validate() uri = self._properties.get("sqlalchemy_uri", "") if self._model and uri == self._model.safe_sqlalchemy_uri(): uri = self._model.sqlalchemy_uri_decrypted try: database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=self._properties.get("encrypted_extra", "{}"), ) database.set_sqlalchemy_uri(uri) database.db_engine_spec.mutate_db_for_connection_test(database) username = self._actor.username if self._actor is not None else None engine = database.get_sqla_engine(user_name=username) with closing(engine.raw_connection()) as conn: if not engine.dialect.do_ping(conn): raise DBAPIError(None, None, None) # Log succesful connection test with engine event_logger.log_with_context( action="test_connection_success", engine=database.db_engine_spec.__name__, ) except (NoSuchModuleError, ModuleNotFoundError) as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseTestConnectionDriverError( message=_("Could not load database driver: {}").format( database.db_engine_spec.__name__), ) except DBAPIError as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) # check if we have connectivity to the host, and if the port is open self._diagnose(uri) raise DatabaseTestConnectionFailedError() except SupersetSecurityException as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseSecurityUnsafeError(message=str(ex)) except Exception as ex: # pylint: disable=broad-except event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseTestConnectionUnexpectedError(str(ex))
def validate(self) -> None: exceptions: List[ValidationError] = [] sqlalchemy_uri: Optional[str] = self._properties.get("sqlalchemy_uri") database_name: Optional[str] = self._properties.get("database_name") if not sqlalchemy_uri: exceptions.append(DatabaseRequiredFieldValidationError("sqlalchemy_uri")) if not database_name: exceptions.append(DatabaseRequiredFieldValidationError("database_name")) else: # Check database_name uniqueness if not DatabaseDAO.validate_uniqueness(database_name): exceptions.append(DatabaseExistsValidationError()) if exceptions: exception = DatabaseInvalidError() exception.add_list(exceptions) event_logger.log_with_context( action=f"db_connection_failed.{exception.__class__.__name__}" ) raise exception
def form_post( # pylint: disable=too-many-locals self, form: ColumnarToDatabaseForm ) -> Response: database = form.con.data columnar_table = Table(table=form.name.data, schema=form.schema.data) files = form.columnar_file.data file_type = {file.filename.split(".")[-1] for file in files} if file_type == {"zip"}: zipfile_ob = zipfile.ZipFile( # pylint: disable=consider-using-with form.columnar_file.data[0] ) # pylint: disable=consider-using-with file_type = {filename.split(".")[-1] for filename in zipfile_ob.namelist()} files = [ io.BytesIO((zipfile_ob.open(filename).read(), filename)[0]) for filename in zipfile_ob.namelist() ] if len(file_type) > 1: message = _( "Multiple file extensions are not allowed for columnar uploads." " Please make sure all files are of the same extension.", ) flash(message, "danger") return redirect("/columnartodatabaseview/form") read = pd.read_parquet kwargs = { "columns": form.usecols.data if form.usecols.data else None, } if not schema_allows_file_upload(database, columnar_table.schema): message = _( 'Database "%(database_name)s" schema "%(schema_name)s" ' "is not allowed for columnar uploads. " "Please contact your Superset Admin.", database_name=database.database_name, schema_name=columnar_table.schema, ) flash(message, "danger") return redirect("/columnartodatabaseview/form") try: chunks = [read(file, **kwargs) for file in files] df = pd.concat(chunks) database = ( db.session.query(models.Database) .filter_by(id=form.data.get("con").data.get("id")) .one() ) database.db_engine_spec.df_to_sql( database, columnar_table, df, to_sql_kwargs={ "chunksize": 1000, "if_exists": form.if_exists.data, "index": form.index.data, "index_label": form.index_label.data, }, ) # Connect table to the database that should be used for exploration. # E.g. if hive was used to upload a csv, presto will be a better option # to explore the table. expore_database = database explore_database_id = database.explore_database_id if explore_database_id: expore_database = ( db.session.query(models.Database) .filter_by(id=explore_database_id) .one_or_none() or database ) sqla_table = ( db.session.query(SqlaTable) .filter_by( table_name=columnar_table.table, schema=columnar_table.schema, database_id=expore_database.id, ) .one_or_none() ) if sqla_table: sqla_table.fetch_metadata() if not sqla_table: sqla_table = SqlaTable(table_name=columnar_table.table) sqla_table.database = expore_database sqla_table.database_id = database.id sqla_table.owners = [g.user] sqla_table.schema = columnar_table.schema sqla_table.fetch_metadata() db.session.add(sqla_table) db.session.commit() except Exception as ex: # pylint: disable=broad-except db.session.rollback() message = _( 'Unable to upload Columnar file "%(filename)s" to table ' '"%(table_name)s" in database "%(db_name)s". ' "Error message: %(error_msg)s", filename=[file.filename for file in form.columnar_file.data], table_name=form.name.data, db_name=database.database_name, error_msg=str(ex), ) flash(message, "danger") stats_logger.incr("failed_columnar_upload") return redirect("/columnartodatabaseview/form") # Go back to welcome page / splash screen message = _( 'Columnar file "%(columnar_filename)s" uploaded to table "%(table_name)s" ' 'in database "%(db_name)s"', columnar_filename=[file.filename for file in form.columnar_file.data], table_name=str(columnar_table), db_name=sqla_table.database.database_name, ) flash(message, "info") event_logger.log_with_context( action="successful_columnar_upload", database=form.con.data.name, schema=form.schema.data, table=form.name.data, ) return redirect("/tablemodelview/list/")
def form_post(self, form: ExcelToDatabaseForm) -> Response: database = form.con.data excel_table = Table(table=form.name.data, schema=form.schema.data) if not schema_allows_file_upload(database, excel_table.schema): message = _( 'Database "%(database_name)s" schema "%(schema_name)s" ' "is not allowed for excel uploads. Please contact your Superset Admin.", database_name=database.database_name, schema_name=excel_table.schema, ) flash(message, "danger") return redirect("/exceltodatabaseview/form") uploaded_tmp_file_path = tempfile.NamedTemporaryFile( # pylint: disable=consider-using-with dir=app.config["UPLOAD_FOLDER"], suffix=os.path.splitext(form.excel_file.data.filename)[1].lower(), delete=False, ).name try: utils.ensure_path_exists(config["UPLOAD_FOLDER"]) upload_stream_write(form.excel_file.data, uploaded_tmp_file_path) df = pd.read_excel( header=form.header.data if form.header.data else 0, index_col=form.index_col.data, io=form.excel_file.data, keep_default_na=not form.null_values.data, mangle_dupe_cols=form.mangle_dupe_cols.data, na_values=form.null_values.data if form.null_values.data else None, parse_dates=form.parse_dates.data, skiprows=form.skiprows.data, sheet_name=form.sheet_name.data if form.sheet_name.data else 0, ) database = ( db.session.query(models.Database) .filter_by(id=form.data.get("con").data.get("id")) .one() ) database.db_engine_spec.df_to_sql( database, excel_table, df, to_sql_kwargs={ "chunksize": 1000, "if_exists": form.if_exists.data, "index": form.index.data, "index_label": form.index_label.data, }, ) # Connect table to the database that should be used for exploration. # E.g. if hive was used to upload a excel, presto will be a better option # to explore the table. expore_database = database explore_database_id = database.explore_database_id if explore_database_id: expore_database = ( db.session.query(models.Database) .filter_by(id=explore_database_id) .one_or_none() or database ) sqla_table = ( db.session.query(SqlaTable) .filter_by( table_name=excel_table.table, schema=excel_table.schema, database_id=expore_database.id, ) .one_or_none() ) if sqla_table: sqla_table.fetch_metadata() if not sqla_table: sqla_table = SqlaTable(table_name=excel_table.table) sqla_table.database = expore_database sqla_table.database_id = database.id sqla_table.owners = [g.user] sqla_table.schema = excel_table.schema sqla_table.fetch_metadata() db.session.add(sqla_table) db.session.commit() except Exception as ex: # pylint: disable=broad-except db.session.rollback() message = _( 'Unable to upload Excel file "%(filename)s" to table ' '"%(table_name)s" in database "%(db_name)s". ' "Error message: %(error_msg)s", filename=form.excel_file.data.filename, table_name=form.name.data, db_name=database.database_name, error_msg=str(ex), ) flash(message, "danger") stats_logger.incr("failed_excel_upload") return redirect("/exceltodatabaseview/form") # Go back to welcome page / splash screen message = _( 'Excel file "%(excel_filename)s" uploaded to table "%(table_name)s" in ' 'database "%(db_name)s"', excel_filename=form.excel_file.data.filename, table_name=str(excel_table), db_name=sqla_table.database.database_name, ) flash(message, "info") event_logger.log_with_context( action="successful_excel_upload", database=form.con.data.name, schema=form.schema.data, table=form.name.data, ) return redirect("/tablemodelview/list/")
def form_post(self, form: CsvToDatabaseForm) -> Response: database = form.con.data csv_table = Table(table=form.name.data, schema=form.schema.data) if not schema_allows_file_upload(database, csv_table.schema): message = _( 'Database "%(database_name)s" schema "%(schema_name)s" ' "is not allowed for csv uploads. Please contact your Superset Admin.", database_name=database.database_name, schema_name=csv_table.schema, ) flash(message, "danger") return redirect("/csvtodatabaseview/form") try: df = pd.concat( pd.read_csv( chunksize=1000, encoding="utf-8", filepath_or_buffer=form.csv_file.data, header=form.header.data if form.header.data else 0, index_col=form.index_col.data, infer_datetime_format=form.infer_datetime_format.data, iterator=True, keep_default_na=not form.null_values.data, mangle_dupe_cols=form.mangle_dupe_cols.data, usecols=form.usecols.data if form.usecols.data else None, na_values=form.null_values.data if form.null_values.data else None, nrows=form.nrows.data, parse_dates=form.parse_dates.data, sep=form.sep.data, skip_blank_lines=form.skip_blank_lines.data, skipinitialspace=form.skipinitialspace.data, skiprows=form.skiprows.data, ) ) database = ( db.session.query(models.Database) .filter_by(id=form.data.get("con").data.get("id")) .one() ) database.db_engine_spec.df_to_sql( database, csv_table, df, to_sql_kwargs={ "chunksize": 1000, "if_exists": form.if_exists.data, "index": form.index.data, "index_label": form.index_label.data, }, ) # Connect table to the database that should be used for exploration. # E.g. if hive was used to upload a csv, presto will be a better option # to explore the table. expore_database = database explore_database_id = database.explore_database_id if explore_database_id: expore_database = ( db.session.query(models.Database) .filter_by(id=explore_database_id) .one_or_none() or database ) sqla_table = ( db.session.query(SqlaTable) .filter_by( table_name=csv_table.table, schema=csv_table.schema, database_id=expore_database.id, ) .one_or_none() ) if sqla_table: sqla_table.fetch_metadata() if not sqla_table: sqla_table = SqlaTable(table_name=csv_table.table) sqla_table.database = expore_database sqla_table.database_id = database.id sqla_table.owners = [g.user] sqla_table.schema = csv_table.schema sqla_table.fetch_metadata() db.session.add(sqla_table) db.session.commit() except Exception as ex: # pylint: disable=broad-except db.session.rollback() message = _( 'Unable to upload CSV file "%(filename)s" to table ' '"%(table_name)s" in database "%(db_name)s". ' "Error message: %(error_msg)s", filename=form.csv_file.data.filename, table_name=form.name.data, db_name=database.database_name, error_msg=str(ex), ) flash(message, "danger") stats_logger.incr("failed_csv_upload") return redirect("/csvtodatabaseview/form") # Go back to welcome page / splash screen message = _( 'CSV file "%(csv_filename)s" uploaded to table "%(table_name)s" in ' 'database "%(db_name)s"', csv_filename=form.csv_file.data.filename, table_name=str(csv_table), db_name=sqla_table.database.database_name, ) flash(message, "info") event_logger.log_with_context( action="successful_csv_upload", database=form.con.data.name, schema=form.schema.data, table=form.name.data, ) return redirect("/tablemodelview/list/")
def run(self) -> None: self.validate() uri = self._properties.get("sqlalchemy_uri", "") if self._model and uri == self._model.safe_sqlalchemy_uri(): uri = self._model.sqlalchemy_uri_decrypted # context for error messages url = make_url(uri) context = { "hostname": url.host, "password": url.password, "port": url.port, "username": url.username, "database": url.database, } try: database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=self._properties.get("encrypted_extra", "{}"), ) database.set_sqlalchemy_uri(uri) database.db_engine_spec.mutate_db_for_connection_test(database) username = self._actor.username if self._actor is not None else None engine = database.get_sqla_engine(user_name=username) event_logger.log_with_context( action="test_connection_attempt", engine=database.db_engine_spec.__name__, ) with closing(engine.raw_connection()) as conn: try: alive = func_timeout( int(app.config["TEST_DATABASE_CONNECTION_TIMEOUT"]. total_seconds()), engine.dialect.do_ping, args=(conn, ), ) except sqlite3.ProgrammingError: # SQLite can't run on a separate thread, so ``func_timeout`` fails alive = engine.dialect.do_ping(conn) except FunctionTimedOut as ex: raise SupersetTimeoutException( error_type=SupersetErrorType. CONNECTION_DATABASE_TIMEOUT, message= ("Please check your connection details and database settings, " "and ensure that your database is accepting connections, " "then try connecting again."), level=ErrorLevel.ERROR, extra={"sqlalchemy_uri": database.sqlalchemy_uri}, ) from ex except Exception: # pylint: disable=broad-except alive = False if not alive: raise DBAPIError(None, None, None) # Log succesful connection test with engine event_logger.log_with_context( action="test_connection_success", engine=database.db_engine_spec.__name__, ) except (NoSuchModuleError, ModuleNotFoundError) as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseTestConnectionDriverError( message=_("Could not load database driver: {}").format( database.db_engine_spec.__name__), ) from ex except DBAPIError as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) # check for custom errors (wrong username, wrong password, etc) errors = database.db_engine_spec.extract_errors(ex, context) raise DatabaseTestConnectionFailedError(errors) from ex except SupersetSecurityException as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseSecurityUnsafeError(message=str(ex)) from ex except SupersetTimeoutException as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) # bubble up the exception to return a 408 raise ex except Exception as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) errors = database.db_engine_spec.extract_errors(ex, context) raise DatabaseTestConnectionUnexpectedError(errors) from ex
def put(self, pk: int) -> Response: """Updates an Report Schedule --- put: description: >- Updates a Report Schedule parameters: - in: path schema: type: integer name: pk description: The Report Schedule pk requestBody: description: Report Schedule schema required: true content: application/json: schema: $ref: '#/components/schemas/{{self.__class__.__name__}}.put' responses: 200: description: Report Schedule changed content: application/json: schema: type: object properties: id: type: number result: $ref: '#/components/schemas/{{self.__class__.__name__}}.put' 400: $ref: '#/components/responses/400' 401: $ref: '#/components/responses/401' 403: $ref: '#/components/responses/403' 404: $ref: '#/components/responses/404' 422: $ref: '#/components/responses/422' 500: $ref: '#/components/responses/500' """ try: item = self.edit_model_schema.load(request.json) # normally this would be covered by a decorator, however # due to this model being formatted incorrectly the data # needed some manipulation. event_logger.log_with_context( action="ReportScheduleRestApi.put", dashboard_id=request.json.get("dashboard", None), chart_id=request.json.get("chart", None), report_format=request.json.get("report_format", None), active=request.json.get("active", None), ) # This validates custom Schema with custom validations except ValidationError as error: return self.response_400(message=error.messages) try: new_model = UpdateReportScheduleCommand(g.user, pk, item).run() return self.response(200, id=new_model.id, result=item) except ReportScheduleNotFoundError: return self.response_404() except ReportScheduleInvalidError as ex: return self.response_422(message=ex.normalized_messages()) except ReportScheduleForbiddenError: return self.response_403() except ReportScheduleUpdateFailedError as ex: logger.error( "Error updating report %s: %s", self.__class__.__name__, str(ex), exc_info=True, ) return self.response_422(message=str(ex))
def run(self) -> None: self.validate() uri = self._properties.get("sqlalchemy_uri", "") if self._model and uri == self._model.safe_sqlalchemy_uri(): uri = self._model.sqlalchemy_uri_decrypted # context for error messages url = make_url(uri) context = { "hostname": url.host, "password": url.password, "port": url.port, "username": url.username, "database": url.database, } try: database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=self._properties.get("encrypted_extra", "{}"), ) database.set_sqlalchemy_uri(uri) database.db_engine_spec.mutate_db_for_connection_test(database) username = self._actor.username if self._actor is not None else None engine = database.get_sqla_engine(user_name=username) event_logger.log_with_context( action="test_connection_attempt", engine=database.db_engine_spec.__name__, ) with closing(engine.raw_connection()) as conn: try: alive = engine.dialect.do_ping(conn) except Exception: # pylint: disable=broad-except alive = False if not alive: raise DBAPIError(None, None, None) # Log succesful connection test with engine event_logger.log_with_context( action="test_connection_success", engine=database.db_engine_spec.__name__, ) except (NoSuchModuleError, ModuleNotFoundError) as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseTestConnectionDriverError( message=_("Could not load database driver: {}").format( database.db_engine_spec.__name__), ) from ex except DBAPIError as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) # check for custom errors (wrong username, wrong password, etc) errors = database.db_engine_spec.extract_errors(ex, context) raise DatabaseTestConnectionFailedError(errors) from ex except SupersetSecurityException as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) raise DatabaseSecurityUnsafeError(message=str(ex)) from ex except Exception as ex: event_logger.log_with_context( action=f"test_connection_error.{ex.__class__.__name__}", engine=database.db_engine_spec.__name__, ) errors = database.db_engine_spec.extract_errors(ex, context) raise DatabaseTestConnectionUnexpectedError(errors) from ex
def run(self) -> None: engine = self._properties["engine"] engine_specs = get_engine_specs() if engine in BYPASS_VALIDATION_ENGINES: # Skip engines that are only validated onCreate return if engine not in engine_specs: raise InvalidEngineError( SupersetError( message=__( 'Engine "%(engine)s" is not a valid engine.', engine=engine, ), error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR, level=ErrorLevel.ERROR, extra={ "allowed": list(engine_specs), "provided": engine }, ), ) engine_spec = engine_specs[engine] if not hasattr(engine_spec, "parameters_schema"): raise InvalidEngineError( SupersetError( message=__( 'Engine "%(engine)s" cannot be configured through parameters.', engine=engine, ), error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR, level=ErrorLevel.ERROR, extra={ "allowed": [ name for name, engine_spec in engine_specs.items() if issubclass(engine_spec, BasicParametersMixin) ], "provided": engine, }, ), ) # perform initial validation errors = engine_spec.validate_parameters( # type: ignore self._properties.get("parameters", {})) if errors: event_logger.log_with_context(action="validation_error", engine=engine) raise InvalidParametersError(errors) serialized_encrypted_extra = self._properties.get( "encrypted_extra", "{}") try: encrypted_extra = json.loads(serialized_encrypted_extra) except json.decoder.JSONDecodeError: encrypted_extra = {} # try to connect sqlalchemy_uri = engine_spec.build_sqlalchemy_uri( # type: ignore self._properties.get("parameters"), encrypted_extra, ) if self._model and sqlalchemy_uri == self._model.safe_sqlalchemy_uri(): sqlalchemy_uri = self._model.sqlalchemy_uri_decrypted database = DatabaseDAO.build_db_for_connection_test( server_cert=self._properties.get("server_cert", ""), extra=self._properties.get("extra", "{}"), impersonate_user=self._properties.get("impersonate_user", False), encrypted_extra=serialized_encrypted_extra, ) database.set_sqlalchemy_uri(sqlalchemy_uri) database.db_engine_spec.mutate_db_for_connection_test(database) username = self._actor.username if self._actor is not None else None engine = database.get_sqla_engine(user_name=username) try: with closing(engine.raw_connection()) as conn: alive = engine.dialect.do_ping(conn) except Exception as ex: url = make_url_safe(sqlalchemy_uri) context = { "hostname": url.host, "password": url.password, "port": url.port, "username": url.username, "database": url.database, } errors = database.db_engine_spec.extract_errors(ex, context) raise DatabaseTestConnectionFailedError(errors) from ex if not alive: raise DatabaseOfflineError( SupersetError( message=__("Database is offline."), error_type=SupersetErrorType.GENERIC_DB_ENGINE_ERROR, level=ErrorLevel.ERROR, ), )