[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.

[Denni]

You are using the QThread concept for this I will try and look at this later to outline a better cleaner approach to what you are trying to do -- but if you are sub-classing a QThread you are either using the wrong kind of QThread or the wrong usage of QThread

[jan_pips]

You are using the QThread concept for this I will try and look at this later to outline a better cleaner approach to what you are trying to do -- but if you are sub-classing a QThread you are either using the wrong kind of QThread or the wrong usage of QThread

Thanks for effort. What I am trying to achieve it to open and keep port forwarding connection via SSH and on the top of that port forwarding establish a connection to SQL Server. If I run either port forwarding connection or SQL server conection on its own (one or another) everything works OK (when running SQL Server connection alone I am using PuTTY to establish SSH tunnel and forward ports). Therefore my impression is that port forwarding and SQL Server connection somehow intefere one with another. I do not know however how to fix it.

Regarding the QThread. It is quite likely the way I am using QThread is wrong but I am not aware of anything else that could run multiple concurrent processes within QT GUI without freezeing the app. Running port forwarding without QThread freezes the app, the same with SQL Server connection provided the query takes more than 1 - 2 seconds (in that case the freeze is not noticable).

[Denni]

Keep in mind that when running QThreads or even Python Threads you are dealing with the Python GIL (look it up if you are not familiar with it) but in a nutshell this simply means while you have Threads these threads are all run linearly such that only one operation (thread) is being executed at a time. So if you have 2 Sub-Threads and the Main Thread (QApplication) then if you launch anything within in any one of these 3 Threads that is a continuous process nothing else is going to be able to do anything until that continuous process finishes or you some how pass control back the the Event Handler Thread (QApplication)

So when designing Threads within Python you must keep this in mind, if your program seems to freeze this is because you have locked out (or blocked) the Event Handler from doing its job -- however all those events still stack up within the Event Queue such that once the Event Handler gets back control it will process every Event that was Queue up.

So in a secondary nutshell if you want your GUI (which must be in a Main Thread - QApplication) to be viable while you do things in other aspects of your program you need to create a Multiprocess and put the GUI in one process and everything else in the other process or create more than one Multiprocess and put everything within its own process

[jan_pips]

I think I understand the concept but the point is I am not full-breed python developer (I am rather self-taught person) so I am afraid I do not quite get how should I implement this solution. Therefore I would appreciate some more detailed hints.

[Denni]

Okay let us start from square one -- what is it you are trying to do and/or implement -- as much overview detail as you can provide so that I can get a solid picture of it -- then I can help advise you on possible paths to take -- then you can decide then we can move on from there

[jan_pips]

What I am trying to achieve is to connect my application - that can be used by more than just one person - to host where there is the instance of SQL Server. For security reasons I am using SSH to connect to host itself and within SSH connection I am performing port forwarding so the connection to SQL Server can be made via localhost nad local port. Then the connection to SQL Server is used to perform different SQL queries.

As I said before I can bypass the built-in mechanism to connect to host via SSH and establish port forwarding by using PuTTY (in that case I need only connection to SQL server built in my app). And such solution is workable. I wanted however to incorporate single point where user has to log in rather than start first provide password at start with Pageant, then either start PuTTY or execute Saved Session from Pageant and then to provide login and password to connect to database. The rsa keys that are used to connect to host via SSH can also be used to encrypt the SQL Server user and password so at the end of the day the application user has just to provide the name of the RSA private key and password.

As you can see from the code I have attached. The parts that allows to:
- connect to external host via SSH
- establish the port forwarding connection
- establish connection to SQL Server
are ready and they are working in separation. The problem is to run all of them within single app.

[Denni]

Okay first these Database aspects should all be contained within their own Class that is its own file. (aka MyDatabaseClass.py)

Then you just import that file into your Main Application and call the various APIs that you have put within your DbClass to autonomously perform any action/request that is needed.

For instance if the program does not need to be logged into but the database does then -- in the app you pop a window and get the database login credentials that you then pass to the DbClass' Login method where it then validates the user and then perhaps passes back an encrypted login session credential that the user's app can then use going forward for that specific session.

Then the app can perform all the rest of its functionality that is needed without needing to login again and again

Next your DbClass should just create a the secure connection to the Sequel Server Database behind the scenes using the credential it created each time it is making a request.

Doing it this way -- once you have it working allows you to then do things like move the DbClass into its own thread and/or into its own process -- depending on which is needed.

But you have to get the basic separation in place in order to and even more separated state -- basically the App (which I assume is the GUI) knows absolutely nothing about that Database beyond the API functions it needs to call -- it never ever creates a query it simply passes parameters to an API that in turn passes back a Dictionary or List result. Further your DbClass has no concept of what the data it is sending back is being used for nor does it care. Its job is solely to interface with the database engine of choice and get the information and send it back as a dictionary or list depending on its design


NOTE: If I state something you have already done great just keep in mind that I am writing this for you and anyone else that might be interested in this topic so I am covering all the elements as we progress -- so just skim over anything you have already done

[jan_pips]

Okay first these Database aspects should all be contained within their own Class that is its own file. (aka MyDatabaseClass.py)

Then you just import that file into your Main Application and call the various APIs that you have put within your DbClass to autonomously perform any action/request that is needed.

That is already done.

For instance if the program does not need to be logged into but the database does then -- in the app you pop a window and get the database login credentials that you then pass to the DbClass' Login method where it then validates the user and then perhaps passes back an encrypted login session credential that the user's app can then use going forward for that specific session.

Then the app can perform all the rest of its functionality that is needed without needing to login again and again

The Database instance from the imported Database class is created when the application start. Then if necessary the Database object is passed to child GUI window and within that window is given separate thread for executing the query. There is just one login but the login is initiated by user so the login window does not pop up when the app starts but when the user selects manu item. There is only one login.

Next your DbClass should just create a the secure connection to the Sequel Server Database behind the scenes using the credential it created each time it is making a request.

Doing it this way -- once you have it working allows you to then do things like move the DbClass into its own thread and/or into its own process -- depending on which is needed.

Maybe I will describe in a few words how it is does. When the database instance of a class is created it ends at

engine_create() 

(function from SQLAlchemy package). Then every time a request is needed there is a method within the GUI window which invokes the function that executes

engine.connect()

Of course those both fuctions are standard SQLAlchemy database object functions so they are executed as method of Database instance.

Then the proper execution of query is assigned to a QThread instance of class and the QThread instance is started within QThreadPool. So I believe that is what you refered to.

But you have to get the basic separation in place in order to and even more separated state -- basically the App (which I assume is the GUI) knows absolutely nothing about that Database beyond the API functions it needs to call -- it never ever creates a query it simply passes parameters to an API that in turn passes back a Dictionary or List result. Further your DbClass has no concept of what the data it is sending back is being used for nor does it care. Its job is solely to interface with the database engine of choice and get the information and send it back as a dictionary or list depending on its design

Yes that how it works. Basically the GUI elements deliver some parameters (strings, integers and so on) to Database instance. Those parameters are injected into SQL query (basicaly the are values that narrows the query - part of where element of query or arguments of SQL functions created on SQL server. The result of query is returned to dictionary and the dictionary is used to either create the output file or provide input that is displayed within GUI window.

NOTE: If I state something you have already done great just keep in mind that I am writing this for you and anyone else that might be interested in this topic so I am covering all the elements as we progress -- so just skim over anything you have already done

That's fine. I would even prefer that way as at each stage I am able to verify if my approach is correct or not.

[Denni]

Okay just to be sure because it sounds like you make A connection to the database and then perform queries via that connection as needed while maintaining that connection continuously -- if this is the case that is the wrong way to implement that for many reasons. That being said when you execute a query from within your Database Class you Connect -- Execute-Query -- Get-Results -- Disconnect

If you are doing that dynamite if not then you need to.

Okay is class dbhMSSQL your Database class in its entirety? And by that I mean anything and everything -- connecting to, executing queries against and anything else directly database oriented is contained within that class -- except of of course the calls made to that class from outside.