Python Forum
[PyQt] SSH port forwarding and connection to SQL Server in separate threads freezes
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[PyQt] SSH port forwarding and connection to SQL Server in separate threads freezes
#11
(Nov-07-2019, 09:04 PM)Denni Wrote: 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.
Reply
#12
Okay I tried to work with the above Class but you do not include the import statements you are using so there are a few objects that I cannot reference as I do not know what library they are coming from -- can you make this above Class complete so that I can conclude with the example I was trying to render for you.
Reply
#13
Below full code

import pymssql
import sqlalchemy as ORM

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
Reply
#14
Okay I am curious here -- I started digging into SQLAlchemy so that I knew what to expect with this library and I stumbled across information that outlines the following:
Quote:It should be stated that when using the SQLAlchemy ORM the Engine, Connection, and related objects are not generally accessed; for the Session object is used as the default and much preferred interface to the database.

However, for applications that are built around direct usage of textual SQL statements and/or SQL expression constructs without involvement of the ORM’s higher level management services, this is when the Engine and Connection become king and queen.
This leads me to ask is there a need to use the lower functionality of this library over its preferred default functionality? Or is this just an oversight due to unfamiliarity with SQLAlchemy? Note: makes no difference to me just mostly curious is all.
Reply
#15
(Nov-12-2019, 08:31 PM)Denni Wrote: This leads me to ask is there a need to use the lower functionality of this library over its preferred default functionality? Or is this just an oversight due to unfamiliarity with SQLAlchemy? Note: makes no difference to me just mostly curious is all.
Good question, glad you have asked. In principle it is on one hand my lack of deeper knowledge of SQLAlchemy tied with desire to prototype certain solutions quickly. The idea is that once basic things are working fine with the use of queries written is SQL then I will start to rewrite the code with use of high level SQLAlchemy objects and functions. So in nutshell I need right now a bit dirty solution working in order to be able - in my own pace limited by some other responsibilities - rework the code to do things as they should look like.
Reply
#16
(Nov-08-2019, 03:19 PM)Denni Wrote: Okay I tried to work with the above Class but you do not include the import statements you are using so there are a few objects that I cannot reference as I do not know what library they are coming from -- can you make this above Class complete so that I can conclude with the example I was trying to render for you.

Hi Denni. Any breakthrough regarding the issue?
Reply
#17
Oh yeah almost forgot (okay I forgot no almost to it thanks for the reminder) here is a MUC using sqlite3 with some of your stuff tossed in and either commented out or not called -- aka any function2 is would your version for your database from what you posted I hope this helps some
from PyQt5.QtCore    import pyqtSlot
from PyQt5.QtWidgets import QApplication, QStyleFactory, QWidget, QVBoxLayout, QLabel, QPushButton

#import pymssql
from sqlite3 import connect as sq3Connect
from sqlite3 import Row as sq3Row
#from sqlalchemy import create_engine as ormCreatEng
#from sqlalchemy import Table         as ormTable
#from sqlalchemy import select        as ormSelect

class dbhMSSQL(object):
    def __init__(self, Config):
        self.Dialect  = Config['Dialect']
        self.Driver   = Config['Driver']
        self.UserName = Config['UserName']
        self.Password = Config['Password']
        self.Host     = Config['Host']
        self.Port     = Config['Port']
        self.Database = Config['Database']
        
      # Internal Class Properties
        self.SQLDef = ''
        self.PipeOpen = False
        
      # The instantiation should establish the Connection Pipe as part
      # of creating the Class -- for your database
      # Broke this down into smaller related chunks to make it more readable
      #  self.Conctr = ''
      #  self.Conctr += '{0}+{1}://'.format(self.Dialect, self.Driver)
      #  self.Conctr += '{2}:{3}@'.format(self.UserName, self.Password)
      #  self.Conctr += '{4}:{5}/{6}'.format(self.Host, self.Port, self.Database)

      #  self.DbEngn = ormCreatEng(self.Conctr)

      # NOTE: The SQLite3 version does not seem to use this same Pipe structure 
      # but I could be wrong about what this is meant for the ideal usages is to
      # open a temporary connection, run your SQL, and then close that connection
      # but some databases support multiple hooks (aka Pipes) that are then 
      # activated, used and deactivated which is what this seems to be doing
        self.PipeOpen = self.CheckConnection()

    # What you should do if the Database fails to connect is generally not 
    # something within the business logic of working with the database and thus
    # should be handled by the calling program
    @property
    def IsDbConnected(self):
        return self.PipeOpen

