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

[jan_pips]

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.

Yes. Everything that is connected to connecting and quering database is related to class dbhMSSQL. The database class dbhMSSQL looks the following:

class dbhMSSQL(object):
    def __init__(self, config):
        self.dialect = "mssql"
        self.driver = "pymssql"
        self.username = "parameter provided from GUI"
        self.password = "parameter provided from GUI"
        self.host = "host"
        self.port = "port"
        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):
        try:
            dbc_main = self.engine.connect()
            result = self.engine.execute(
                "SELECT * FROM sys.dm_exec_connections"
            ).fetchall()
            dbc_main.close()
            return True
        except Exception as ex:
            return False

This additional method connection_check is mainly used to verify if the engine is created when the user enters login window so he does not have to provide login credentials again.

As you can see from method connection_check before executing the query a connection object is created dbc_main and then after the query is finished that object is closed so as far as I understand SQLAlchemy connection to database is closed but engine object remains intact. Hence when other function needs to retrive anything from database another connection object is created, performs the task and is closed.