DBViewer (sqlite)

[Axel_Erfurt]

open sqlite Database -> select Table from ComboBox
export to tsv or csv
print table

code on github

#!/usr/bin/python3
# -*- coding: utf-8 -*-

btnWidth = 100
################################################
from PyQt5 import QtCore, QtGui, QtWidgets, QtPrintSupport
import sqlite3
import csv
################################################
class MainWindow(QtWidgets.QMainWindow):
    def __init__(self):
        super(MainWindow, self).__init__()
        root = QtCore.QFileInfo(__file__).absolutePath()
        self.setObjectName("MyDBViewer")
        self.setGeometry(10, 10, 800, 600)
        self.setAttribute(QtCore.Qt.WA_DeleteOnClose)
        self.setMinimumSize(QtCore.QSize(300, 200))
        self.setWindowIcon(QtGui.QIcon.fromTheme("office-database"))
        self.setDocumentMode(True)
        self.settings = QtCore.QSettings('Axel Schneider', self.objectName())
        self.dbfile = ""
        self.tablename = ""
        self.createMenuBar()
        self.createToolbar()
        QtCore.QMetaObject.connectSlotsByName(self)

        self.viewer = QtWidgets.QTableWidget()
        self.viewer.setStyleSheet(stylesheet(self))        
        self.viewer.selectionModel().selectionChanged.connect(self.getCellText)
  
        self.setCentralWidget(self.viewer)

        self.readSettings()
        self.statusbar.showMessage("Ready")

    def createMenuBar(self):
        self.menubar = QtWidgets.QMenuBar(self)
        self.menubar.setObjectName("menubar")
        self.menuFile = QtWidgets.QMenu(self.menubar)
        self.menuFile.setObjectName("menuFile")
        self.menuHelp = QtWidgets.QMenu(self.menubar)
        self.menuHelp.setObjectName("menuHelp")
        self.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(self)
        font = QtGui.QFont()
        font.setPointSize(7)
        self.statusbar.setFont(font)
        self.statusbar.setObjectName("statusbar")
        self.setStatusBar(self.statusbar)
        self.actionNew = QtWidgets.QAction(self, triggered = self.fileNew)
        icon = QtGui.QIcon.fromTheme("document-new")
        self.actionNew.setIcon(icon)
        self.actionNew.setObjectName("actionNew")
        self.actionOpen = QtWidgets.QAction(self, triggered = self.fileOpen)
        icon = QtGui.QIcon.fromTheme("document-open")
        self.actionOpen.setIcon(icon)
        self.actionOpen.setObjectName("actionOpen")

        self.actionSave_as = QtWidgets.QAction(self, triggered = self.fileSaveTab)
        icon = QtGui.QIcon.fromTheme("document-save-as")
        self.actionSave_as.setIcon(icon)
        self.actionSave_as.setObjectName("actionSave_as")

        self.actionSave_comma = QtWidgets.QAction(self, triggered = self.fileSaveComma)
        icon = QtGui.QIcon.fromTheme("document-save-as")
        self.actionSave_comma.setIcon(icon)
        self.actionSave_comma.setObjectName("actionSave_comma")

        self.actionPrint_preview = QtWidgets.QAction(self, triggered = self.handlePreview)
        icon = QtGui.QIcon.fromTheme("document-print-preview")
        self.actionPrint_preview.setIcon(icon)
        self.actionPrint_preview.setObjectName("actionPrint_preview")

        self.actionPrint = QtWidgets.QAction(self, triggered = self.handlePrint)
        icon = QtGui.QIcon.fromTheme("document-print")
        self.actionPrint.setIcon(icon)
        self.actionPrint.setObjectName("actionPrint")

        self.actionExit = QtWidgets.QAction(self, triggered = self.appClose)
        icon = QtGui.QIcon.fromTheme("application-exit")
        self.actionExit.setIcon(icon)
        self.actionExit.setShortcutContext(QtCore.Qt.ApplicationShortcut)
        self.actionExit.setObjectName("actionExit")

        self.actionAbout = QtWidgets.QAction(self, triggered = self.about)
        icon = QtGui.QIcon.fromTheme("help-about")
        self.actionAbout.setIcon(icon)

        self.actionHide = QtWidgets.QAction(self, triggered = self.toggleVerticalHeaders)
        icon = QtGui.QIcon.fromTheme("pane-hide-symbolic")
        self.actionHide.setIcon(icon)
        ###############################
        self.menuFile.addAction(self.actionNew)
        self.menuFile.addAction(self.actionOpen)
        self.menuFile.addAction(self.actionSave_as)
        self.menuFile.addAction(self.actionSave_comma)
        self.menuFile.addSeparator()
        self.menuFile.addAction(self.actionPrint_preview)
        self.menuFile.addAction(self.actionPrint)
        self.menuFile.addSeparator()
        self.menuFile.addAction(self.actionExit)
        ### help ###
        self.menuHelp.addAction(self.actionAbout)
        ##########
        self.menubar.addAction(self.menuFile.menuAction())
