Nov-25-2019, 07:34 PM
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()