Mar-27-2023, 10:04 AM
You can use
This is an old project where I used it. Double click and edit cell.
model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)This is an old project where I used it. Double click and edit cell.
from PyQt5 import QtSql, QtPrintSupport
from PyQt5.QtGui import QTextDocument, QIcon, QTextCursor, QTextTableFormat
from PyQt5.QtCore import QFileInfo, Qt, QSettings, QSize, QFile, QTextStream
from PyQt5.QtWidgets import (QMainWindow, QTableView, QDialog, QGridLayout, QPushButton,
QLineEdit, QWidget, QFileDialog, QComboBox, QMessageBox, QApplication)
import sys
###################################
class MyWindow(QMainWindow):
def __init__(self, parent=None):
super(MyWindow, self).__init__()
self.setObjectName("SqliteViewer")
root = QFileInfo(__file__).absolutePath()
self.setAttribute(Qt.WA_DeleteOnClose)
self.settings = QSettings('Axel Schneider', self.objectName())
self.viewer = QTableView()
self.db = QtSql.QSqlDatabase.addDatabase('QSQLITE')
self.model = QtSql.QSqlTableModel()
self.delrow = -1
self.dbfile = ""
self.tablename = ""
self.headers = []
self.results = ""
self.mycolumn = 0
self.viewer.verticalHeader().setVisible(False)
self.setStyleSheet(stylesheet(self))
self.viewer.setModel(self.model)
self.viewer.clicked.connect(self.findrow)
self.viewer.selectionModel().selectionChanged.connect(self.getCellText)
self.dlg = QDialog()
self.layout = QGridLayout()
self.layout.addWidget(self.viewer,0, 0, 1, 4)
addBtn = QPushButton("insert row")
addBtn.setIcon(QIcon.fromTheme("add"))
addBtn.setFixedWidth(110)
addBtn.clicked.connect(self.addrow)
self.layout.addWidget(addBtn, 1, 0)
delBtn = QPushButton("delete row")
delBtn.setIcon(QIcon.fromTheme("remove"))
delBtn.setFixedWidth(110)
delBtn.clicked.connect(self.deleteRow)
self.layout.addWidget(delBtn,1, 1)
self.editor = QLineEdit()
self.editor.returnPressed.connect(self.editCell)
self.editor.setStatusTip("ENTER new value")
self.editor.setToolTip("ENTER new value")
self.layout.addWidget(self.editor,1, 2)
self.findfield = QLineEdit()
self.findfield.addAction(QIcon.fromTheme("edit-find"), 0)
self.findfield.returnPressed.connect(self.findCell)
self.findfield.setFixedWidth(200)
self.findfield.setPlaceholderText("find")
self.findfield.setStatusTip("ENTER to find")
self.findfield.setToolTip("ENTER to find")
self.layout.addWidget(self.findfield,1, 3)
self.myWidget = QWidget()
self.myWidget.setLayout(self.layout)
self.createToolbar()
self.statusBar().showMessage("Ready")
self.setCentralWidget(self.myWidget)
self.setWindowIcon(QIcon.fromTheme("office-database"))
self.setGeometry(20,20,600,450)
self.setWindowTitle("SqliteViewer")
self.readSettings()
self.msg("Ready")
self.viewer.setFocus()
def createToolbar(self):
self.actionOpen = QPushButton("Open DB")
self.actionOpen.clicked.connect(self.fileOpen)
icon = QIcon.fromTheme("document-open")
self.actionOpen.setShortcut("Ctrl+O")
self.actionOpen.setShortcutEnabled(True)
self.actionOpen.setIcon(icon)
self.actionOpen.setObjectName("actionOpen")
self.actionOpen.setStatusTip("Open Database")
self.actionOpen.setToolTip("Open Database")
self.actionHide = QPushButton()
self.actionHide.clicked.connect(self.toggleVerticalHeaders)
icon = QIcon.fromTheme("pane-hide-symbolic")
self.actionHide.setIcon(icon)
self.actionHide.setToolTip("toggle vertical Headers")
self.actionHide.setShortcut("F3")
self.actionHide.setShortcutEnabled(True)
self.actionHide.setStatusTip("toggle vertical Headers")
### first row as headers
self.actionHeaders = QPushButton()
self.actionHeaders.clicked.connect(self.selectedRowToHeaders)
icon = QIcon.fromTheme("ok")
self.actionHeaders.setIcon(icon)
self.actionHeaders.setToolTip("selected row to headers")
self.actionHeaders.setShortcut("F5")
self.actionHeaders.setShortcutEnabled(True)
self.actionHeaders.setStatusTip("selected row to headers")
self.actionPreview = QPushButton()
self.actionPreview.clicked.connect(self.handlePreview)
icon = QIcon.fromTheme("document-print-preview")
self.actionPreview.setShortcut("Shift+Ctrl+P")
self.actionPreview.setShortcutEnabled(True)
self.actionPreview.setIcon(icon)
self.actionPreview.setObjectName("actionPreview")
self.actionPreview.setStatusTip("Print Preview")
self.actionPreview.setToolTip("Print Preview")
self.actionPrint = QPushButton()
self.actionPrint.clicked.connect(self.handlePrint)
icon = QIcon.fromTheme("document-print")
self.actionPrint.setShortcut("Shift+Ctrl+P")
self.actionPrint.setShortcutEnabled(True)
self.actionPrint.setIcon(icon)
self.actionPrint.setObjectName("actionPrint")
self.actionPrint.setStatusTip("Print")
self.actionPrint.setToolTip("Print")
###############################
self.tb = self.addToolBar("ToolBar")
self.tb.setIconSize(QSize(16, 16))
self.tb.setMovable(False)
self.tb.addWidget(self.actionOpen)
self.tb.addSeparator()
self.tb.addWidget(self.actionPreview)
self.tb.addWidget(self.actionPrint)
### sep
self.tb.addSeparator()
self.tb.addSeparator()
### popupMenu
self.pop = QComboBox()
self.pop.setFixedWidth(200)
self.pop.currentIndexChanged.connect(self.setTableName)
self.tb.addWidget(self.pop)
self.tb.addSeparator()
self.tb.addWidget(self.actionHide)
self.addToolBar(self.tb)
def deleteRow(self):
row = self.viewer.currentIndex().row()
self.model.removeRow(row)
self.initializeModel()
self.viewer.selectRow(row)
def selectedRowToHeaders(self):
if self.model.rowCount() > 0:
headers = []
row = self.selectedRow()
for column in range(self.model.columnCount()):
headers.append(self.model.data(self.model.index(row, column)))
self.model.setHeaderData(column, Qt.Horizontal, headers[column], Qt.EditRole)
print(headers)
def findCell(self):
column = 0
ftext = self.findfield.text()
model = self.viewer.model()
if self.viewer.selectionModel().hasSelection():
row = self.viewer.selectionModel().selectedIndexes()[0].row()
row = row + 1
else:
row = 0
start = model.index(row, column)
matches = model.match(start, Qt.DisplayRole,ftext, 1, Qt.MatchContains)
if matches:
print("found", ftext, matches)
index = matches[0]
self.viewer.selectionModel().select(index, QItemSelectionModel.Select)
else:
column = column + 1
self.findNextCell(column)
def findNextCell(self, column):
self.viewer.clearSelection()
ftext = self.findfield.text()
model = self.viewer.model()
if self.viewer.selectionModel().hasSelection():
row = self.viewer.selectionModel().selectedIndexes()[0].row()
row = row + 1
else:
row = 0
start = model.index(row, column)
matches = model.match(start, Qt.DisplayRole,ftext, 1, Qt.MatchContains)
if matches:
print("found", ftext)
index = matches[0]
self.viewer.selectionModel().select(index, QItemSelectionModel.Select)
else:
column = column + 1
self.findNextCell(column)
def toggleVerticalHeaders(self):
if self.viewer.verticalHeader().isVisible() == False:
self.viewer.verticalHeader().setVisible(True)
else:
self.viewer.verticalHeader().setVisible(False)
def fileOpen(self):
tablelist = []
fileName, _ = QFileDialog.getOpenFileName(None, "Open Database File", "/home/brian/Dokumente/DB", "DB (*.sqlite *.db *.sql3);; All Files (*.*)")
if fileName:
self.fileOpenStartup(fileName)
def fileOpenStartup(self, fileName):
tablelist = []
if fileName:
self.db.close()
self.dbfile = fileName
self.db.setDatabaseName(self.dbfile)
self.db.open()
print("Tables:", self.db.tables())
tablelist = self.db.tables()
self.fillComboBox(tablelist)
self.msg("please choose Table from the ComboBox")
def setAutoWidth(self):
self.viewer.resizeColumnsToContents()
def fillComboBox(self, tablelist):
self.pop.clear()
self.pop.insertItem(0, "choose Table ...")
self.pop.setCurrentIndex(0)
for row in tablelist:
self.pop.insertItem(self.pop.count(), row)
if self.pop.count() > 1:
self.pop.setCurrentIndex(1)
self.setTableName()
def getCellText(self):
if self.viewer.selectionModel().hasSelection():
item = self.viewer.selectedIndexes()[0]
if not item == None:
name = item.data()
else:
name = ""
self.editor.setText(str(name))
else:
self.editor.clear()
def editCell(self):
item = self.viewer.selectedIndexes()[0]
row = self.selectedRow()
column = self.selectedColumn()
self.model.setData(item, self.editor.text())
def setTableName(self):
if not self.pop.currentText() == "choose Table ...":
self.tablename = self.pop.currentText()
print("DB is:", self.dbfile)
self.msg("initialize")
self.initializeModel()
def initializeModel(self):
print("Table selected:", self.tablename)
self.model.setTable(self.tablename)
self.model.setEditStrategy(QtSql.QSqlTableModel.OnFieldChange)
self.model.select()
self.setAutoWidth()
self.msg(self.tablename + " loaded *** " + str(self.model.rowCount()) + " records")
def addrow(self):
row = self.viewer.selectionModel().selectedIndexes()[0].row()
ret = self.model.insertRow(row)
if ret:
self.viewer.selectRow(row)
item = self.viewer.selectedIndexes()[0]
self.model.setData(item, str(row))
def findrow(self, i):
self.delrow = i.row()
def selectedRow(self):
if self.viewer.selectionModel().hasSelection():
row = self.viewer.selectionModel().selectedIndexes()[0].row()
return int(row)
def selectedColumn(self):
column = self.viewer.selectionModel().selectedIndexes()[0].column()
return int(column)
def closeEvent(self, e):
self.writeSettings()
e.accept()
def readSettings(self):
print("reading settings")
if self.settings.contains('geometry'):
self.setGeometry(self.settings.value('geometry'))
def writeSettings(self):
print("writing settings")
self.settings.setValue('geometry', self.geometry())
def msg(self, message):
self.statusBar().showMessage(message)
def handlePrint(self):
if self.model.rowCount() == 0:
self.msg("no rows")
else:
dialog = QtPrintSupport.QPrintDialog()
if dialog.exec_() == QDialog.Accepted:
self.handlePaintRequest(dialog.printer())
self.msg("Document printed")
def handlePreview(self):
if self.model.rowCount() == 0:
self.msg("no rows")
else:
dialog = QtPrintSupport.QPrintPreviewDialog()
dialog.setFixedSize(1000,700)
dialog.paintRequested.connect(self.handlePaintRequest)
dialog.exec_()
self.msg("Print Preview closed")
def handlePaintRequest(self, printer):
printer.setDocName(self.tablename)
document = QTextDocument()
cursor = QTextCursor(document)
model = self.viewer.model()
tableFormat = QTextTableFormat()
tableFormat.setBorder(0.2)
tableFormat.setBorderStyle(3)
tableFormat.setCellSpacing(0);
tableFormat.setTopMargin(0);
tableFormat.setCellPadding(4)
table = cursor.insertTable(model.rowCount() + 1, model.columnCount(), tableFormat)
model = self.viewer.model()
### get headers
myheaders = []
for i in range(0, model.columnCount()):
myheader = model.headerData(i, Qt.Horizontal)
cursor.insertText(myheader)
cursor.movePosition(QTextCursor.NextCell)
### get cells
for row in range(0, model.rowCount()):
for col in range(0, model.columnCount()):
index = model.index( row, col )
cursor.insertText(str(index.data()))
cursor.movePosition(QTextCursor.NextCell)
document.print_(printer)
def stylesheet(self):
return """
QTableView
{
border: 1px solid grey;
border-radius: 0px;
font-size: 8pt;
background-color: #e8eaf3;
selection-color: #ffffff;
}
QTableView::item:hover
{
color: black;
background: qlineargradient(x1:0, y1:0, x2:1, y2:1, stop:0 #729fcf, stop:1 #d3d7cf);
}
QTableView::item:selected
{
color: #F4F4F4;
background: qlineargradient(x1:0, y1:0, x2:1, y2:1, stop:0 #6169e1, stop:1 #3465a4);
}
QStatusBar
{
font-size: 7pt;
color: #57579e;
}
QPushButton
{
font-size: 8pt;
icon-size: 16px;
}
QPushButton:hover
{
color: black;
background: qlineargradient(x1:0, y1:0, x2:1, y2:1, stop:0 #729fcf, stop:1 #d3d7cf);
border: 1px solid #b7b7b7 inset;
border-radius: 3px;
}
QComboBox
{
font-size: 8pt;
}
"""
###################################
if __name__ == "__main__":
app = QApplication(sys.argv)
app.setApplicationName('MyWindow')
main = MyWindow("")
main.show()
if len(sys.argv) > 1:
print(sys.argv[1])
main.fileOpenStartup(sys.argv[1])
sys.exit(app.exec_())