[PyQt] SSH port forwarding and connection to SQL Server in separate threads freezes

[jan_pips]

To begin with I have to say I was not sure where to put the question but it seems to be after all problem with QT Threading.

I am trying to incorporate into the application the modules that connect via SSH with remote host, establish port forwarding (forward the remote port on which SQL Server is listening) and then establish connection to SQL Server instance.

Below are the extracted parts of the code which:
1. defines the GUI (in QT5) and assigns actions to slots
2. provides framework for port forwarding
3. provides framework for database connection
4. executes the app

First part defines simple window to provide username and password to load rsa key and then connect to remote host via ssh and finally to establish port forwarding. App works when it only connects to remote host and sets up the port forwarding. I have checked that with database browser (a separate application). When I run just the ssh connection and port forwarding part I can easily work with database browser just like when using putty with port forwarding option.

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import os
import paramiko
import socket
import select
import socketserver as SocketServer
import sys
import traceback

import sqlalchemy as ORM

from PyQt5 import QtCore
from PyQt5 import QtGui
from PyQt5 import QtWidgets

"""
Define main window
"""
class guiWindowLogin(QtWidgets.QDialog):

    def __init__(self):
        super(guiWindowLogin, self).__init__()

        # Initialize imported variables and objects
        self.WorkerPool = QtCore.QThreadPool(self)
        self.ssh_forwarder = SSHForwarder()
        self.dbh_mssql = dbhMSSQL()

        # Initialize window and widgets
        self.init_winMain()

        # Initialize widgets
        self.init_lblUserName()
        self.init_ledUserName()
        self.init_lblUserPass()
        self.init_ledUserPass()
        self.init_lblConnection()
        self.init_btnLogin()
        self.init_btnWindowClose()

        # Show window
        self.open()

    def init_winMain(self):
        self.left = 200
        self.top = 100
        self.width = 340
        self.height = 170
        self.setWindowTitle("Login")
        self.setFixedSize(self.width, self.height)
        self.move(self.left, self.top)

    def init_lblUserName(self):
        self.lblUserName = QtWidgets.QLabel(self)
        self.lblUserName.setGeometry(10, 10, 120, 30)
        self.lblUserName.setFont(QtGui.QFont("Microsoft Sans Serif", 10))
        self.lblUserName.setText("Username:")

    def init_ledUserName(self):
        self.ledUserName = QtWidgets.QLineEdit(self)
        self.ledUserName.setFont(QtGui.QFont("Microsoft Sans Serif", 10))
        self.ledUserName.setGeometry(100, 10, 230, 30)

    def init_lblUserPass(self):
        self.lblUserPass = QtWidgets.QLabel(self)
        self.lblUserPass.setGeometry(10, 50, 120, 30)
        self.lblUserPass.setFont(QtGui.QFont("Microsoft Sans Serif", 10))
        self.lblUserPass.setText("Password:")

    def init_ledUserPass(self):
        self.ledUserPass = QtWidgets.QLineEdit(self)
        self.ledUserPass.setGeometry(100, 50, 230, 30)
        self.ledUserPass.setFont(QtGui.QFont("Microsoft Sans Serif", 10))
        self.ledUserPass.setEchoMode(QtWidgets.QLineEdit.Password)

    def init_lblConnection(self):
        self.lblConnection = QtWidgets.QLabel(self)
        self.lblConnection.setGeometry(10, 90, 320, 30)
        self.lblConnection.setFont(QtGui.QFont("Microsoft Sans Serif", 10, weight = QtGui.QFont.Bold))
        self.lblConnection.setAlignment(QtCore.Qt.AlignCenter)
        self.lblConnection.setFrameStyle(QtWidgets.QFrame.Panel)
        self.lblConnection.setFrameShadow(QtWidgets.QFrame.Sunken)

    def init_btnLogin(self):
        self.btnLogin = QtWidgets.QPushButton(self)
        self.btnLogin.setGeometry(10, 130, 150, 30)
        self.btnLogin.setFont(QtGui.QFont("Microsoft Sans Serif", 10))
        self.btnLogin.setText("Login")
        self.btnLogin.clicked.connect(self.slot_login)

    def init_btnWindowClose(self):
        self.btnWindowClose = QtWidgets.QPushButton(self)
        self.btnWindowClose.setGeometry(180, 130, 150, 30)
        self.btnWindowClose.setFont(QtGui.QFont("Microsoft Sans Serif", 11))
        self.btnWindowClose.setText("Close window")
        self.btnWindowClose.clicked.connect(self.slot_window_close)

    def slot_login(self):
        try:
            self.ssh_forwarder.key_load(self.ledUserName.text(), self.ledUserPass.text())
            self.ssh_forwarder.connect()
            self.SSHConnection = Worker(self.ssh_forwarder.tunnel_create)
            self.WorkerPool.start(self.SSHConnection)
        except Exception as ex:
            print("An exception of type {0} occurred. Arguments:\n{1!r}".format(type(ex).__name__, ex.args))
        finally:
            self.exe_ssh_connection_check()

        self.dbh_mssql.engine_create()
        try:
            self.MSSQLConnection = Worker(self.dbh_mssql.connection_check)
            self.WorkerPool.start(self.MSSQLConnection)
        except Exception as ex:
            print("An exception of type {0} occurred. Arguments:\n{1!r}".format(type(ex).__name__, ex.args))


    def slot_window_close(self):
        self.close()

    def exe_ssh_connection_check(self):
        hostname = self.ssh_forwarder.connection_check()
        if hostname == "unknown":
            self.lblConnection.setStyleSheet("color: rgb(0, 0, 0); background-color: rgb(255, 153, 153)")
            self.lblConnection.setText("No connection")
        else:
            self.lblConnection.setStyleSheet("color: rgb(0, 0, 0); background-color: rgb(153, 255, 102)")
            self.lblConnection.setText("Connected to: {}".format(hostname))
        self.lblConnection.repaint()

