def test_XY_large(self):
from Orange.data.sql.table import AUTO_DL_LIMIT as DLL
mat = np.random.randint(0, 2, (DLL + 100, 3))
conn, table_name = self.create_sql_table(mat)
sql_table = SqlTable(
conn,
table_name,
type_hints=Domain([],
DiscreteVariable(name="col2",
values=["0", "1", "2"])),
)
self.assertRaises(ValueError, lambda: sql_table.X)
self.assertRaises(ValueError, lambda: sql_table.Y)
with self.assertRaises(ValueError):
sql_table.download_data(DLL + 10)
# Download partial data
sql_table.download_data(DLL + 10, partial=True)
assert_almost_equal(sql_table.X, mat[:DLL + 10, :2])
assert_almost_equal(sql_table.Y.flatten()[:DLL + 10], mat[:DLL + 10,
2])
# Download all data
sql_table.download_data()
assert_almost_equal(sql_table.X, mat[:, :2])
assert_almost_equal(sql_table.Y.flatten(), mat[:, 2])
def test_XY_large(self):
from Orange.data.sql.table import AUTO_DL_LIMIT as DLL
mat = np.random.randint(0, 2, (DLL + 100, 3))
conn, table_name = self.create_sql_table(mat)
sql_table = SqlTable(conn, table_name,
type_hints=Domain([], DiscreteVariable(
name='col2', values=['0', '1', '2'])))
self.assertRaises(ValueError, lambda: sql_table.X)
self.assertRaises(ValueError, lambda: sql_table.Y)
with self.assertRaises(ValueError):
sql_table.download_data(DLL + 10)
# Download partial data
sql_table.download_data(DLL + 10, partial=True)
assert_almost_equal(sql_table.X, mat[:DLL + 10, :2])
assert_almost_equal(sql_table.Y.flatten()[:DLL + 10], mat[:DLL + 10, 2])
# Download all data
sql_table.download_data()
assert_almost_equal(sql_table.X, mat[:, :2])
assert_almost_equal(sql_table.Y.flatten(), mat[:, 2])
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.tablecombo.currentText()
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:
if not self.materialize_table_name:
self.Error.connection(
"Specify a table name to materialize the query")
return
try:
cur = self._connection.cursor()
cur.execute("DROP TABLE IF EXISTS " + self.materialize_table_name)
cur.execute("CREATE TABLE " + self.materialize_table_name + " AS " + self.table)
cur.execute("ANALYZE " + self.materialize_table_name)
self.table = self.materialize_table_name
except psycopg2.ProgrammingError as ex:
self.Error.connection(str(ex))
return
finally:
self._connection.commit()
try:
table = SqlTable(dict(host=self.host,
port=self.port,
database=self.database,
user=self.username,
password=self.password),
self.table,
inspect_values=False)
except psycopg2.ProgrammingError 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(guess_values=True)
self.Information.data_sampled()
else:
domain = table.get_domain(guess_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 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 open_table(self):
if self.tablecombo.currentIndex() <= 0:
return
if self.tablecombo.currentIndex() < self.tablecombo.count() - 1:
self.table = self.tablecombo.currentText()
else:
self.table = self.sqltext.toPlainText()
table = SqlTable(dict(host=self.host,
port=self.port,
database=self.database,
user=self.username,
password=self.password),
self.table,
inspect_values=False)
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(1)
if self.guess_values:
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
if sample:
s = table.sample_time(1)
domain = s.get_domain(guess_values=True)
self.information(
1, "Domain was generated from a sample of the table.")
else:
domain = table.get_domain(guess_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)
self.send("Data", 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 # pylint: disable=import-error
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)
if is_postgres(self.backend):
sample_button = confirm.addButton("Yes, on a sample",
QMessageBox.YesRole)
confirm.exec()
if confirm.clickedButton() == no_button:
self.guess_values = False
elif is_postgres(self.backend) and \
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() > AUTO_DL_LIMIT:
if is_postgres(self.backend):
confirm = QMessageBox(self)
confirm.setIcon(QMessageBox.Warning)
confirm.setText("Data appears to be big. Do you really "
"want to download it to local memory?")
if table.approx_len() <= MAX_DL_LIMIT:
confirm.addButton("Yes", QMessageBox.YesRole)
no_button = confirm.addButton("No", QMessageBox.NoRole)
sample_button = confirm.addButton("Yes, a sample",
QMessageBox.YesRole)
confirm.exec()
if confirm.clickedButton() == no_button:
return
elif confirm.clickedButton() == sample_button:
table = table.sample_percentage(
AUTO_DL_LIMIT / table.approx_len() * 100)
else:
if table.approx_len() > MAX_DL_LIMIT:
QMessageBox.warning(
self, 'Warning', "Data is too big to download.\n")
return
else:
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:
return
table.download_data(MAX_DL_LIMIT)
table = Table(table)
return table
def open_table(self):
if self.tablecombo.currentIndex() <= 0:
return
if self.tablecombo.currentIndex() < self.tablecombo.count() - 1:
self.table = self.tablecombo.currentText()
else:
self.table = self.sqltext.toPlainText()
table = SqlTable(dict(host=self.host,
port=self.port,
database=self.database,
user=self.username,
password=self.password),
self.table,
inspect_values=False)
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(1)
if self.guess_values:
QApplication.setOverrideCursor(QCursor(Qt.WaitCursor))
if sample:
s = table.sample_time(1)
domain = s.get_domain(guess_values=True)
self.information(
1, "Domain was generated from a sample of the table.")
else:
domain = table.get_domain(guess_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)
self.send("Data", table)
