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()
