We sharpen a tool on PyQt

I needed a tool. Sharp, practical, versatile. Meets all my requirements and expands according to my desire.



But simple and convenient. It should be noted here that I am not a developer at the main job, so I don’t have a permanent programming environment on my work computer and, when required, I’ll write what it takes - bat, JScript, VBA in MSOffice (yes, this is Windows, corporate systems, there’s no bash and perl "out of the box"), macros in different software, etc. All this helps to solve the current problem, but the level and possibilities are not the same as we would like to have.
')
In short, I need an integrated environment with a built-in programming language, in which I could parse and convert files, climb into databases, receive reports, invoke web services, generate queries in jer, etc., etc.

You will say that now there are tools for every taste and color, just choose. Frog aka TOAD for Oracle, SoapUI for the bus, and GNU and Apache products for everything else.
But the problem is that all of them are specialized for one kind of activity, but on the other hand are too universal - much can be done, but with many actions. And if the opportunity in the product is missing, then you can not add it. Either the product is closed, or you need to develop / buy a plugin, or download the source code and understand them. And I needed a tool in which simple actions are done simply, but for complex ones, some time is spent first and then everything is simple again.

Therefore, I decided to assemble myself the simplest shell from which I would run the modules I needed. The shell will be expandable, and the modules are simple and as independent as possible from the shell.



As a programming language, you need to take something that does not require compilation, or at minimal cost to it, so that you can easily rebuild it for a specific task.

Javascript is good for small scripts and would work, but it doesn’t have a window interface, but I’m not interested in raising NodeJS locally for windows and fighting the browser.
Perl, PHP - the same problem.

Visual Basic and VBScript - well, this is under Windows. Yes, most of the corporate IT systems where I have the honor to work are Windows. And each has an Office and, therefore, VBA. But if you do something that you want to constantly use, then cross-platform.

The choice fell on Python + PyQt5. I learned about the existence of a language (besides Habr, of course) from the raspberry Raspberry Pi, where Python was preinstalled. The breakdown of the pen served as a bot for Telegram, looking for synonyms of phrases (on pymorphy2 and YARN, then I will describe if interested). And I already knew Qt.

pip3 install pyqt5 

To begin with, we will make a universal module for querying the database. And so that the request and its parameters are defined outside the module, in the ini-file, and the module is engaged in all the work with the interface, working with the database and displaying data.

Let's connect PyQt. Naming in Qt is strict, so importing everything in a row will not interfere.

 from PyQt5.QtCore import * from PyQt5.QtWidgets import * from PyQt5.QtSql import * 

To prevent error messages and Qt warnings from being lost, we connect the module with the message handler, as suggested here.

 import meshandler 

We will move the connection to the database into a separate module in order not to clog here.

 import dbpool 

Create a class based on QDialog (QWidget is also suitable, but in it the default buttons do not work)

 class PyExecutor(QDialog): def __init__(self, iniFile, parent=None): super(PyExecutor, self).__init__(parent) self.setWindowFlags(self.windowFlags() | Qt.WindowMinimizeButtonHint | Qt.WindowMaximizeButtonHint ) 

Fill the window, top to bottom

 self.topLay = QVBoxLayout(self) self.topLay.setContentsMargins(6,6,6,6) 

Layout with space for entering parameters and buttons

 self.lay = QFormLayout() self.topLay.addLayout(self.lay) 

Place for output

 self.resultLay = QVBoxLayout() self.topLay.addLayout(self.resultLay) 

And the status bar to

 self.bar = QStatusBar(self) self.topLay.addWidget(self.bar) 

Download the ini file. The load will be moved to a separate method, so that later it can be blocked, if necessary.

 self.loadIni(iniFile) def loadIni(self, iniFile): 

I use Qt tools to work with ini-files simply because I know how to do it there. In Python, there must be some ways too, but I didn’t dig. To avoid future problems with the Russian language, we will work in UTF-8 in all files.

 ini = QSettings(iniFile, QSettings.IniFormat) ini.setIniCodec("utf-8") 

Load the request parameters from the section "Input"

 ini.beginGroup("Input") for key in sorted(ini.childKeys()): 

The parameter is defined by the string "Name = Label: Default Value"
The name can be omitted along with a colon, then the interface will be Name.

 v = ini.value(key).split(':') if len(v)>1: paramTitle = v[0] paramValue = v[1] else: paramTitle = key paramValue = v[0] 

For each parameter, we create an input line, add it to ourselves, insert it with the label into the interface

 self.params.append([key, paramTitle, paramValue]) if paramTitle != '': le = QLineEdit() self.inputs[key] = le le.setText(paramValue) le.paramTitle = paramTitle self.lay.addRow(paramTitle, le) ini.endGroup() 

Read the parameters of the database connection from the section «DB»

 ini.beginGroup("DB") self.dbini = ini.value("DBConnect") if self.dbini == "this": self.dbini = iniFile ini.endGroup() 

And finally, read the text of the SQL query.

In the “Run” section, either there will be a “SQL” key with the query text itself (it is better to put it in quotes), or there will be an “SQLScript” key in which the sql file with the query is registered — this allows you to create multi-line queries. In addition, it is more convenient to edit requests in the file in FAR with the backlit Colorer.

Like ini, we assume that the sql file is in UTF-8 encoding, only for transcoding we will use 'utf-8-sig' to get rid of the BOM at the beginning of the file.

 ini.beginGroup("Run") if ini.contains("SQL"): self.sql = ini.value("SQL") else: f = QFile(ini.value("SQLScript")) f.open(QIODevice.ReadOnly) self.sql = str(f.readAll(),'utf-8-sig') ini.endGroup() 