Part that causes the app to freeze is the one below.

self.dbh_mssql.engine_create()
    try:
        self.MSSQLConnection = Worker(self.dbh_mssql.connection_check)
        self.WorkerPool.start(self.MSSQLConnection)
    except Exception as ex:
        print("An exception of type {0} occurred. Arguments:\n{1!r}".format(type(ex).__name__, ex.args))

The other parts of the code are the following:

SSH and forwarding framework:

The part of the code below was taken with some cosmetic modification from

https://github.com/paramiko/paramiko/blo...forward.py

# --- SSH and port forwarding ---#
class ForwardServer(SocketServer.ThreadingTCPServer):
    daemon_threads = True
    allow_reuse_address = True

class Handler(SocketServer.BaseRequestHandler):
    def handle(self):
        try:
            self.channel = self.ssh_transport.open_channel(
                "direct-tcpip",
                (self.chain_host, self.chain_port),
                self.request.getpeername(),
            )
        except Exception as e:
            print('Incoming request to {0}:{1} failed: %s'.format(self.chain_host, self.chain_port, repr(e)))
            return
        if self.channel is None:
            print('Incoming request to {0}:{1} was rejected by the SSH server.'.format(self.chain_host, self.chain_port))
            return

        print('Connected!  Tunnel open {0} -> {1} -> {2}'.format(
                self.request.getpeername(),
                self.channel.getpeername(),
                (self.chain_host, self.chain_port),
            )
        )
        while True:
            r, w, x = select.select([self.request, self.channel], [], [])
            if self.request in r:
                data = self.request.recv(1024)
                if len(data) == 0:
                    break
                self.channel.send(data)
            if self.channel in r:
                data = self.channel.recv(1024)
                if len(data) == 0:
                    break
                self.request.send(data)

        self.peername = self.request.getpeername()
        self.channel.close()
        self.request.close()
        print('Tunnel closed from {0}'.format(self.peername))

