thanks Deanhystad. I actually rearranged some of the code and got it working. In case anyone is interested in the code see below. The only thing I have left to do is figure out the best way for a user to edit the database data when they search for a piece of hardware. If it was me I would want to be able to double-click a row which would pop open a window with the selected row information, edit it in the separate window and then click an enter button or the enter key and it will update the table display and database. Not sure what I'm going to do with Export button yet. Thought about having it export current table information to an Excel file to be printed. Going to go over my options and see what I can do. Also my credentials will not be hard coded in the final production version.
MainWindow.py
MainWindow.py
from PyQt5 import QtCore, QtGui, QtWidgets
from NewHardwareWindow import Ui_NewHardwareWindow
import sys
import pyodbc
class Ui_MainWindow(object):
def SetupUi(self, MainWindow):
super().__init__()
MainWindow.setObjectName("MainWindow")
MainWindow.setWindowTitle("Hardware Inventory")
MainWindow.setWindowModality(QtCore.Qt.NonModal)
MainWindow.resize(900, 350)
MainWindow.setMaximumSize(QtCore.QSize(900, 350))
MainWindow.setMinimumSize(QtCore.QSize(900, 350))
MainWindow.setCursor(QtGui.QCursor(QtCore.Qt.PointingHandCursor))
MainWindow.setTabletTracking(False)
MainWindow.setAutoFillBackground(False)
MainWindow.setStyleSheet('')
MainWindow.setToolButtonStyle(QtCore.Qt.ToolButtonFollowStyle)
MainWindow.setTabShape(QtWidgets.QTabWidget.Rounded)
font = QtGui.QFont()
font.setFamily("MS UI Gothic")
font.setPointSize(8)
self.CentralWidget = QtWidgets.QWidget(MainWindow)
self.CentralWidget.setObjectName("CentralWidget")
self.TableWidget = QtWidgets.QTableWidget(self.CentralWidget)
self.TableWidget.setGeometry(QtCore.QRect(120, 10, 770, 340))
self.TableWidget.setSortingEnabled(True)
self.TableWidget.setRowCount(0)
self.TableWidget.setColumnCount(5)
self.TableWidget.setObjectName("TableWidget")
item = QtWidgets.QTableWidgetItem()
self.TableWidget.setHorizontalHeaderItem(0, item)
item = QtWidgets.QTableWidgetItem()
self.TableWidget.setHorizontalHeaderItem(1, item)
item = QtWidgets.QTableWidgetItem()
self.TableWidget.setHorizontalHeaderItem(2, item)
item = QtWidgets.QTableWidgetItem()
self.TableWidget.setHorizontalHeaderItem(3, item)
item = QtWidgets.QTableWidgetItem()
self.TableWidget.setHorizontalHeaderItem(4, item)
item = self.TableWidget.horizontalHeaderItem(0)
item.setText("Creation Date")
item = self.TableWidget.horizontalHeaderItem(1)
item.setText("Last Modified")
item = self.TableWidget.horizontalHeaderItem(2)
item.setText("Serial Number")
item = self.TableWidget.horizontalHeaderItem(3)
item.setText("Model")
item = self.TableWidget.horizontalHeaderItem(4)
item.setText("User")
self.textbox_search = QtWidgets.QLineEdit(self.CentralWidget)
self.textbox_search.setGeometry(QtCore.QRect(10, 10, 101, 15))
self.textbox_search.setObjectName("textbox_model")
self.textbox_search.setText('')
self.textbox_search.setFocus()
self.textbox_search.returnPressed.connect(self.SearchHardware)
self.button_search = QtWidgets.QPushButton(self.CentralWidget)
self.button_search.setText("SEARCH")
self.button_search.setGeometry(QtCore.QRect(10, 30, 101, 18))
self.button_search.setFont(font)
self.button_search.setObjectName("button_search")
self.button_search.clicked.connect(self.SearchHardware)
self.button_search.setAutoDefault(True)
self.button_new_hardware = QtWidgets.QPushButton(self.CentralWidget)
self.button_new_hardware.setText("NEW HARDWARE")
self.button_new_hardware.setGeometry(QtCore.QRect(10, 60, 101, 31))
self.button_new_hardware.setFont(font)
self.button_new_hardware.setObjectName("button_new_hardware")
self.button_new_hardware.setAutoDefault(True)
self.button_new_hardware.clicked.connect(self.EnterNewHardwareWindow)
self.button_viewall = QtWidgets.QPushButton(self.CentralWidget)
self.button_viewall.setGeometry(QtCore.QRect(10, 100, 101, 31))
self.button_viewall.setFont(font)
self.button_viewall.setObjectName("button_viewall")
self.button_viewall.setText('VIEW ENTIRE DB')
self.button_viewall.setAutoDefault(True)
self.button_viewall.clicked.connect(self.ViewAll)
self.button_export = QtWidgets.QPushButton(self.CentralWidget)
self.button_export.setGeometry(QtCore.QRect(10, 140, 101, 31))
self.button_export.setFont(font)
self.button_export.setObjectName("button_export")
self.button_export.setText('EXPORT')
MainWindow.setCentralWidget(self.CentralWidget)
self.statusbar = QtWidgets.QStatusBar(MainWindow)
self.statusbar.setObjectName("statusbar")
MainWindow.setStatusBar(self.statusbar)
def EnterNewHardwareWindow(self):
self.window = QtWidgets.QMainWindow()
self.ui = Ui_NewHardwareWindow()
self.ui.setupUi(self.window)
self.window.show()
def SearchHardware(self):
user_search = self.textbox_search.text()
azure_server = 'pythonserver5874.database.windows.net'
azure_db = 'inventoryDatabase'
azure_username = ''
password = ''
driver = '{ODBC Driver 17 for SQL Server}'
connection_string = f"DRIVER={driver};SERVER={azure_server};PORT=1433;DATABASE={azure_db};UID={azure_username};PWD={password}"
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()
sql_statement = '''SELECT CreationDate, ModifyDate, SerialNumber, Model, Username FROM inventoryDatabase.dbo.Hardware WHERE SerialNumber = (?) OR Model = (?) OR Username = (?);'''
result = cursor.execute(sql_statement, user_search, user_search, user_search)
self.TableWidget.setRowCount(0)
for row_number, row_data in enumerate(result):
self.TableWidget.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.TableWidget.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
def ViewAll(self):
azure_server = 'pythonserver5874.database.windows.net'
azure_db = 'inventoryDatabase'
azure_username = ''
password = ''
driver = '{ODBC Driver 17 for SQL Server}'
connection_string = f"DRIVER={driver};SERVER={azure_server};PORT=1433;DATABASE={azure_db};UID={azure_username};PWD={password}"
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()
sql_statement = '''SELECT CreationDate, ModifyDate, SerialNumber, Model, Username FROM inventoryDatabase.dbo.Hardware'''
result = cursor.execute(sql_statement)
self.TableWidget.setRowCount(0)
for row_number, row_data in enumerate(result):
self.TableWidget.insertRow(row_number)
for column_number, data in enumerate(row_data):
self.TableWidget.setItem(row_number, column_number, QtWidgets.QTableWidgetItem(str(data)))
if __name__ == "__main__":
app = QtWidgets.QApplication(sys.argv)
window = QtWidgets.QMainWindow()
ui = Ui_MainWindow()
ui.SetupUi(window)
window.show()
sys.exit(app.exec_())NewHardwareWindow.pyfrom PyQt5 import QtCore, QtGui, QtWidgets
import pyodbc
import datetime
class Ui_NewHardwareWindow(object):
def setupUi(self, NewHardwareWindow):
super().__init__()
NewHardwareWindow.setObjectName("NewHardwareWindow")
NewHardwareWindow.resize(265, 167)
NewHardwareWindow.setWindowTitle('NEW HARDWARE')
NewHardwareWindow.setMinimumSize(265, 167)
NewHardwareWindow.setMaximumSize(265, 167)
font_label = QtGui.QFont()
font_label.setFamily('MS UI Gothic')
font_label.setPointSize(10)
font_button = QtGui.QFont()
font_button.setFamily('MS UI Gothic')
font_button.setPointSize(9)
self.centralwidget = QtWidgets.QWidget(NewHardwareWindow)
self.centralwidget.setObjectName("centralwidget")
self.label_serialnumber = QtWidgets.QLabel(self.centralwidget)
self.label_serialnumber.setGeometry(QtCore.QRect(10, 20, 101, 20))
self.label_serialnumber.setText("SERIAL NUMBER")
self.label_serialnumber.setFont(font_label)
self.label_serialnumber.setObjectName("label_serialnumber")
self.label_model = QtWidgets.QLabel(self.centralwidget)
self.label_model.setGeometry(QtCore.QRect(10, 50, 91, 16))
self.label_model.setText('MODEL')
self.label_model.setFont(font_label)
self.label_model.setObjectName("label_model")
self.label_user = QtWidgets.QLabel(self.centralwidget)
self.label_user.setGeometry(QtCore.QRect(10, 80, 81, 16))
self.label_user.setText('USER')
self.label_user.setFont(font_label)
self.label_user.setObjectName("label_user")
self.textbox_serialnumber = QtWidgets.QLineEdit(self.centralwidget)
self.textbox_serialnumber.setGeometry(QtCore.QRect(110, 20, 121, 16))
self.textbox_serialnumber.setObjectName("textbox_serialnumber")
self.textbox_serialnumber.returnPressed.connect(self.EnterNewHardware)
self.textbox_model = QtWidgets.QLineEdit(self.centralwidget)
self.textbox_model.setGeometry(QtCore.QRect(110, 50, 121, 16))
self.textbox_model.setObjectName("textbox_model")
self.textbox_model.returnPressed.connect(self.EnterNewHardware)
self.textbox_username = QtWidgets.QLineEdit(self.centralwidget)
self.textbox_username.setGeometry(QtCore.QRect(110, 80, 121, 16))
self.textbox_username.setObjectName("textbox_username")
self.textbox_username.returnPressed.connect(self.EnterNewHardware)
self.button_clear = QtWidgets.QPushButton(self.centralwidget)
self.button_clear.setGeometry(QtCore.QRect(110, 110, 61, 31))
self.button_clear.setText('CLEAR')
self.button_clear.setFont(font_button)
self.button_clear.setObjectName("button_clear")
self.button_clear.clicked.connect(self.ClearTextBoxes)
self.button_enter = QtWidgets.QPushButton(self.centralwidget)
self.button_enter.setGeometry(QtCore.QRect(170, 110, 61, 31))
self.button_enter.setText('ENTER')
self.button_enter.setFont(font_button)
self.button_enter.setObjectName("button_enter")
self.button_enter.clicked.connect(self.EnterNewHardware)
self.button_enter.setAutoDefault(True)
self.statusbar = QtWidgets.QStatusBar(NewHardwareWindow)
self.statusbar.setObjectName("statusbar")
NewHardwareWindow.setCentralWidget(self.centralwidget)
NewHardwareWindow.setStatusBar(self.statusbar)
def EnterNewHardware(self):
serial_number = self.textbox_serialnumber.text()
model = self.textbox_model.text()
user_name = self.textbox_username.text()
creation_date = datetime.datetime.now().strftime('%m/%d/%y')
modify_date = datetime.datetime.now().strftime('%m/%d/%y')
print(creation_date)
print(modify_date)
print(serial_number)
print(model)
print(user_name)
azure_server = 'pythonserver5874.database.windows.net'
azure_db = 'inventoryDatabase'
azure_username = ''
password = ''
driver = '{ODBC Driver 17 for SQL Server}'
connection_string = f"DRIVER={driver};SERVER={azure_server};PORT=1433;DATABASE={azure_db};UID={azure_username};PWD={password}"
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()
sql_statement = 'INSERT INTO inventoryDatabase.dbo.Hardware(CreationDate, ModifyDate, SerialNumber, Model, Username) ' \
'VALUES (?, ?, ?, ?, ?)'
sql_data = (creation_date, modify_date, serial_number, model, user_name)
if self.textbox_serialnumber.text() == "":
self.statusbar.showMessage("Enter serial number")
elif self.textbox_model.text() == "":
self.statusbar.showMessage("Enter model")
elif self.textbox_username.text() == "":
self.statusbar.showMessage("Enter username")
else:
cursor.execute(sql_statement, sql_data)
conn.commit()
cursor.commit()
self.textbox_serialnumber.clear()
self.textbox_model.clear()
self.textbox_username.clear()
self.statusbar.showMessage(serial_number + " has been entered")
self.textbox_serialnumber.setFocus()
def ClearTextBoxes(self):
self.textbox_serialnumber.clear()
self.textbox_model.clear()
self.textbox_username.clear()