The final touches - add a start button, arrange beautifully.

 self.runBtn = QPushButton("Run") self.runBtn.setDefault(True) self.btnLay = QHBoxLayout() self.btnLay.addStretch() self.btnLay.addWidget(self.runBtn) self.lay.addRow(self.btnLay) 

The button will assign our method that runs the request for execution

 self.runBtn.clicked.connect(self.run) 

Actually launch method

 def run(self): self.runBtn.setEnabled(False) # ,        self.clearResult() #  ,   

Let's go work with the database.

We receive object QSqlDatabase, it should be valid and open. And if not - oops, nothing happens.

 self.db = dbpool.openDatabase(self.dbini) if self.db == None or not self.db.isValid() or not self.db.isOpen(): print("No opened DB", self.dbini) self.endRun() return 

In Qt, essentially one way to work with database queries is QSqlQuery

 self.query = QSqlQuery(self.db) 

Parsing sql query, filling its parameters with values ​​from input lines

 self.query.prepare(self.sql) for p in self.params: key = p[0] if key in self.inputs: le = self.inputs[key] par = ':'+key self.query.bindValue(par, le.text()) 

In order not to wait until the request is executed, we will move its execution to a separate thread.

 self.tr = QueryRunner(self.query) self.tr.finished.connect(self.showQueryResult) self.tr.start(); 

When the stream ends, this method will execute.

 def showQueryResult(self): 

Create a QTableView label like this

 w = self.createTableView() 

But the model with the result of the query will be transmitted in the view not immediately, but through a proxy - this will give us the opportunity to sort the label by clicking on the column and make a search if necessary.

 w.sqlModel = QSqlQueryModel(w) w.sqlModel.setQuery(self.query) w.proxyModel = QSortFilterProxyModel(w) w.proxyModel.setSourceModel(w.sqlModel) w.setModel(w.proxyModel) self.resultLay.addWidget(w) self.endRun() 

Let's launch what we've got to check without a shell

 if __name__ == '__main__': #    Windows import os import PyQt5 import sys pyqt = os.path.dirname(PyQt5.__file__) QApplication.addLibraryPath(os.path.join(pyqt, "Qt", "plugins")) 

And actually launch

 app = QApplication(sys.argv) ex = PyExecutor("artists.ini") ex.show() sys.exit(app.exec_()) 

File artists.ini

 [Common] Title=  [Input] Name= ():%r% [DB] DBConnect=sqlite.ini [Run] SQL="SELECT * FROM artists where :Name = '' or artist like :Name" 

Checked - works



Now we need the actual launch shell.

In the shell, I want to see a tree of all my configured functions, and run them in separate windows. And that the windows were not modal, i.e. it was possible to switch between them and launch new ones.

For simplicity, we will use an MDI window, since Qt has everything for it. Reading the tree and its mapping is taken entirely from the PyQt example , so we will not dwell on it.

We only define that in the first column we have the name of the function displayed in the row of the tree, in the second - the description of the function, in the third - the ini-file passed to the module


artists.ini

I will show how to create the main window on QMainWindow

 class MainWindow(QMainWindow): def __init__(self, parent=None): super(MainWindow, self).__init__(parent) 

The main part of the MDI window is a special QMdiArea widget. It will live windows run modules.

 self.mdiArea = QMdiArea(self) self.setCentralWidget(self.mdiArea) 

Let's make the main menu, while with one item:

 self.mainMenu = QMenuBar(self) self.setMenuBar(self.mainMenu) m = self.mainMenu.addMenu("Window") a = m.addAction("Cascade windows") a.triggered.connect(self.mdiArea.cascadeSubWindows) 

The tree will be in the dock-panel on the left.

 self.treePanel = QDockWidget(" ", self) w = QWidget(self.treePanel) self.treePanel.setWidget(w) lay = QVBoxLayout(w) lay.setSpacing(1) lay.setContentsMargins(1,1,1,1) w.setLayout(lay) self.tree = TreeWidget(self.treePanel) lay.addWidget(self.tree) 

The description of the function will be displayed at the bottom (later)

 edit = QTextEdit(w) lay.addWidget(edit) 

For double-click in the tree, assign a handler and put the panel in the main window

 self.tree.activated.connect(self.handle_dblclick) self.addDockWidget(Qt.LeftDockWidgetArea, self.treePanel) 

The double click handler will take the name of the ini-file from the tree model, and create with it a class from the module. Our class is a widget, we insert it into the client part of the MDI window.

 def handle_dblclick(self, index): proc = index.data(Qt.UserRole) if proc != None: proc = proc.strip() ex = PyExecutor(proc) self.mdiArea.addSubWindow(ex) ex.show() 

Check - works:



Sources are posted on github under the MIT license. The link leads to the sources used for the article, and from the root you can get the latest version.

Tips:
1. PyQt, like Qt, does not include the QOCI binary driver needed to access Oracle. The driver must be compiled from the sources (C ++) supplied with Qt and put in PyQt5 \ Qt \ plugins \ sqldrivers. To build you need dll from Oracle Client.
2. Python is better to set so that the path to it does not contain Cyrillic. Otherwise, PyQt has a small roof, and it cannot find its files.

Continued:
- We sculpt a toolbar on PyQt, export data to Excel and HTML
- We cut XML by XQuery markup
- Preview documents in Python program