Mercurial Repositories > eric / file comparison
comparison: eric7/SqlBrowser/SqlBrowserWidget.py
eric7/SqlBrowser/SqlBrowserWidget.py
- branch
- eric7
- changeset 8312
- 800c432b34c8
- parent 8235
- 78e6d29eb773
- child 8318
- 962bce857696
equal
deleted
inserted
replaced
| 8311:4e8b98454baa | 8312:800c432b34c8 |
|---|---|
| 1 # -*- coding: utf-8 -*- | |
| 2 | |
| 3 # Copyright (c) 2009 - 2021 Detlev Offenbach <detlev@die-offenbachs.de> | |
| 4 # | |
| 5 | |
| 6 """ | |
| 7 Module implementing the SQL Browser widget. | |
| 8 """ | |
| 9 | |
| 10 from PyQt5.QtCore import pyqtSignal, QVariant, Qt, pyqtSlot | |
| 11 from PyQt5.QtGui import QStandardItemModel | |
| 12 from PyQt5.QtWidgets import QWidget, QDialog, QAbstractItemView | |
| 13 from PyQt5.QtSql import ( | |
| 14 QSqlDatabase, QSqlError, QSqlTableModel, QSqlQueryModel, QSqlQuery | |
| 15 ) | |
| 16 | |
| 17 from E5Gui import E5MessageBox | |
| 18 | |
| 19 from .Ui_SqlBrowserWidget import Ui_SqlBrowserWidget | |
| 20 | |
| 21 | |
| 22 class SqlBrowserWidget(QWidget, Ui_SqlBrowserWidget): | |
| 23 """ | |
| 24 Class implementing the SQL Browser widget. | |
| 25 | |
| 26 @signal statusMessage(str) emitted to show a status message | |
| 27 """ | |
| 28 statusMessage = pyqtSignal(str) | |
| 29 | |
| 30 cCount = 0 | |
| 31 | |
| 32 def __init__(self, parent=None): | |
| 33 """ | |
| 34 Constructor | |
| 35 | |
| 36 @param parent reference to the parent widget (QWidget) | |
| 37 """ | |
| 38 super().__init__(parent) | |
| 39 self.setupUi(self) | |
| 40 | |
| 41 self.table.addAction(self.insertRowAction) | |
| 42 self.table.addAction(self.deleteRowAction) | |
| 43 | |
| 44 if len(QSqlDatabase.drivers()) == 0: | |
| 45 E5MessageBox.information( | |
| 46 self, | |
| 47 self.tr("No database drivers found"), | |
| 48 self.tr( | |
| 49 """This tool requires at least one Qt database driver. """ | |
| 50 """Please check the Qt documentation how to build the """ | |
| 51 """Qt SQL plugins.""")) | |
| 52 | |
| 53 self.connections.tableActivated.connect( | |
| 54 self.on_connections_tableActivated) | |
| 55 self.connections.schemaRequested.connect( | |
| 56 self.on_connections_schemaRequested) | |
| 57 self.connections.cleared.connect(self.on_connections_cleared) | |
| 58 | |
| 59 self.statusMessage.emit(self.tr("Ready")) | |
| 60 | |
| 61 @pyqtSlot() | |
| 62 def on_clearButton_clicked(self): | |
| 63 """ | |
| 64 Private slot to clear the SQL entry widget. | |
| 65 """ | |
| 66 self.sqlEdit.clear() | |
| 67 self.sqlEdit.setFocus() | |
| 68 | |
| 69 @pyqtSlot() | |
| 70 def on_executeButton_clicked(self): | |
| 71 """ | |
| 72 Private slot to execute the entered SQL query. | |
| 73 """ | |
| 74 self.executeQuery() | |
| 75 self.sqlEdit.setFocus() | |
| 76 | |
| 77 @pyqtSlot() | |
| 78 def on_insertRowAction_triggered(self): | |
| 79 """ | |
| 80 Private slot handling the action to insert a new row. | |
| 81 """ | |
| 82 self.__insertRow() | |
| 83 | |
| 84 @pyqtSlot() | |
| 85 def on_deleteRowAction_triggered(self): | |
| 86 """ | |
| 87 Private slot handling the action to delete a row. | |
| 88 """ | |
| 89 self.__deleteRow() | |
| 90 | |
| 91 @pyqtSlot(str) | |
| 92 def on_connections_tableActivated(self, table): | |
| 93 """ | |
| 94 Private slot to show the contents of a table. | |
| 95 | |
| 96 @param table name of the table for which to show the contents (string) | |
| 97 """ | |
| 98 self.showTable(table) | |
| 99 | |
| 100 @pyqtSlot(str) | |
| 101 def on_connections_schemaRequested(self, table): | |
| 102 """ | |
| 103 Private slot to show the schema of a table. | |
| 104 | |
| 105 @param table name of the table for which to show the schema (string) | |
| 106 """ | |
| 107 self.showSchema(table) | |
| 108 | |
| 109 @pyqtSlot() | |
| 110 def on_connections_cleared(self): | |
| 111 """ | |
| 112 Private slot to clear the table. | |
| 113 """ | |
| 114 model = QStandardItemModel(self.table) | |
| 115 self.table.setModel(model) | |
| 116 self.table.setEditTriggers( | |
| 117 QAbstractItemView.EditTrigger.NoEditTriggers) | |
| 118 | |
| 119 self.updateActions() | |
| 120 | |
| 121 def addConnection(self, driver, dbName, user, password, host, port): | |
| 122 """ | |
| 123 Public method to add a database connection. | |
| 124 | |
| 125 @param driver name of the Qt database driver (string) | |
| 126 @param dbName name of the database (string) | |
| 127 @param user user name (string) | |
| 128 @param password password (string) | |
| 129 @param host host name (string) | |
| 130 @param port port number (integer) | |
| 131 @return SQL error object (QSqlError) | |
| 132 """ | |
| 133 err = QSqlError() | |
| 134 | |
| 135 self.__class__.cCount += 1 | |
| 136 db = QSqlDatabase.addDatabase( | |
| 137 driver.upper(), "Browser{0:d}".format(self.__class__.cCount)) | |
| 138 db.setDatabaseName(dbName) | |
| 139 db.setHostName(host) | |
| 140 db.setPort(port) | |
| 141 if not db.open(user, password): | |
| 142 err = db.lastError() | |
| 143 db = QSqlDatabase() | |
| 144 QSqlDatabase.removeDatabase( | |
| 145 "Browser{0:d}".format(self.__class__.cCount)) | |
| 146 | |
| 147 self.connections.refresh() | |
| 148 | |
| 149 return err | |
| 150 | |
| 151 def addConnectionByDialog(self): | |
| 152 """ | |
| 153 Public slot to add a database connection via an input dialog. | |
| 154 """ | |
| 155 from .SqlConnectionDialog import SqlConnectionDialog | |
| 156 dlg = SqlConnectionDialog(self) | |
| 157 if dlg.exec() == QDialog.DialogCode.Accepted: | |
| 158 driver, dbName, user, password, host, port = dlg.getData() | |
| 159 err = self.addConnection( | |
| 160 driver, dbName, user, password, host, port) | |
| 161 | |
| 162 if err.type() != QSqlError.ErrorType.NoError: | |
| 163 E5MessageBox.warning( | |
| 164 self, | |
| 165 self.tr("Unable to open database"), | |
| 166 self.tr( | |
| 167 """An error occurred while opening the connection.""")) | |
| 168 | |
| 169 def showTable(self, table): | |
| 170 """ | |
| 171 Public slot to show the contents of a table. | |
| 172 | |
| 173 @param table name of the table to be shown (string) | |
| 174 """ | |
| 175 model = QSqlTableModel(self.table, self.connections.currentDatabase()) | |
| 176 model.setEditStrategy(QSqlTableModel.EditStrategy.OnRowChange) | |
| 177 model.setTable(table) | |
| 178 model.select() | |
| 179 if model.lastError().type() != QSqlError.ErrorType.NoError: | |
| 180 self.statusMessage.emit(model.lastError().text()) | |
| 181 self.table.setModel(model) | |
| 182 self.table.setEditTriggers( | |
| 183 QAbstractItemView.EditTrigger.DoubleClicked | | |
| 184 QAbstractItemView.EditTrigger.EditKeyPressed) | |
| 185 | |
| 186 self.table.resizeColumnsToContents() | |
| 187 | |
| 188 self.table.selectionModel().currentRowChanged.connect( | |
| 189 self.updateActions) | |
| 190 | |
| 191 self.updateActions() | |
| 192 | |
| 193 def showSchema(self, table): | |
| 194 """ | |
| 195 Public slot to show the schema of a table. | |
| 196 | |
| 197 @param table name of the table to be shown (string) | |
| 198 """ | |
| 199 rec = self.connections.currentDatabase().record(table) | |
| 200 model = QStandardItemModel(self.table) | |
| 201 | |
| 202 model.insertRows(0, rec.count()) | |
| 203 model.insertColumns(0, 7) | |
| 204 | |
| 205 model.setHeaderData(0, Qt.Orientation.Horizontal, "Fieldname") | |
| 206 model.setHeaderData(1, Qt.Orientation.Horizontal, "Type") | |
| 207 model.setHeaderData(2, Qt.Orientation.Horizontal, "Length") | |
| 208 model.setHeaderData(3, Qt.Orientation.Horizontal, "Precision") | |
| 209 model.setHeaderData(4, Qt.Orientation.Horizontal, "Required") | |
| 210 model.setHeaderData(5, Qt.Orientation.Horizontal, "Auto Value") | |
| 211 model.setHeaderData(6, Qt.Orientation.Horizontal, "Default Value") | |
| 212 | |
| 213 for i in range(rec.count()): | |
| 214 fld = rec.field(i) | |
| 215 model.setData(model.index(i, 0), fld.name()) | |
| 216 if fld.typeID() == -1: | |
| 217 model.setData(model.index(i, 1), | |
| 218 QVariant.typeToName(fld.type())) | |
| 219 else: | |
| 220 model.setData( | |
| 221 model.index(i, 1), "{0} ({1})".format( | |
| 222 QVariant.typeToName(fld.type()), fld.typeID())) | |
| 223 if fld.length() < 0: | |
| 224 model.setData(model.index(i, 2), "?") | |
| 225 else: | |
| 226 model.setData(model.index(i, 2), fld.length()) | |
| 227 if fld.precision() < 0: | |
| 228 model.setData(model.index(i, 3), "?") | |
| 229 else: | |
| 230 model.setData(model.index(i, 3), fld.precision()) | |
| 231 if fld.requiredStatus() == -1: | |
| 232 model.setData(model.index(i, 4), "?") | |
| 233 else: | |
| 234 model.setData(model.index(i, 4), bool(fld.requiredStatus())) | |
| 235 model.setData(model.index(i, 5), fld.isAutoValue()) | |
| 236 model.setData(model.index(i, 6), fld.defaultValue()) | |
| 237 | |
| 238 self.table.setModel(model) | |
| 239 self.table.setEditTriggers( | |
| 240 QAbstractItemView.EditTrigger.NoEditTriggers) | |
| 241 | |
| 242 self.table.resizeColumnsToContents() | |
| 243 | |
| 244 self.updateActions() | |
| 245 | |
| 246 def updateActions(self): | |
| 247 """ | |
| 248 Public slot to update the actions. | |
| 249 """ | |
| 250 enableIns = isinstance(self.table.model(), QSqlTableModel) | |
| 251 enableDel = enableIns & self.table.currentIndex().isValid() | |
| 252 | |
| 253 self.insertRowAction.setEnabled(enableIns) | |
| 254 self.deleteRowAction.setEnabled(enableDel) | |
| 255 | |
| 256 def __insertRow(self): | |
| 257 """ | |
| 258 Private slot to insert a row into the database table. | |
| 259 """ | |
| 260 model = self.table.model() | |
| 261 if not isinstance(model, QSqlTableModel): | |
| 262 return | |
| 263 | |
| 264 insertIndex = self.table.currentIndex() | |
| 265 row = 0 if insertIndex.row() == -1 else insertIndex.row() | |
| 266 model.insertRow(row) | |
| 267 insertIndex = model.index(row, 0) | |
| 268 self.table.setCurrentIndex(insertIndex) | |
| 269 self.table.edit(insertIndex) | |
| 270 | |
| 271 def __deleteRow(self): | |
| 272 """ | |
| 273 Private slot to delete a row from the database table. | |
| 274 """ | |
| 275 model = self.table.model() | |
| 276 if not isinstance(model, QSqlTableModel): | |
| 277 return | |
| 278 | |
| 279 model.setEditStrategy(QSqlTableModel.EditStrategy.OnManualSubmit) | |
| 280 | |
| 281 currentSelection = self.table.selectionModel().selectedIndexes() | |
| 282 for selectedIndex in currentSelection: | |
| 283 if selectedIndex.column() != 0: | |
| 284 continue | |
| 285 model.removeRow(selectedIndex.row()) | |
| 286 | |
| 287 model.submitAll() | |
| 288 model.setEditStrategy(QSqlTableModel.EditStrategy.OnRowChange) | |
| 289 | |
| 290 self.updateActions() | |
| 291 | |
| 292 def executeQuery(self): | |
| 293 """ | |
| 294 Public slot to execute the entered query. | |
| 295 """ | |
| 296 model = QSqlQueryModel(self.table) | |
| 297 model.setQuery(QSqlQuery( | |
| 298 self.sqlEdit.toPlainText(), self.connections.currentDatabase())) | |
| 299 self.table.setModel(model) | |
| 300 | |
| 301 if model.lastError().type() != QSqlError.ErrorType.NoError: | |
| 302 self.statusMessage.emit(model.lastError().text()) | |
| 303 elif model.query().isSelect(): | |
| 304 self.statusMessage.emit(self.tr("Query OK.")) | |
| 305 else: | |
| 306 self.statusMessage.emit( | |
| 307 self.tr("Query OK, number of affected rows: {0}") | |
| 308 .format(model.query().numRowsAffected())) | |
| 309 | |
| 310 self.table.resizeColumnsToContents() | |
| 311 | |
| 312 self.updateActions() |