# Regardless of what you are doing always use the same methods to do it for all 
# instances if at all possible and adjust for this as needed - this helps to 
# reduce redundancy within your code base as well as comparmentalize the various 
# aspects of the Class which you are rendering
    def CheckConnection(self):
        RetVal = False
        self.SQLDef = 'SELECT * FROM tblConnections'
        Rslts = self.dbSelect()
        if Rslts != None and len(Rslts) > 0:
            RetVal = True
        print('Check Results ',RetVal)  # Debug purposes only - remove eventually

        return RetVal

    def OpenConn(self):
        try:
            self.dbConn = sq3Connect(self.Database)
            # This uses the native structure that mostly resembles a Recordset/Dictionary
            self.dbConn.row_factory = sq3Row
            # This removes the unicode prefix from a string
            self.dbConn.text_factory = str
            self.dbCrsr = self.dbConn.cursor()
        except Exception as err:
            print("ERROR : OpenConn :",err)
            print("Database : [" + self.Database + "]")

    def OpenConn2(self):
        try:
            self.dbConn = self.DbEngn.connect()
        except Exception as err:
          # Handle the Error Properly and Informatively
            print("ERROR : OpenConn :",err)
            print("Database : [" + self.Database + "]")

    def CloseConn(self):
        self.dbConn.close()

# There are 2 basic types of Queries -- 
#    Selects and Executes (Insert, Update, and Delete)
    def dbSelect(self):
        try:
            self.OpenConn()
            self.dbCrsr.execute(self.SQLDef)
            return self.dbCrsr.fetchall()
        except Exception as err:
            print("ERROR : dbSelect :",err)
            print(self.SQLDef)
        finally:
            self.CloseConn()

    def dbSelect2(self):
        try:
            self.OpenConn()
            return self.DbEngn.execute(self.SQLDef).fetchall()
        except Exception as err:
            print("ERROR : dbSelect :",err)
            print(self.SQLDef)
        finally:
            self.CloseConn()

    def dbExecute(self):
        try:
            self.OpenConn()
            self.dbCrsr.execute(self.SQLDef)
            self.dbConn.commit()
        except Exception as err:
            print("ERROR : dbExecute :",err)
            print("Query : ",self.SQLDef)
        finally:
            self.CloseConn()

    def dbExecute2(self):
        try:
            self.OpenConn()
          # Not sure how your version of this would look
          # but I included the framework in order to be
          # more complete
        except Exception as err:
            print("ERROR : dbExecute :",err)
            print("Query : ",self.SQLDef)
        finally:
            self.CloseConn()
# ******************************************************************************
  # This would be a call to one of your pre-established Stored Procedures if 
  # your database handles them - or your built-in Stored Procedure as shown 
  # below -- these Pre-Defined Methods thus control how one interacts with the
  # database and can be built to be highly generic if need be but always keep in
  # mind that a to open interface to the database can be subject to injection
  # attacks and as such it is always good practice to make that as hard or 
  # impossible as can be obtained
    def GetMyData(self):
        self.SQLDef = 'SELECT * FROM tblConnections'
        return self.dbSelect()

class MyWindow(QWidget):
    def __init__(self):
        QWidget.__init__(self)
        self.setStyle(QStyleFactory.create("Cleanlooks"))
        
      # I assume all the following values are read in from config file or some such if 
      # not they ought to be and as such I have represented this by doing the following
        self.ConfigData = {}
