def get_control_by_type(param): if isinstance(param, tuple): options = param[1:] param = param[0] else: options = () if param == 'str': result = QLineEdit() if 'pwd' in options: result.setEchoMode(QLineEdit.Password) if 'optional' in options: result.setPlaceholderText('(Optional)') elif param == 'big_str': result = QPlainTextEdit() elif param == 'label': result = QLabel() if 'url' in options: result.setTextFormat(Qt.RichText) result.setTextInteractionFlags(Qt.TextBrowserInteraction) result.setOpenExternalLinks(True) elif param == 'checkbox': result = QCheckBox() else: raise RuntimeError() return result
class SignInForm(QDialog): def __init__(self, flags, *args, **kwargs): super().__init__(flags, *args, **kwargs) self.cm: CredentialManager = CredentialManager(CREDENTIAL_MANAGER_SERVICE) self.setWindowTitle('Sign in') self.setFixedSize(400, 250) self.server_cb_label = QLabel('Server *') self.server_cb = QComboBox(self) self.server_cb.addItems(RESOLWE_URLS) self.server_cb.setEditable(True) self.username_label = QLabel('Username *') self.username_line_edit = QLineEdit(self) self.username_line_edit.setPlaceholderText('Enter correct username') self.username_line_edit.returnPressed.connect(self.sign_in) self.username_line_edit.textChanged.connect(self.handle_sign_in_btn) self.password_label = QLabel('Password *') self.password_line_edit = QLineEdit(self) self.password_line_edit.setPlaceholderText('Enter correct password') self.password_line_edit.returnPressed.connect(self.sign_in) self.password_line_edit.textChanged.connect(self.handle_sign_in_btn) self.password_line_edit.setEchoMode(QLineEdit.Password) self.sign_in_btn = QPushButton('Sign in', self) self.sign_in_btn.setDisabled(True) self.sign_in_btn.clicked.connect(self.sign_in) self.error_msg = QLabel('Unable to log in with provided credentials.') self.error_msg.setStyleSheet('color:red') self.error_msg.hide() layout = QVBoxLayout(self) layout.addWidget(self.server_cb_label) layout.addWidget(self.server_cb) layout.addWidget(self.username_label) layout.addWidget(self.username_line_edit) layout.addWidget(self.password_label) layout.addWidget(self.password_line_edit) layout.addWidget(self.error_msg) layout.addStretch() layout.addWidget(self.sign_in_btn) self.resolwe_instance = None def handle_sign_in_btn(self): self.sign_in_btn.setEnabled( True if self.username_line_edit.text() and self.password_line_edit.text() else False ) def sign_in(self): self.server_cb_label.setStyleSheet(None) self.username_label.setStyleSheet(None) self.password_label.setStyleSheet(None) self.error_msg.hide() server = self.server_cb.currentText() username = self.cm.username if self.cm.username else self.username_line_edit.text() password = self.cm.password if self.cm.password else self.password_line_edit.text() if not server: self.server_cb_label.setStyleSheet('color:red') return if not username: self.username_label.setStyleSheet('color:red') return if not password: self.password_label.setStyleSheet('color:red') return try: self.resolwe_instance = connect(username, password, url=server) except ResolweAuthException: self.error_msg.show() return self.cm.username = username self.cm.password = password self.accept()
class OWSql(OWWidget): name = "SQL Table" id = "orange.widgets.data.sql" description = "Load data set from SQL." icon = "icons/SQLTable.svg" priority = 10 category = "Data" keywords = ["data", "file", "load", "read"] class Outputs: data = Output( "Data", Table, doc="Attribute-valued data set read from the input file.") settings_version = 2 want_main_area = False resizing_enabled = False host = Setting(None) port = Setting(None) database = Setting(None) schema = Setting(None) username = "" password = "" table = Setting(None) sql = Setting("") guess_values = Setting(True) download = Setting(False) materialize = Setting(False) materialize_table_name = Setting("") class Information(OWWidget.Information): data_sampled = Msg("Data description was generated from a sample.") class Error(OWWidget.Error): connection = Msg("{}") no_backends = Msg("Please install a backend to use this widget") missing_extension = Msg("Database is missing extension{}: {}") def __init__(self): super().__init__() self.backend = None self.data_desc_table = None self.database_desc = None vbox = gui.vBox(self.controlArea, "Server", addSpace=True) box = gui.vBox(vbox) self.backendmodel = BackendModel(Backend.available_backends()) self.backendcombo = QComboBox(box) if len(self.backendmodel): self.backendcombo.setModel(self.backendmodel) else: self.Error.no_backends() box.setEnabled(False) box.layout().addWidget(self.backendcombo) self.servertext = QLineEdit(box) self.servertext.setPlaceholderText('Server') self.servertext.setToolTip('Server') self.servertext.editingFinished.connect(self._load_credentials) if self.host: self.servertext.setText(self.host if not self.port else '{}:{}'. format(self.host, self.port)) box.layout().addWidget(self.servertext) self.databasetext = QLineEdit(box) self.databasetext.setPlaceholderText('Database[/Schema]') self.databasetext.setToolTip('Database or optionally Database/Schema') if self.database: self.databasetext.setText( self.database if not self.schema else '{}/{}'. format(self.database, self.schema)) box.layout().addWidget(self.databasetext) self.usernametext = QLineEdit(box) self.usernametext.setPlaceholderText('Username') self.usernametext.setToolTip('Username') box.layout().addWidget(self.usernametext) self.passwordtext = QLineEdit(box) self.passwordtext.setPlaceholderText('Password') self.passwordtext.setToolTip('Password') self.passwordtext.setEchoMode(QLineEdit.Password) box.layout().addWidget(self.passwordtext) self._load_credentials() tables = gui.hBox(box) self.tablemodel = TableModel() self.tablecombo = QComboBox( minimumContentsLength=35, sizeAdjustPolicy=QComboBox.AdjustToMinimumContentsLength) self.tablecombo.setModel(self.tablemodel) self.tablecombo.setToolTip('table') tables.layout().addWidget(self.tablecombo) self.tablecombo.activated[int].connect(self.select_table) self.connectbutton = gui.button(tables, self, '↻', callback=self.connect) self.connectbutton.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Fixed) tables.layout().addWidget(self.connectbutton) self.custom_sql = gui.vBox(box) self.custom_sql.setVisible(False) self.sqltext = QTextEdit(self.custom_sql) self.sqltext.setPlainText(self.sql) self.custom_sql.layout().addWidget(self.sqltext) mt = gui.hBox(self.custom_sql) cb = gui.checkBox(mt, self, 'materialize', 'Materialize to table ') cb.setToolTip('Save results of the query in a table') le = gui.lineEdit(mt, self, 'materialize_table_name') le.setToolTip('Save results of the query in a table') self.executebtn = gui.button(self.custom_sql, self, 'Execute', callback=self.open_table) box.layout().addWidget(self.custom_sql) gui.checkBox(box, self, "guess_values", "Auto-discover discrete variables", callback=self.open_table) gui.checkBox(box, self, "download", "Download data to local memory", callback=self.open_table) gui.rubber(self.buttonsArea) QTimer.singleShot(0, self.connect) def _load_credentials(self): self._parse_host_port() cm = self._credential_manager(self.host, self.port) self.username = cm.username self.password = cm.password if self.username: self.usernametext.setText(self.username) if self.password: self.passwordtext.setText(self.password) def _save_credentials(self): cm = self._credential_manager(self.host, self.port) cm.username = self.username cm.password = self.password def _credential_manager(self, host, port): return CredentialManager("SQL Table: {}:{}".format(host, port)) def error(self, id=0, text=""): super().error(id, text) err_style = 'QLineEdit {border: 2px solid red;}' if 'server' in text or 'host' in text: self.servertext.setStyleSheet(err_style) else: self.servertext.setStyleSheet('') if 'role' in text: self.usernametext.setStyleSheet(err_style) else: self.usernametext.setStyleSheet('') if 'database' in text: self.databasetext.setStyleSheet(err_style) else: self.databasetext.setStyleSheet('') def _parse_host_port(self): hostport = self.servertext.text().split(':') self.host = hostport[0] self.port = hostport[1] if len(hostport) == 2 else None def connect(self): self._parse_host_port() self.database, _, self.schema = self.databasetext.text().partition('/') self.username = self.usernametext.text() or None self.password = self.passwordtext.text() or None try: if self.backendcombo.currentIndex() < 0: return backend = self.backendmodel[self.backendcombo.currentIndex()] self.backend = backend( dict(host=self.host, port=self.port, database=self.database, user=self.username, password=self.password)) self.Error.connection.clear() self._save_credentials() self.database_desc = OrderedDict( (("Host", self.host), ("Port", self.port), ("Database", self.database), ("User name", self.username))) self.refresh_tables() self.select_table() except BackendError as err: error = str(err).split('\n')[0] self.Error.connection(error) self.database_desc = self.data_desc_table = None self.tablecombo.clear() def refresh_tables(self): self.tablemodel.clear() self.Error.missing_extension.clear() if self.backend is None: self.data_desc_table = None return self.tablemodel.append("Select a table") self.tablemodel.extend(self.backend.list_tables(self.schema)) self.tablemodel.append("Custom SQL") def select_table(self): curIdx = self.tablecombo.currentIndex() if self.tablecombo.itemText(curIdx) != "Custom SQL": self.custom_sql.setVisible(False) return self.open_table() else: self.custom_sql.setVisible(True) self.data_desc_table = None self.database_desc["Table"] = "(None)" self.table = None #self.Error.missing_extension( # 's' if len(missing) > 1 else '', # ', '.join(missing), # shown=missing) def open_table(self): table = self.get_table() self.data_desc_table = table self.Outputs.data.send(table) def get_table(self): if self.tablecombo.currentIndex() <= 0: if self.database_desc: self.database_desc["Table"] = "(None)" self.data_desc_table = None return if self.tablecombo.currentIndex() < self.tablecombo.count() - 1: self.table = self.tablemodel[self.tablecombo.currentIndex()] self.database_desc["Table"] = self.table if "Query" in self.database_desc: del self.database_desc["Query"] else: self.sql = self.table = self.sqltext.toPlainText() if self.materialize: import psycopg2 if not self.materialize_table_name: self.Error.connection( "Specify a table name to materialize the query") return try: with self.backend.execute_sql_query( "DROP TABLE IF EXISTS " + self.materialize_table_name): pass with self.backend.execute_sql_query( "CREATE TABLE " + self.materialize_table_name + " AS " + self.table): pass with self.backend.execute_sql_query( "ANALYZE " + self.materialize_table_name): pass self.table = self.materialize_table_name except (psycopg2.ProgrammingError, BackendError) as ex: self.Error.connection(str(ex)) return try: table = SqlTable(dict(host=self.host, port=self.port, database=self.database, user=self.username, password=self.password), self.table, backend=type(self.backend), inspect_values=False) except BackendError as ex: self.Error.connection(str(ex)) return self.Error.connection.clear() sample = False if table.approx_len() > LARGE_TABLE and self.guess_values: confirm = QMessageBox(self) confirm.setIcon(QMessageBox.Warning) confirm.setText("Attribute discovery might take " "a long time on large tables.\n" "Do you want to auto discover attributes?") confirm.addButton("Yes", QMessageBox.YesRole) no_button = confirm.addButton("No", QMessageBox.NoRole) sample_button = confirm.addButton("Yes, on a sample", QMessageBox.YesRole) confirm.exec() if confirm.clickedButton() == no_button: self.guess_values = False elif confirm.clickedButton() == sample_button: sample = True self.Information.clear() if self.guess_values: QApplication.setOverrideCursor(QCursor(Qt.WaitCursor)) if sample: s = table.sample_time(1) domain = s.get_domain(inspect_values=True) self.Information.data_sampled() else: domain = table.get_domain(inspect_values=True) QApplication.restoreOverrideCursor() table.domain = domain if self.download: if table.approx_len() > MAX_DL_LIMIT: QMessageBox.warning( self, 'Warning', "Data is too big to download.\n" "Consider using the Data Sampler widget to download " "a sample instead.") self.download = False elif table.approx_len() > AUTO_DL_LIMIT: confirm = QMessageBox.question( self, 'Question', "Data appears to be big. Do you really " "want to download it to local memory?", QMessageBox.Yes | QMessageBox.No, QMessageBox.No) if confirm == QMessageBox.No: self.download = False if self.download: table.download_data(MAX_DL_LIMIT) table = Table(table) return table def send_report(self): if not self.database_desc: self.report_paragraph("No database connection.") return self.report_items("Database", self.database_desc) if self.data_desc_table: self.report_items("Data", report.describe_data(self.data_desc_table)) @classmethod def migrate_settings(cls, settings, version): if version < 2: # Until Orange version 3.4.4 username and password had been stored # in Settings. cm = cls._credential_manager(settings["host"], settings["port"]) cm.username = settings["username"] cm.password = settings["password"]
class OWSql(OWWidget): name = "SQL Table" id = "orange.widgets.data.sql" description = "Load data set from SQL." icon = "icons/SQLTable.svg" priority = 30 category = "Data" keywords = ["data", "file", "load", "read", "SQL"] class Outputs: data = Output("Data", Table, doc="Attribute-valued data set read from the input file.") settings_version = 2 want_main_area = False resizing_enabled = False host = Setting(None) port = Setting(None) database = Setting(None) schema = Setting(None) username = "" password = "" table = Setting(None) sql = Setting("") guess_values = Setting(True) download = Setting(False) materialize = Setting(False) materialize_table_name = Setting("") class Information(OWWidget.Information): data_sampled = Msg("Data description was generated from a sample.") class Error(OWWidget.Error): connection = Msg("{}") no_backends = Msg("Please install a backend to use this widget") missing_extension = Msg("Database is missing extension{}: {}") def __init__(self): super().__init__() self.backend = None self.data_desc_table = None self.database_desc = None vbox = gui.vBox(self.controlArea, "Server", addSpace=True) box = gui.vBox(vbox) self.backends = BackendModel(Backend.available_backends()) self.backendcombo = QComboBox(box) if len(self.backends): self.backendcombo.setModel(self.backends) else: self.Error.no_backends() box.setEnabled(False) box.layout().addWidget(self.backendcombo) self.servertext = QLineEdit(box) self.servertext.setPlaceholderText('Server') self.servertext.setToolTip('Server') self.servertext.editingFinished.connect(self._load_credentials) if self.host: self.servertext.setText(self.host if not self.port else '{}:{}'.format(self.host, self.port)) box.layout().addWidget(self.servertext) self.databasetext = QLineEdit(box) self.databasetext.setPlaceholderText('Database[/Schema]') self.databasetext.setToolTip('Database or optionally Database/Schema') if self.database: self.databasetext.setText( self.database if not self.schema else '{}/{}'.format(self.database, self.schema)) box.layout().addWidget(self.databasetext) self.usernametext = QLineEdit(box) self.usernametext.setPlaceholderText('Username') self.usernametext.setToolTip('Username') box.layout().addWidget(self.usernametext) self.passwordtext = QLineEdit(box) self.passwordtext.setPlaceholderText('Password') self.passwordtext.setToolTip('Password') self.passwordtext.setEchoMode(QLineEdit.Password) box.layout().addWidget(self.passwordtext) self._load_credentials() self.tables = TableModel() tables = gui.hBox(box) self.tablecombo = QComboBox( minimumContentsLength=35, sizeAdjustPolicy=QComboBox.AdjustToMinimumContentsLength ) self.tablecombo.setModel(self.tables) self.tablecombo.setToolTip('table') tables.layout().addWidget(self.tablecombo) self.connect() index = self.tablecombo.findText(str(self.table)) if index != -1: self.tablecombo.setCurrentIndex(index) # set up the callback to select_table in case of selection change self.tablecombo.activated[int].connect(self.select_table) self.connectbutton = gui.button( tables, self, '↻', callback=self.connect) self.connectbutton.setSizePolicy( QSizePolicy.Fixed, QSizePolicy.Fixed) tables.layout().addWidget(self.connectbutton) self.custom_sql = gui.vBox(box) self.custom_sql.setVisible(False) self.sqltext = QTextEdit(self.custom_sql) self.sqltext.setPlainText(self.sql) self.custom_sql.layout().addWidget(self.sqltext) mt = gui.hBox(self.custom_sql) cb = gui.checkBox(mt, self, 'materialize', 'Materialize to table ') cb.setToolTip('Save results of the query in a table') le = gui.lineEdit(mt, self, 'materialize_table_name') le.setToolTip('Save results of the query in a table') self.executebtn = gui.button( self.custom_sql, self, 'Execute', callback=self.open_table) box.layout().addWidget(self.custom_sql) gui.checkBox(box, self, "guess_values", "Auto-discover categorical variables", callback=self.open_table) gui.checkBox(box, self, "download", "Download data to local memory", callback=self.open_table) gui.rubber(self.buttonsArea) QTimer.singleShot(0, self.select_table) def _load_credentials(self): self._parse_host_port() cm = self._credential_manager(self.host, self.port) self.username = cm.username self.password = cm.password if self.username: self.usernametext.setText(self.username) if self.password: self.passwordtext.setText(self.password) def _save_credentials(self): cm = self._credential_manager(self.host, self.port) cm.username = self.username or '' cm.password = self.password or '' def _credential_manager(self, host, port): return CredentialManager("SQL Table: {}:{}".format(host, port)) def error(self, id=0, text=""): super().error(id, text) err_style = 'QLineEdit {border: 2px solid red;}' if 'server' in text or 'host' in text: self.servertext.setStyleSheet(err_style) else: self.servertext.setStyleSheet('') if 'role' in text: self.usernametext.setStyleSheet(err_style) else: self.usernametext.setStyleSheet('') if 'database' in text: self.databasetext.setStyleSheet(err_style) else: self.databasetext.setStyleSheet('') def _parse_host_port(self): hostport = self.servertext.text().split(':') self.host = hostport[0] self.port = hostport[1] if len(hostport) == 2 else None def connect(self): self._parse_host_port() self.database, _, self.schema = self.databasetext.text().partition('/') self.username = self.usernametext.text() or None self.password = self.passwordtext.text() or None try: if self.backendcombo.currentIndex() < 0: return backend = self.backends[self.backendcombo.currentIndex()] self.backend = backend(dict( host=self.host, port=self.port, database=self.database, user=self.username, password=self.password )) self.Error.connection.clear() self._save_credentials() self.database_desc = OrderedDict(( ("Host", self.host), ("Port", self.port), ("Database", self.database), ("User name", self.username) )) self.refresh_tables() except BackendError as err: error = str(err).split('\n')[0] self.Error.connection(error) self.database_desc = self.data_desc_table = None self.tablecombo.clear() def refresh_tables(self): self.tables.clear() self.Error.missing_extension.clear() if self.backend is None: self.data_desc_table = None return self.tables.append("Select a table") self.tables.append("Custom SQL") self.tables.extend(self.backend.list_tables(self.schema)) # Called on tablecombo selection change: def select_table(self): curIdx = self.tablecombo.currentIndex() if self.tablecombo.itemText(curIdx) != "Custom SQL": self.custom_sql.setVisible(False) return self.open_table() else: self.custom_sql.setVisible(True) self.data_desc_table = None self.database_desc["Table"] = "(None)" self.table = None if len(str(self.sql)) > 14: return self.open_table() #self.Error.missing_extension( # 's' if len(missing) > 1 else '', # ', '.join(missing), # shown=missing) def open_table(self): table = self.get_table() self.data_desc_table = table self.Outputs.data.send(table) def get_table(self): curIdx = self.tablecombo.currentIndex() if curIdx <= 0: if self.database_desc: self.database_desc["Table"] = "(None)" self.data_desc_table = None return if self.tablecombo.itemText(curIdx) != "Custom SQL": self.table = self.tables[self.tablecombo.currentIndex()] self.database_desc["Table"] = self.table if "Query" in self.database_desc: del self.database_desc["Query"] what = self.table else: what = self.sql = self.sqltext.toPlainText() self.table = "Custom SQL" if self.materialize: import psycopg2 if not self.materialize_table_name: self.Error.connection( "Specify a table name to materialize the query") return try: with self.backend.execute_sql_query("DROP TABLE IF EXISTS " + self.materialize_table_name): pass with self.backend.execute_sql_query("CREATE TABLE " + self.materialize_table_name + " AS " + self.sql): pass with self.backend.execute_sql_query("ANALYZE " + self.materialize_table_name): pass except (psycopg2.ProgrammingError, BackendError) as ex: self.Error.connection(str(ex)) return try: table = SqlTable(dict(host=self.host, port=self.port, database=self.database, user=self.username, password=self.password), what, backend=type(self.backend), inspect_values=False) except BackendError as ex: self.Error.connection(str(ex)) return self.Error.connection.clear() sample = False if table.approx_len() > LARGE_TABLE and self.guess_values: confirm = QMessageBox(self) confirm.setIcon(QMessageBox.Warning) confirm.setText("Attribute discovery might take " "a long time on large tables.\n" "Do you want to auto discover attributes?") confirm.addButton("Yes", QMessageBox.YesRole) no_button = confirm.addButton("No", QMessageBox.NoRole) sample_button = confirm.addButton("Yes, on a sample", QMessageBox.YesRole) confirm.exec() if confirm.clickedButton() == no_button: self.guess_values = False elif confirm.clickedButton() == sample_button: sample = True self.Information.clear() if self.guess_values: QApplication.setOverrideCursor(QCursor(Qt.WaitCursor)) if sample: s = table.sample_time(1) domain = s.get_domain(inspect_values=True) self.Information.data_sampled() else: domain = table.get_domain(inspect_values=True) QApplication.restoreOverrideCursor() table.domain = domain if self.download: if table.approx_len() > MAX_DL_LIMIT: QMessageBox.warning( self, 'Warning', "Data is too big to download.\n" "Consider using the Data Sampler widget to download " "a sample instead.") self.download = False elif table.approx_len() > AUTO_DL_LIMIT: confirm = QMessageBox.question( self, 'Question', "Data appears to be big. Do you really " "want to download it to local memory?", QMessageBox.Yes | QMessageBox.No, QMessageBox.No) if confirm == QMessageBox.No: self.download = False if self.download: table.download_data(MAX_DL_LIMIT) table = Table(table) return table def send_report(self): if not self.database_desc: self.report_paragraph("No database connection.") return self.report_items("Database", self.database_desc) if self.data_desc_table: self.report_items("Data", report.describe_data(self.data_desc_table)) @classmethod def migrate_settings(cls, settings, version): if version < 2: # Until Orange version 3.4.4 username and password had been stored # in Settings. cm = cls._credential_manager(settings["host"], settings["port"]) cm.username = settings["username"] cm.password = settings["password"]
class OWBaseSql(OWWidget, openclass=True): """Base widget for connecting to a database. Override `get_backend` when subclassing to get corresponding backend. """ class Outputs: data = Output("Data", Table) class Error(OWWidget.Error): connection = Msg("{}") want_main_area = False resizing_enabled = False host = Setting(None) # type: Optional[str] port = Setting(None) # type: Optional[str] database = Setting(None) # type: Optional[str] schema = Setting(None) # type: Optional[str] username = "" password = "" def __init__(self): super().__init__() self.backend = None # type: Optional[Backend] self.data_desc_table = None # type: Optional[Table] self.database_desc = None # type: Optional[OrderedDict] self._setup_gui() self.connect() def _setup_gui(self): self.controlArea.setMinimumWidth(360) vbox = gui.vBox(self.controlArea, "Server", addSpace=True) self.serverbox = gui.vBox(vbox) self.servertext = QLineEdit(self.serverbox) self.servertext.setPlaceholderText("Server") self.servertext.setToolTip("Server") self.servertext.editingFinished.connect(self._load_credentials) if self.host: self.servertext.setText(self.host if not self.port else "{}:{}".format(self.host, self.port)) self.serverbox.layout().addWidget(self.servertext) self.databasetext = QLineEdit(self.serverbox) self.databasetext.setPlaceholderText("Database[/Schema]") self.databasetext.setToolTip("Database or optionally Database/Schema") if self.database: self.databasetext.setText( self.database if not self.schema else "{}/{}".format(self.database, self.schema)) self.serverbox.layout().addWidget(self.databasetext) self.usernametext = QLineEdit(self.serverbox) self.usernametext.setPlaceholderText("Username") self.usernametext.setToolTip("Username") self.serverbox.layout().addWidget(self.usernametext) self.passwordtext = QLineEdit(self.serverbox) self.passwordtext.setPlaceholderText("Password") self.passwordtext.setToolTip("Password") self.passwordtext.setEchoMode(QLineEdit.Password) self.serverbox.layout().addWidget(self.passwordtext) self._load_credentials() self.connectbutton = gui.button(self.serverbox, self, "Connect", callback=self.connect) self.connectbutton.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Fixed) def _load_credentials(self): self._parse_host_port() cm = self._credential_manager(self.host, self.port) self.username = cm.username self.password = cm.password if self.username: self.usernametext.setText(self.username) if self.password: self.passwordtext.setText(self.password) def _save_credentials(self): cm = self._credential_manager(self.host, self.port) cm.username = self.username or "" cm.password = self.password or "" @staticmethod def _credential_manager(host, port): return CredentialManager("SQL Table: {}:{}".format(host, port)) def _parse_host_port(self): hostport = self.servertext.text().split(":") self.host = hostport[0] self.port = hostport[1] if len(hostport) == 2 else None def _check_db_settings(self): self._parse_host_port() self.database, _, self.schema = self.databasetext.text().partition("/") self.username = self.usernametext.text() or None self.password = self.passwordtext.text() or None def connect(self): self.clear() self._check_db_settings() if not self.host or not self.database: return try: backend = self.get_backend() if backend is None: return self.backend = backend(dict( host=self.host, port=self.port, database=self.database, user=self.username, password=self.password )) self.on_connection_success() except BackendError as err: self.on_connection_error(err) def get_backend(self) -> Type[Backend]: """ Derived widgets should override this to get corresponding backend. Returns ------- backend: Type[Backend] """ raise NotImplementedError def on_connection_success(self): self._save_credentials() self.database_desc = OrderedDict(( ("Host", self.host), ("Port", self.port), ("Database", self.database), ("User name", self.username) )) def on_connection_error(self, err): error = str(err).split("\n")[0] self.Error.connection(error) def open_table(self): data = self.get_table() self.data_desc_table = data self.Outputs.data.send(data) info = str(len(data)) if data else self.info.NoOutput self.info.set_output_summary(info) def get_table(self) -> Table: """ Derived widgets should override this to get corresponding table. Returns ------- table: Table """ raise NotImplementedError def clear(self): self.Error.connection.clear() self.database_desc = None self.data_desc_table = None self.Outputs.data.send(None) self.info.set_output_summary(self.info.NoOutput) def send_report(self): if not self.database_desc: self.report_paragraph("No database connection.") return self.report_items("Database", self.database_desc) if self.data_desc_table: self.report_items("Data", report.describe_data(self.data_desc_table))
class OWOracleSQL(OWWidget): name = "Oracle SQL" description = "Select data from oracle databases" icon = "icons/OracleSQL.svg" class Error(OWWidget.Error): no_backends = Msg( "Please install cx_Oracle package. It is either missing or not working properly" ) class Outputs: data = Output("Data", Table) autocommit = settings.Setting(False, schema_only=True) savedQuery = settings.Setting(None, schema_only=True) savedUsername = settings.Setting(None, schema_only=True) savedPwd = settings.Setting(None, schema_only=True) savedDB = settings.Setting(None, schema_only=True) def __init__(self): super().__init__() self.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Maximum) self.domain = None self.data = None self.query = '' if self.savedQuery is not None: self.query = self.savedQuery self.username = '' if self.savedUsername is not None: self.username = self.savedUsername self.password = '' if self.savedPwd is not None: self.password = self.savedPwd self.database = '' if self.savedDB is not None: self.database = self.savedDB #Control Area layout self.connectBox = gui.widgetBox(self.controlArea, "Database connection") self.connectBox.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Fixed) #Database self.userLabel = gui.label(self.connectBox, self, 'User name') self.connectUser = QLineEdit(self.username, self) self.connectBox.layout().addWidget(self.connectUser) self.passwordLabel = gui.label(self.connectBox, self, 'Password') self.connectPassword = QLineEdit(self.password, self) self.connectPassword.setEchoMode(QLineEdit.Password) self.connectBox.layout().addWidget(self.connectPassword) self.dbLabel = gui.label(self.connectBox, self, 'Database') self.connectDB = QLineEdit(self.database, self) self.connectBox.layout().addWidget(self.connectDB) self.runSQL = gui.auto_commit(self.connectBox, self, 'autocommit', label='Run SQL', commit=self.commit) # query self.sqlBox = gui.widgetBox(self.mainArea, "SQL") self.queryTextEdit = QPlainTextEdit(self.query, self) self.sqlBox.layout().addWidget(self.queryTextEdit) QTimer.singleShot(0, self.commit) def handleNewSignals(self): self._invalidate() def countUniques(self, lst): return len(set([x for x in lst if x is not None])) def setOfUniques(self, lst): return sorted(set([x for x in lst if x is not None])) def dateToStr(self, lst): return [str(x) if x is not None else x for x in lst] def commit(self): if cx_Oracle is None: data = [] columns = [] self.Error.no_backends() username = None password = None database = None query = None else: username = self.connectUser.text() password = self.connectPassword.text() database = self.connectDB.text() con = cx_Oracle.connect(username + "/" + password + "@" + database) query = self.queryTextEdit.toPlainText() cur = con.cursor() cur.execute(query) data = cur.fetchall() columns = [i[0] for i in cur.description] data_tr = list(zip(*data)) def create_variable(column_name, column_data): continuous_types = (int, float, type(None)) datetime_types = (datetime.datetime, type(None)) column_name = str(column_name) if all(isinstance(x, continuous_types) for x in column_data): return ContinuousVariable(column_name) if all(isinstance(x, datetime_types) for x in column_data): return TimeVariable(column_name) if self.countUniques(column_data) < 101: return DiscreteVariable(str(column_name), self.setOfUniques(column_data)) return DiscreteVariable(str(column_name), self.setOfUniques(column_data)) featurelist = [ create_variable(column_name, column_data) for column_name, column_data in zip(columns, data_tr) ] data_tr = [ self.dateToStr(data) if isinstance(var, TimeVariable) else data for (var, data) in zip(featurelist, data_tr) ] data = list(zip(*data_tr)) orangedomain = Domain(featurelist) orangetable = Table(orangedomain, data) self.Outputs.data.send(orangetable) self.savedQuery = query self.savedUsername = username self.savedPwd = password self.savedDB = database def _invalidate(self): self.commit()
class ORACLESQL(OWWidget): name = "Oracle SQL" icon = "icons/OracleSQL.svg" want_main_area = True want_message_bar = True #inputs = [] outputs = [("Data", Table)] description = "Select data from oracle databases" settingsHandler = settings.DomainContextHandler() autocommit = settings.Setting(False, schema_only=True) savedQuery = settings.Setting(None, schema_only=True) savedUsername = settings.Setting(None, schema_only=True) savedPwd = settings.Setting(None, schema_only=True) savedDB = settings.Setting(None, schema_only=True) class Error(OWWidget.Error): no_backends = Msg("Please install cx_Oracle package. It is either missing or not working properly") class Outputs: data = Output("Data", Table) def __init__(self): super().__init__() #Defaults self.setSizePolicy(QSizePolicy.Maximum, QSizePolicy.Maximum) self.domain = None self.data = None self.query = '' if self.savedQuery is not None: self.query = self.savedQuery self.username = '' if self.savedUsername is not None: self.username = self.savedUsername self.password = '' if self.savedPwd is not None: self.password = self.savedPwd self.database = '' if self.savedDB is not None: self.database = self.savedDB #Control Area layout sip.delete(self.controlArea.layout()) self.controlArea.setLayout(QHBoxLayout()) self.connectBox = gui.widgetBox(self.controlArea, "Database connection") self.connectBox.setSizePolicy(QSizePolicy.Fixed, QSizePolicy.Fixed) self.sqlBox = gui.widgetBox(self.controlArea, "SQL") #Database self.userLabel = gui.label(self.connectBox, self, 'User name') self.connectUser = QLineEdit(self.username, self) self.connectBox.layout().addWidget(self.connectUser) self.passwordLabel = gui.label(self.connectBox, self, 'Password') self.connectPassword = QLineEdit(self.password, self) self.connectPassword.setEchoMode(QLineEdit.Password) self.connectBox.layout().addWidget(self.connectPassword) self.dbLabel = gui.label(self.connectBox, self, 'Database') self.connectDB = QLineEdit(self.database, self) self.connectBox.layout().addWidget(self.connectDB) self.runSQL = gui.auto_commit(self.connectBox, self, 'autocommit', label='Run SQL', commit=self.commit) # query self.queryTextEdit = QPlainTextEdit(self.query, self) self.sqlBox.layout().addWidget(self.queryTextEdit) if self.autocommit: self.commit() def handleNewSignals(self): self._invalidate() def countUniques(self,lst): return len(set([x for x in lst if x is not None])) def setOfUniques(self,lst): return sorted(set([x for x in lst if x is not None])) def dateToStr(self,lst): return([str(x) if x is not None else x for x in lst ]) def commit(self): if cx_Oracle is None: data = [] columns = [] self.Error.no_backends() username = None password = None database = None query = None else: username = self.connectUser.text() password = self.connectPassword.text() database = self.connectDB.text() con = cx_Oracle.connect(username+"/"+password+"@"+database) query = self.queryTextEdit.toPlainText() cur = con.cursor() cur.execute(query) data = cur.fetchall() columns = [i[0] for i in cur.description] data_tr=list(zip(*data)) n=len(columns) featurelist=[ContinuousVariable(str(columns[col])) if all(type(x)==int or type(x)==float or type(x)==type(None) for x in data_tr[col]) else TimeVariable(str(columns[col])) if all(type(x)==type(datetime.datetime(9999,12,31,0,0)) or type(x)==type(None) for x in data_tr[col]) else DiscreteVariable(str(columns[col]),self.setOfUniques(data_tr[col])) if self.countUniques(data_tr[col]) < 101 else DiscreteVariable(str(columns[col]),self.setOfUniques(data_tr[col])) for col in range (0,n)] data_tr = [self.dateToStr(data_tr[col]) if all(type(x)==type(datetime.datetime(9999,12,31,0,0)) or type(x)==type(None) for x in data_tr[col]) else data_tr[col] for col in range (0,n)] data=list(zip(*data_tr)) orangedomain=Domain(featurelist) orangetable=Table(orangedomain,data) self.Outputs.data.send(orangetable) self.savedQuery = query self.savedUsername = username self.savedPwd = password self.savedDB = database def _invalidate(self): self.commit()