def executeQuery(self):
self.query = str(self.queryEdit.toPlainText()).strip()
try:
sqlReader = orngSQL.SQLReader("%s://%s:%s@%s/%s" %
(self.databaseTypes[self.databaseType].lower(),
quote(self.username), quote(self.password), quote(self.host), quote(self.database)))
sqlReader.execute(self.query)
data = sqlReader.data()
self.error = ""
self.errorIcon.setVisible(False)
except Exception, d:
for m in reversed(d.args):
if isinstance(m, (str, unicode)):
self.error = "Error: " + m
break
else:
self.error = "Error: " + str(d)
self.errorIcon.setVisible(True)
data = None
def __init__(self, parent=None, signalManager=None):
OWSubSQLSelect.__init__(self, parent, signalManager, "SQL select")
self.sqlReader = orngSQL.SQLReader()
self.inputs = []
self.outputs = [("Data", ExampleTable),
("Feature Definitions", orange.Domain)]
#set default settings
self.domain = None
self.recentConnections = ["(none)"]
self.queryFile = None
self.query = ''
self.lastQuery = None
self.loadSettings()
if self.lastQuery is not None:
self.query = self.lastQuery
self.connectString = self.recentConnections[0]
self.connectBox = OWGUI.widgetBox(self.controlArea, "Database")
self.connectLineEdit = OWGUI.lineEdit(self.connectBox,
self,
'connectString',
callback=self.connectDB)
self.connectCombo = OWGUI.comboBox(self.connectBox,
self,
'connectString',
items=self.recentConnections,
callback=self.selectConnection)
button = OWGUI.button(self.connectBox,
self,
'connect',
callback=self.connectDB,
disabled=0)
#query
self.splitCanvas = QSplitter(Qt.Vertical, self.mainArea)
self.mainArea.layout().addWidget(self.splitCanvas)
self.textBox = OWGUI.widgetBox(self, 'SQL select')
self.splitCanvas.addWidget(self.textBox)
self.queryTextEdit = QPlainTextEdit(self.query, self)
self.textBox.layout().addWidget(self.queryTextEdit)
self.selectBox = OWGUI.widgetBox(self.controlArea, "Select statement")
# self.selectSubmitBox = QHGroupBox("", self.selectBox)
# self.queryTextEdit.setSizePolicy(QSizePolicy(QSizePolicy.Preferred, QSizePolicy.Preferred))
# self.queryTextEdit.setMinimumWidth(300)
# self.connect(self.queryTextEdit, SIGNAL('returnPressed()'), self.executeQuery)
OWGUI.button(self.selectBox, self, "Open...", callback=self.openScript)
OWGUI.button(self.selectBox, self, "Save...", callback=self.saveScript)
self.selectBox.setSizePolicy(
QSizePolicy(QSizePolicy.MinimumExpanding,
QSizePolicy.MinimumExpanding))
button = OWGUI.button(self.selectBox,
self,
'execute!',
callback=self.executeQuery,
disabled=0)
self.domainBox = OWGUI.widgetBox(self.controlArea, "Domain")
self.domainLabel = OWGUI.label(self.domainBox, self, '')
# info
self.infoBox = OWGUI.widgetBox(self.controlArea, "Info")
self.info = []
self.info.append(OWGUI.label(self.infoBox, self, 'No data loaded.'))
self.info.append(OWGUI.label(self.infoBox, self, ''))
self.resize(300, 300)
# Description: Writes a data set to and reads from an SQL database
# Category: file formats
# Classes: ExampleTable, orngSQL.SQLReader, orngSQL.SQLWriter
# Uses: iris.tab
# Referenced: orngSQL.htm
import orange, orngSQL, orngTree
data = orange.ExampleTable("iris")
print "Input data domain:"
for a in data.domain.variables:
print a
r = orngSQL.SQLReader('mysql://*****:*****@localhost/test')
w = orngSQL.SQLWriter('mysql://*****:*****@localhost/test')
# the following line only works with mysql because it uses the enum type.
w.create('iris',
data,
renameDict={
'sepal length': 'seplen',
'sepal width': 'sepwidth',
'petal length': 'petlen',
'petal width': 'petwidth'
},
typeDict={
'iris':
"""enum('Iris-setosa', 'Iris-versicolor', 'Iris-virginica')"""
})
r.execute("SELECT petwidth, petlen FROM iris WHERE seplen<5.0;")
data = r.data()
print "\n%d instances returned" % len(data)
# Description: Writes a data set to and reads from an SQL database
# Category: file formats
# Classes: ExampleTable, orngSQL.SQLReader, orngSQL.SQLWriter
# Uses: iris.tab
# Referenced: orngSQL.htm
import orange, orngSQL, orngTree
data = orange.ExampleTable("iris")
print "Input data domain:"
for a in data.domain.variables:
print a
w = orngSQL.SQLWriter('mysql://*****:*****@localhost/test')
w.create('iris', data)
r = orngSQL.SQLReader('mysql://*****:*****@puhek/test')
r.execute('SELECT "petal width", "petal length" FROM iris WHERE "sepal length"<5.0')
data = r.data()
print "\n%d instances returned" % len(data)
print "Output data domain:"
for a in data.domain.variables:
print a