#        self.ConfigData['Dialect']  = 'mssql'
#        self.ConfigData['Driver']   = 'pymssql'
        self.ConfigData['Dialect']  = 'sqlite'
        self.ConfigData['Driver']   = ''
        self.ConfigData['UserName'] = 'test'
        self.ConfigData['Password'] = 'testpassword'
        self.ConfigData['Host']     = 'host'
        self.ConfigData['Port']     = 'port'
        self.ConfigData['Database'] = './NewDatabase.sqlt3'
        
        self.dbhSQLite = dbhMSSQL(self.ConfigData)

        self.lblOne = QLabel('Position One')
        self.lblTwo = QLabel('Position Two')

        self.btnOpen = QPushButton('Open')
        self.btnOpen.clicked.connect(self.OpenAction)

        self.btnQury = QPushButton('Query')
        self.btnQury.clicked.connect(self.QueryAction)
    
        VBox = QVBoxLayout()
        VBox.addWidget(self.lblOne)
        VBox.addWidget(self.btnOpen)
        VBox.addWidget(self.btnQury)
        VBox.addWidget(self.lblTwo)

        self.setLayout(VBox)

    @pyqtSlot()
    def OpenAction(self):
        if self.dbhSQLite.IsDbConnected:
            print('Database Opened Successfully')
        else:
            print('Database Connection Failed')

    @pyqtSlot()
    def QueryAction(self):
        Results = self.dbhSQLite.GetMyData
        for Row in Results():
            for Item in Row:
                print('Result Set :',Item)

if __name__ == '__main__':
    MainThred = QApplication([])
    
    MainApp = MyWindow()
    MainApp.show()

    MainThred.exec()
Reply
#18
Denni. Thanks for your effort but I believe one thing is missing. Did you connect to SQLLite over SSH port forwarding because the clash between port forwarding over SSH and connection to database that causes GUI to freeze is mine biggest issue. I understand that - as you have pointed out - my code with respect to connection to database was not good enough, but from my perspective the key issue is whether putting process that establish port forwarding over SSH into one thread and process that establish databse connection into another thread will work flawlessly without clashing.
Reply
#19
Okay but I first wanted to take this one bite at time and make sure we are on the same page as we move forward -- if what I provided creates a solid usable connection to an sqlite3 database on your end -- and then you can use this class to connect to your database (since I cannot duplicate that specific aspect) -- then we will have that base stuff in place -- and then can move to the next piece

If their is any issue with this basic stuff then we need not move further -- if it it works fine -- then we move forward to that next piece where we start figuring out this next element that you put forth -- sure we could skip ahead but then we might not be on the same page which makes it harder for me to help you troubleshoot your code through you -- I say it that way because again I can not do it on my end but I have helped other programmers debug there stuff in this manner so it does work
Reply
#20
(Nov-25-2019, 09:02 PM)Denni Wrote: Okay but I first wanted to take this one bite at time and make sure we are on the same page as we move forward -- if what I provided creates a solid usable connection to an sqlite3 database on your end -- and then you can use this class to connect to your database (since I cannot duplicate that specific aspect) -- then we will have that base stuff in place -- and then can move to the next piece

If their is any issue with this basic stuff then we need not move further -- if it it works fine -- then we move forward to that next piece where we start figuring out this next element that you put forth -- sure we could skip ahead but then we might not be on the same page which makes it harder for me to help you troubleshoot your code through you -- I say it that way because again I can not do it on my end but I have helped other programmers debug there stuff in this manner so it does work

OK. Fine. I should be able to make necessary changes tomorrow (it is 11 pm right now) and will let you know.
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  GUI freezes while executing a callback funtion when a button is pressed abi17124 5 7,390 Jul-10-2019, 12:48 AM
Last Post: FullOfHelp
  [Tkinter] tkinter freezes by clicking button Zatox11 33 25,337 Apr-10-2018, 09:03 AM
Last Post: Zatox11
  Running telnet loop freezes GUI reedhallen 1 3,411 Jan-27-2018, 10:24 PM
Last Post: j.crater
  Clock freezes - wx.python glib problem strongheart 3 3,983 Oct-10-2017, 03:36 AM
Last Post: strongheart

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020