def loadTable(self, index):
qDebug("loadTable() #%d" % index)
if self.currentDb == None:
self.statusBar().showMessage("Open a database first before loading tables.")
tblName = self.tableList.itemData(self.tableList.currentIndex())
#tblName = self.tableList.currentText()
qDebug("loadTable() loading table '%s'" % tblName)
self.model = ODBCView(self)
self.model.setQuery("SELECT * FROM [%s]" % tblName, self.currentDb)
if self.model.lastError().isValid():
QMessageBox.critical(self, "Error", self.model.lastError().text())
self.statusBar().showMessage("%d records in table." % self.model.rowCount())
#if not self.model.select():
# QMessageBox.critical(self, "Error", self.model.lastError().text())
self.dbTable.setModel(self.model)
self.dbTable.show()
#qDebug("loadTable() select statement: %s" % self.model.selectStatement())
self.dbTable.resizeColumnsToContents()
self.dbTable.horizontalHeader().setStretchLastSection(True)
self.dbTable.show()
class MainWindow(QMainWindow):
def __init__(self):
super(MainWindow, self).__init__()
## main window setup
self.setMinimumSize(1100, 700)
self.setWindowTitle("Database Viewer")
self.centralWidget = QWidget()
self.setCentralWidget(self.centralWidget)
## layout settings
self.layout = QVBoxLayout()
gridLayout = QGridLayout()
self.layout.addLayout(gridLayout)
self.centralWidget.setLayout(self.layout)
## widgets
self.tableList = QComboBox(self)
self.clearQueryButton = QPushButton("Clear Queries")
self.submitButton = QPushButton("Submit")
self.submitButton.clicked.connect(self.submitChanges)
self.submitButton.setDisabled(True)
self.revertButton = QPushButton("Revert")
self.revertButton.clicked.connect(self.revertChanges)
self.revertButton.setDisabled(True)
## Edit Type selector. On Row Change and On Field Change are automatic updates
## whereas On Manual Submit will cache changes until Submit is clicked.
self.editType = QComboBox()
self.editType.addItems([ 'On Row Change', 'On Field Change', 'On Manual Submit' ])
self.editType.activated.connect(self.changeMode)
self.editType.setDisabled(True)
self.dbTable = QTableView(self)
self.tableList.activated.connect(self.loadTable)
self.clearQueryButton.clicked.connect(self.clearQuery)
self.dbTable.setEditTriggers(QAbstractItemView.NoEditTriggers)
gridLayout.addWidget(QLabel("Tables"), 0, 0, 1, 7)
gridLayout.addWidget(QLabel("Edit Table"), 2, 0, 1, 1)
gridLayout.addWidget(QLabel("Update Type"), 4, 1, 1, 4)
gridLayout.addWidget(self.editType, 4, 0, 1, 4)
gridLayout.addWidget(self.submitButton, 6, 0, 1, 4)
gridLayout.addWidget(self.revertButton, 7, 0, 1, 4)
gridLayout.addWidget(self.tableList, 1, 0, 1, 7)
gridLayout.addWidget(self.clearQueryButton, 2, 0, 1, 4)
gridLayout.addWidget(self.dbTable, 0, 8, 50, 50)
self.currentDb = None
self.fillMenuBar()
self.statusBar()
if len(sys.argv) >= 2:
self.accessDB(' '.join(sys.argv[1:]))
def fillMenuBar(self):
fileMenu = self.menuBar().addMenu("File")
openAction = QAction("Open", self)
openAction.setShortcut(QKeySequence.Open)
openAction.triggered.connect(self.openDB)
fileMenu.addAction(openAction)
editMenu = self.menuBar().addMenu("Edit")
self.editAction = QAction("Edit Table", self)
self.editAction.triggered.connect(self.changeMode)
self.editAction.setCheckable(True)
editMenu.addAction(self.editAction)
toolsMenu = self.menuBar().addMenu("Tools")
queryCreatorAction = QAction("Query Creator", self)
queryCreatorAction.triggered.connect(self.queryCreator)
toolsMenu.addAction(queryCreatorAction)
helpMenu = self.menuBar().addMenu("Help")
aboutAction = QAction("About", self)
aboutAction.triggered.connect(self.about)
helpMenu.addAction(aboutAction)
switchPLUAct = QAction("Switch PLU", self)
switchPLUAct.triggered.connect(self.launchSwitchPLU)
toolsMenu.addAction(switchPLUAct)
def about(self):
QMessageBox.information(self, 'About', 'BAKING SODA!', QMessageBox.Ok)
return
def launchSwitchPLU(self):
sp = SwitchPLU(self)
sp.setAttribute(Qt.WA_DeleteOnClose)
sp.exec_()
def toggleEditButtons(self, on_off):
self.submitButton.setEnabled(on_off)
self.revertButton.setEnabled(on_off)
def backupPrompt(self, state):
if state == Qt.Checked:
if self.editType.currentIndex() == 2:
self.toggleEditButtons(False)
self.editType.setDisabled(False)
elif state == Qt.Unchecked:
self.toggleEditButtons(True)
self.editType.setDisabled(True)
def submitChanges(self):
pass
def revertChanges(self):
pass
def changeMode(self, on_off):
if self.currentDb == None:
self.editAction.setChecked(False)
self.statusBar().showMessage("You must open a database before you can edit a table.")
qDebug("changeMode() open a database first")
else:
if self.editAction.isChecked():
qDebug("changeMode() do you want to edit?")
reply = QMessageBox.question(self, 'Edit Table', 'Are you sure you want to edit this table?', QMessageBox.Yes | QMessageBox.No)
if reply == QMessageBox.Yes:
self.editAction.setChecked(True)
self.toggleEditButtons(on_off)
self.dbTable.setEditTriggers(QAbstractItemView.DoubleClicked)
qDebug("changeMode() turned editing on")
else:
qDebug("changeMode() canceled editing prompt")
self.editAction.setChecked(False)
else:
self.editAction.setChecked(False)
self.toggleEditButtons(on_off)
self.dbTable.setEditTriggers(QAbstractItemView.NoEditTriggers)
qDebug("changeMode() turned editing off")
def openDB(self):
filename = QFileDialog.getOpenFileName(self, "Open Access Database", MSL, "Microsoft Access (*.mdb *.accdb);;Micro$ale Compacted Database (*.old *.bak)")
if filename:
self.accessDB(filename)
def accessDB(self, dbpath):
if self.currentDb is not None:
self.currentDb.close()
db = QSqlDatabase.addDatabase('QODBC', dbpath)
db.setDatabaseName(util.odbc_conn_str + dbpath)
self.currentDb = db
if not db.open():
QMessageBox.critical(self, dbpath, "Could not open database: %s" % db.lastError().text())
return
self.tableList.clear()
#self.tableList.addItems(db.tables())
for tbl in db.tables():
query = QSqlQuery('SELECT COUNT(*) FROM [%s]' % tbl, self.currentDb)
query.next()
self.tableList.addItem('%s (%s)' % (tbl, query.value(0)), tbl)
def loadTable(self, index):
qDebug("loadTable() #%d" % index)
if self.currentDb == None:
self.statusBar().showMessage("Open a database first before loading tables.")
tblName = self.tableList.itemData(self.tableList.currentIndex())
#tblName = self.tableList.currentText()
qDebug("loadTable() loading table '%s'" % tblName)
self.model = ODBCView(self)
self.model.setQuery("SELECT * FROM [%s]" % tblName, self.currentDb)
if self.model.lastError().isValid():
QMessageBox.critical(self, "Error", self.model.lastError().text())
self.statusBar().showMessage("%d records in table." % self.model.rowCount())
#if not self.model.select():
# QMessageBox.critical(self, "Error", self.model.lastError().text())
self.dbTable.setModel(self.model)
self.dbTable.show()
#qDebug("loadTable() select statement: %s" % self.model.selectStatement())
self.dbTable.resizeColumnsToContents()
self.dbTable.horizontalHeader().setStretchLastSection(True)
self.dbTable.show()
def loadQueries(self):
''' load all SQL queries into memory, and add them
to the combo box with the file contents as the item data.
we could run into problems if there are several gigs of
queries to load. tested loading 1,888 queries without delay '''
self.savedCombo.clear()
savePath = QDir(QUERY_DIR)
savePath.setNameFilters(['*.sql'])
saved = savePath.entryList(QDir.Files)
self.savedCombo.addItem('', '')
failedToLoad = [ ]
for filename in saved:
try:
with open(os.path.join(QUERY_DIR, filename), 'rb') as sql:
self.savedCombo.addItem(filename, sql.read())
except IOError:
## this should never happen
failedToLoad.append(filename)
if failedToLoad:
## this should never happen
QMessageBox.critical(self, "No such file or directory.", "Failed to load <b>%d</b> queries: <br><br>%s" % (len(failedToLoad), '<br>'.join(failedToLoad)))
def readQuery(self, index):
''' Sets the query box to the item data of the combo box index '''
self.queryBox.setPlainText(self.savedCombo.itemData(index))
def queryCreator(self):
self.queryDialog = QDialog(self)
self.queryDialog.setWindowTitle("Query Creator")
self.queryDialog.setFixedSize(500, 250)
layout = QVBoxLayout()
gridLayout = QGridLayout()
self.queryDialog.setLayout(layout)
self.savedCombo = QComboBox()
self.queryBox = QTextEdit()
clearButton = QPushButton("Clear")
clearButton.clicked.connect(self.clearQuery)
saveButton = QPushButton("Save")
saveButton.clicked.connect(self.saveQuery)
saveAsButton = QPushButton("Save As")
saveAsButton.clicked.connect(self.saveQueryAs)
deleteButton = QPushButton("Delete")
deleteButton.clicked.connect(self.deleteQuery)
executeButton = QPushButton("Execute")
executeButton.clicked.connect(self.runQuery)
gridLayout.addWidget(clearButton, 0, 0, 1, 4)
gridLayout.addWidget(saveButton, 1, 0, 1, 4)
gridLayout.addWidget(saveAsButton, 2, 0, 1, 4)
gridLayout.addWidget(deleteButton, 3, 0, 1, 4)
gridLayout.addWidget(executeButton, 4, 0, 1, 4)
gridLayout.addWidget(self.savedCombo, 0, 5, 1, 15)
gridLayout.addWidget(self.queryBox, 1, 5, 10, 15)
layout.addLayout(gridLayout)
self.loadQueries()
self.savedCombo.activated.connect(self.readQuery)
self.queryDialog.setAttribute(Qt.WA_DeleteOnClose)
self.queryDialog.exec_()
def clearQuery(self):
''' Reloads current table and index. Also clears out the queryBox if necessary. '''
index = self.tableList.currentIndex()
self.loadTable(index)
if self.savedCombo:
self.queryBox.clear()
def saveQuery(self):
''' Saves changes to the currently selected query. '''
index = self.savedCombo.currentIndex()
saveFile = self.savedCombo.itemText(index)
data = self.queryBox.toPlainText()
self.savedCombo.setItemData(index, data)
with open(os.path.join(QUERY_DIR, saveFile), 'wb') as fh:
fh.write(self.queryBox.toPlainText())
def saveQueryAs(self):
''' Saves text in TextEdit as a .sql file, reloads all the .sql files
in QUERY_DIR, then sets the current index to the file name that was just
saved. Have to use findText because there's no telling where it ends up. '''
saveFile = QFileDialog.getSaveFileName(self, 'Save File', QUERY_DIR, '.sql')
if not saveFile:
self.queryDialog.raise_()
return
index = self.savedCombo.currentIndex()
with open(saveFile, 'wb') as fh:
fh.write(self.queryBox.toPlainText())
self.loadQueries()
self.queryDialog.raise_()
self.savedCombo.setCurrentIndex(self.savedCombo.findText(os.path.basename(saveFile)))
def deleteQuery(self):
''' Makes sure you're not attempting to delete the empty list item,
removes the file from the disk, removes the item from the ComboBox,
and removes the text from the TextEdit '''
currentQuery = self.savedCombo.currentIndex()
if currentQuery != 0:
try:
os.unlink(os.path.join(QUERY_DIR, self.savedCombo.currentText()))
except WindowsError:
pass
## file has already been deleted, silently ignore.
self.loadQueries()
self.queryBox.clear()
def runQuery(self):
if not self.currentDb:
self.statusBar().showMessage("Must connect to a database first.")
return
model = QSqlQueryModel(self)
model.setQuery(self.queryBox.toPlainText(), self.currentDb)
self.dbTable.setModel(model)
self.dbTable.resizeColumnsToContents()
self.dbTable.horizontalHeader().setStretchLastSection(True)
self.dbTable.show()
