Nov-01-2019, 05:44 PM
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.
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
https://www.learnpyqt.com/courses/concur...hreadpool/
After some trials I have found out that the problem lies within the command
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 FalseThreading 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() # DoneApp 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.