#        self.menubar.addAction(self.menuEdit.menuAction())
        self.menubar.addAction(self.menuHelp.menuAction())
        self.setupMenu()

    def setupMenu(self):
        _translate = QtCore.QCoreApplication.translate
        self.setWindowTitle(_translate("MyDBViewer", "MyDBViewer"))
        self.menuFile.setTitle(_translate("MainWindow", "File"))
        self.menuHelp.setTitle(_translate("MainWindow", "Help"))

        self.actionNew.setText(_translate("MainWindow", "New"))
        self.actionNew.setShortcut(_translate("MainWindow", "Ctrl+N"))

        self.actionOpen.setText(_translate("MainWindow", "Open"))
        self.actionOpen.setShortcut(_translate("MainWindow", "Ctrl+O"))

        self.actionSave_as.setText(_translate("MainWindow", "Export TSV (tab delimited Text)"))
        self.actionSave_as.setShortcut(_translate("MainWindow", "Ctrl+S"))
        self.actionSave_as.setStatusTip(_translate("MainWindow", "Export TSV (tab delimited Text)"))

        self.actionSave_comma.setText(_translate("MainWindow", "Export CSV (comma delimited Text)"))
        self.actionSave_comma.setShortcut(_translate("MainWindow", "Shift+Ctrl+S"))
        self.actionSave_comma.setStatusTip(_translate("MainWindow", "Export CSV (comma delimited Text)"))

        self.actionPrint_preview.setText(_translate("MainWindow", "Print Preview"))
        self.actionPrint_preview.setShortcut(_translate("MainWindow", "Shift+Ctrl+P"))
        self.actionPrint_preview.setStatusTip(_translate("MainWindow", "Print Preview"))

        self.actionPrint.setText(_translate("MainWindow", "Print"))
        self.actionPrint.setShortcut(_translate("MainWindow", "Ctrl+P"))
        self.actionPrint.setStatusTip(_translate("MainWindow", "Print"))

        self.actionExit.setText(_translate("MainWindow", "Exit"))
        self.actionExit.setShortcut(_translate("MainWindow", "Ctrl+Q"))
        self.actionAbout.setText(_translate("MainWindow", "about " + self.objectName()))
        self.actionAbout.setStatusTip(_translate("MainWindow", "about " + self.objectName()))

        self.actionHide.setText(_translate("MainWindow", "toggle vertical Headers"))
        self.actionHide.setShortcut(_translate("MainWindow", "F3"))
        self.actionHide.setStatusTip(_translate("MainWindow", "toggle vertical Headers"))

    def createToolbar(self):
        self.tb = QtWidgets.QToolBar("ToolBar")
        self.tb.setMovable(False)
        self.tb.addAction(self.actionOpen)
        self.tb.addAction(self.actionSave_as)
        self.tb.addAction(self.actionSave_comma)
        ### sep
        self.tb.addSeparator()
        self.tb.addAction(self.actionPrint_preview)
        self.tb.addAction(self.actionPrint)
        self.tb.addSeparator()
        ### popupMenu
        self.pop = QtWidgets.QComboBox()
        self.pop.setFixedHeight(26)
        self.pop.setFixedWidth(200)
        self.pop.activated[str].connect(self.setTableName)
        self.tb.addWidget(self.pop)
        self.tb.addSeparator()
        ### btn columnWidth
        self.cw = QtWidgets.QPushButton("auto width")
        self.cw.setFixedWidth(btnWidth)
        self.cw.clicked.connect(self.setAutoWidth)
        self.tb.addWidget(self.cw)

        self.tb.addSeparator()
        self.tb.addAction(self.actionHide)
        self.addToolBar(self.tb)

    def toggleVerticalHeaders(self):
        if self.viewer.verticalHeader().isVisible() == False:
            self.viewer.verticalHeader().setVisible(True)
        else:
            self.viewer.verticalHeader().setVisible(False)

    def fillComboBox(self, tablelist):
        self.pop.clear()
        self.pop.insertItem(0, "choose Table ...")
        for row in tablelist:
            self.pop.insertItem(self.pop.count(), row)

    def setTableName(self):
        self.tablename = self.pop.currentText()
        self.msg(self.tablename)
        self.makeList()

    def setAutoWidth(self):
        if not self.viewer.rowCount() == 0:
            self.viewer.resizeColumnsToContents()
            self.viewer.resizeRowsToContents()
        else:
            self.msg("nothing to do!")

    def fileNew(self):
        self.msg("new")

    def fileOpen(self):
        tablelist = []
        fileName, _ = QtWidgets.QFileDialog.getOpenFileName(None, "Open DataBase", "", "Tables (*.sqlite *.db *.sql3)")
        if fileName:
            self.dbfile = fileName
            conn = sqlite3.connect(self.dbfile)
            cur = conn.cursor()
            res = conn.execute("SELECT name FROM sqlite_master WHERE type='table';")
            for name in res:
                print (name[0])
                tablelist.append(name[0])
        self.fillComboBox(tablelist)
        self.msg("please choose Table from the ComboBox")

    def fileSaveTab(self):
        if not self.viewer.rowCount() == 0:
            self.msg("exporting Table")
            conn=sqlite3.connect(self.dbfile)
            c=conn.cursor()
            data = c.execute("SELECT * FROM " + self.tablename)
            headers = [description[0] for description in c.description]
            fileName, _ = QtWidgets.QFileDialog.getSaveFileName(None, "Export Table to CSV", self.tablename + ".tsv", "CSV Files (*.csv *.tsv)")
            if fileName:
                with open(fileName, 'w') as f:
                    writer = csv.writer(f, delimiter = '\t')
                    writer.writerow(headers)
                    writer.writerows(data)
        else:
            self.msg("nothing to export")

    def fileSaveComma(self):
        if not self.viewer.rowCount() == 0:
            self.msg("exporting Table")
            conn=sqlite3.connect(self.dbfile)
            c=conn.cursor()
            data = c.execute("SELECT * FROM " + self.tablename)
            headers = [description[0] for description in c.description]
            fileName, _ = QtWidgets.QFileDialog.getSaveFileName(None, "Export Table to CSV", self.tablename + ".csv", "CSV Files (*.csv)")
            if fileName:
                with open(fileName, 'w') as f:
                    writer = csv.writer(f, delimiter = ',')
                    writer.writerow(headers)
                    writer.writerows(data)
        else:
            self.msg("nothing to export")

    def makeList(self):
        self.viewer.clear()
        self.viewer.clearContents()
        self.viewer.setRowCount(0)
        self.viewer.setColumnCount(0)
        if not self.pop.currentText() == "choose Table ...":
            conn=sqlite3.connect(self.dbfile)
            c=conn.cursor()
            cells = c.execute("SELECT * FROM " + self.tablename)
            mylist = cells.fetchall()
    #        print(mylist)
            for rowdata in mylist:
                row = self.viewer.rowCount()
                self.viewer.insertRow(row)
                if len(rowdata) == 0:
                    self.viewer.setColumnCount(len(rowdata) + 1)
                else:
                    self.viewer.setColumnCount(len(rowdata))
                for column, data in enumerate(rowdata):
                    item = QtWidgets.QTableWidgetItem(str(data))
                    self.viewer.setItem(row, column, item)
            self.viewer.selectRow(0)
            self.msg("Table '" + self.tablename + "' loaded")
            ### get headers
            headers = [description[0] for description in c.description]
            for column, headerdata in enumerate(headers):
                item = QtWidgets.QTableWidgetItem(str(headerdata))
                self.viewer.setHorizontalHeaderItem(column, item)
        else:
            self.msg("please choose Table")

    def fileSaveAs(self):
        self.fileSave()

    def appClose(self):
        self.close()

    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 getCellText(self):
        if self.viewer.selectionModel().hasSelection():
            item = self.viewer.selectedIndexes()[0]
            row = self.selectedRow()
            column = self.selectedColumn()
            if not item == None:
                name = item.data()
            else:
                name = ""
            self.msg(str(name))

    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 handlePrint(self):
        if self.viewer.rowCount() == 0:
            self.msg("no rows")
        else:
            dialog = QtPrintSupport.QPrintDialog()
            if dialog.exec_() == QtWidgets.QDialog.Accepted:
                self.handlePaintRequest(dialog.printer())
                self.msg("Document printed")

    def handlePreview(self):
        if self.viewer.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):
        # find empty cells
        count = self.viewer.rowCount() 
        for row in range(count):
            for column in range(self.viewer.columnCount()):
                myitem = self.viewer.item(row,column)
                if myitem is None:
                    item = QtWidgets.QTableWidgetItem("")
                    self.viewer.setItem(row, column, item)
        printer.setDocName(self.tablename)
        document = QtGui.QTextDocument()
        cursor = QtGui.QTextCursor(document)
        model = self.viewer.model()
        tableFormat = QtGui.QTextTableFormat()
        tableFormat.setBorder(0.1)
        tableFormat.setBorderStyle(3)
        tableFormat.setCellSpacing(0);
        tableFormat.setTopMargin(0);
        tableFormat.setCellPadding(4)
        table = cursor.insertTable(model.rowCount() + 1, model.columnCount(), tableFormat)
        for column in range(table.columns()):
            if not self.viewer.horizontalHeaderItem(column) is None:
                cursor.insertText(self.viewer.horizontalHeaderItem(column).text())
                cursor.movePosition(QtGui.QTextCursor.NextCell)
        for row in range(count):
            for column in range(table.columns()):
                cursor.insertText(self.viewer.item(row, column).text())
                cursor.movePosition(QtGui.QTextCursor.NextCell)
        document.print_(printer)

    def about(self):
        link = "<p><a title='Axel Schneider' href='http://goodoldsongs.jimdo.com' target='_blank'>Axel Schneider</a></p>"
        title = "about MyDBViewer"
        message = "<span style='color: #1F9CDD; font-size: 24pt;font-weight: bold;'\
                    >MyDBViewer 1.0</strong></span></p><h2>MyDBViewer</h2>created by <h3>" + link + "</h3> with PyQt5<br>" \
                    + "<br>Copyright © 2017 The Qt Company Ltd and other contributors." \
                    + "<br>Qt and the Qt logo are trademarks of The Qt Company Ltd."
        info = "<span style='color: #1F9CDD; font-size: 14pt;'>©2017 Axel Schneider</strong></span></p>"
        detail = "MyDBViewer 1.0"
        self.infobox(title, message, info, detail)

    def infobox(self,title, message, info, detail):
        QtWidgets.QMessageBox(QtWidgets.QMessageBox.Information, title, message, QtWidgets.QMessageBox.NoButton, self, QtCore.Qt.Dialog|QtCore.Qt.NoDropShadowWindowHint).show()        


def stylesheet(self):
        return """
        QTableWidget
        {
            border: 0px solid grey;
            border-radius: 0px;
            font-family: Helvetica;
            font-size: 9pt;
            background-color: #e8eaf3;
            selection-color: #ffffff
        }
        QTableWidget::item:hover
        {   
            color: black;
            background: qlineargradient(x1:0, y1:0, x2:1, y2:1, stop:0 #729fcf, stop:1 #d3d7cf);           
        }
        
        QTableWidget::item:selected 
        {
            color: #F4F4F4;
            background: qlineargradient(x1:0, y1:0, x2:1, y2:1, stop:0 #6169e1, stop:1 #3465a4);
        } 
    """
###################################    
if __name__ == '__main__':
    import sys
    app = QtWidgets.QApplication(sys.argv)
    mainWin = MainWindow()
    mainWin.show()
    sys.exit(app.exec_())