class SSHForwarder():
    def __init__(self):
        self.client = paramiko.SSHClient()
        self.client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
        self.remote_host = "1.1.1.1"
        self.remote_port_ssh = 22
        self.remote_port_forward = 53425
        self.local_port_forward = 1433
        self.pkey_rsa = None
        self.key_username = None

    def key_load(self, key_username, key_password):
        self.key_username = key_username
        key_path = os.path.join("C:\\DATA\\ssh", "{0}{1}".format(self.key_username, "_rsa"))
        self.pkey_rsa = paramiko.RSAKey.from_private_key_file(
            key_path,
            password=key_password
        )

    def tunnel_create(self, callback_progress, callback_data):
        class SubHandler(Handler):
            chain_host = self.remote_host
            chain_port = int(self.remote_port_forward)
            ssh_transport = self.client.get_transport()

        ForwardServer(("", int(self.local_port_forward)), SubHandler).serve_forever()

    def connect(self):
        try:
            self.client.connect(
                self.remote_host,
                port=int(self.remote_port_ssh),
                username=self.key_username,
                pkey=self.pkey_rsa
            )
        except Exception as e:
            sys.exit(1)

    def connection_check(self):
        try:
            hostname = (self.client.get_transport().getpeername()[0])
            return hostname
        except Exception as e:
            return "unknown"

Database connection framework:

class dbhMSSQL(object):
    def __init__(self):
        self.dialect = "mssql"
        self.driver = "pymssql"
        self.username = "username"
        self.password = "password"
        self.host = "localhost"
        self.port = "1433"
        self.database = "Database"

    def engine_create(self):
        self.conn_string = "{0}+{1}://{2}:{3}@{4}:{5}/{6}".format(self.dialect, self.driver, self.username, self.password, self.host, self.port, self.database)
        self.engine = ORM.create_engine(self.conn_string)

    def connection_check(self, callback_progress, callback_data):
        try:
            dbc_main = self.engine.connect()
            dbc_main.execute(
                "SELECT * FROM sys.dm_exec_connections"
            ).fetchall()
            dbc_main.close()
            return True
        except Exception as ex:
            return False

Threading framework: the part of the code below was taken with some cosmetic modification from

https://www.learnpyqt.com/courses/concur...hreadpool/

class WorkerSignals(QtCore.QObject):
    error = QtCore.pyqtSignal(tuple)
    finished = QtCore.pyqtSignal()
    progress = QtCore.pyqtSignal(int)
    result = QtCore.pyqtSignal(object)
    data = QtCore.pyqtSignal(dict)


class Worker(QtCore.QRunnable):
    def __init__(self, fn, *args, **kwargs):
        super(Worker, self).__init__()

        # Store constructor arguments (re-used for processing)
        self.fn = fn
        self.args = args
        self.kwargs = kwargs
        self.signals = WorkerSignals()

        # Add the callback to our kwargs
        self.kwargs['callback_progress'] = self.signals.progress
        self.kwargs['callback_data'] = self.signals.data

    # @pyqtSlot()
    def run(self):
        '''
        Initialise the runner function with passed args, kwargs.
        '''

        # Retrieve args/kwargs here; and fire processing using them
        try:
            result = self.fn(
                *self.args,
                **self.kwargs
            )
        except:
            traceback.print_exc()
            exctype, value = sys.exc_info()[:2]
            self.signals.error.emit((exctype, value, traceback.format_exc()))
        else:
            self.signals.result.emit(result)  # Return the result of the processing
        finally:
            self.signals.finished.emit()  # Done

App execution

if __name__ == '__main__':

    app = QtWidgets.QApplication(sys.argv)
    window = guiWindowLogin()
    window.show()
    sys.exit(app.exec_())

If the connection to SQL Server is not put into thread the app freezes as well.

After some trials I have found out that the problem lies within the command

engine.connect()

that is the part of SQLAlchemy package. However I do not know if that is the fault in this command itself or in PYQT threading.