Python Desktop Application that will connect o Azure SQL Database

[thewolf]

Hey guys like I said I'm working on a very simple inventory management app for keeping up with hardware and software at work.

I have two py files in my project currently. mainWindow.py and dbConnection.py. mainWindow.py will of course hold my GUI and object coding. dbConnection.py will hold the database connection and sql statement functions. Do I need two files? No but I'm learning and wanted to practice importing and calling methods from my own py file.

Currently I've only gotten so far to where if you click the "Enter New Hardware Button" on the main window app it calls on the enterNewHardware function on the dbConnection.py file. I guess you would call it a method using it this way. Line 35.

So I'm going to have to pull user's input from the textboxes and pass that data to the enterNewHardware function/method. Sure I can figure this out on my own. I will have a few functions/methods on my dbConnection.py file with different SQL statements that my main window is going to call on. Please see code of dbConnection.py. My question is there a better way to centralize my db connection instead of typing the connection setup in every function? Guess turning into it's own function and then using it as a method for the other functions?

Edit. I would need to create the connection outside the functions and make sqlCMD which is conn.cursor() a global variable and then all the functions that will have SQL statements can access it? Maybe?

import dbConnection
import sys
from PyQt5.QtWidgets import (QLabel, QPushButton, QLineEdit, QApplication, QCheckBox, QMainWindow, QWidget,
                             QVBoxLayout, QTabWidget, QStatusBar)


class mainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.resize(385, 323)
        self.setWindowTitle("HARDWARE | SOFTWARE MANAGER")
        self.statusBar = QStatusBar()
        self.setStatusBar(self.statusBar)

        self.tabForm = QTabWidget()
        self.tabForm.addTab(hardwareTab(), "HARDWARE")
        self.tabForm.addTab(softwareTab(), "SOFTWARE")
        self.tabForm.addTab(reportingTab(), "REPORTING")
        self.setCentralWidget(self.tabForm)


class hardwareTab(QWidget):
    def __init__(self):
        super().__init__()
        self.snLabel = QLabel("SERIAL NUMBER")
        self.snTextBox = QLineEdit()
        self.modelLabel = QLabel("MODEL")
        self.modelTextBox = QLineEdit()
        self.userLabel = QLabel("USER")
        self.userTextBox = QLineEdit()
        self.enButton = QPushButton("ENTER NEW HARDWARE")
        self.cfButton = QPushButton("CLEAR FIELDS")
        self.seButton = QPushButton("SEARCH/EDIT HARDWARE")
        self.activeCheckbox = QCheckBox("ACTIVE")
        self.enButton.clicked.connect(dbConnection.enterNewHardware)

        layout = QVBoxLayout(self)
        layout.addWidget(self.snLabel)
        layout.addWidget(self.snTextBox)
        layout.addWidget(self.modelLabel)
        layout.addWidget(self.modelTextBox)
        layout.addWidget(self.userLabel)
        layout.addWidget(self.userTextBox)
        layout.addWidget(self.activeCheckbox)
        layout.addWidget(self.enButton)
        layout.addWidget(self.cfButton)
        layout.addWidget(self.seButton)


class softwareTab(QWidget):
    def __init__(self):
        super().__init__()
        self.snLabel = QLabel("SERIAL NUMBER / KEY")
        self.snTextbox = QLineEdit()
        self.nameLabel = QLabel("APPLICATION NAME")
        self.nameTextBox = QLineEdit()
        self.userLabel = QLabel("USER")
        self.userTextBox = QLineEdit()
        self.enButton = QPushButton("ENTER NEW SOFTWARE")
        self.cfButton = QPushButton("CLEAR FIELDS")
        self.seButton = QPushButton("SEARCH/EDIT SOFTWARE")

        layout = QVBoxLayout(self)
        layout.addWidget(self.snLabel)
        layout.addWidget(self.snTextbox)
        layout.addWidget(self.nameLabel)
        layout.addWidget(self.nameTextBox)
        layout.addWidget(self.userLabel)
        layout.addWidget(self.userTextBox)
        layout.addWidget(self.enButton)
        layout.addWidget(self.cfButton)
        layout.addWidget(self.seButton)


class reportingTab(QWidget):
    def __init__(self):
        super().__init__()


if __name__ == "__main__":
    APP = QApplication(sys.argv)
    WINDOW = mainWindow()
    WINDOW.show()
    sys.exit(APP.exec_())

import pyodbc


def enterNewHardware():
    azureServer = "pythonserver5874.database.windows.net"
    azureDB = "inventoryDatabase"
    userName = "lol"
    password = "lol"
    driver = "{ODBC Driver 17 for SQL Server}"

    connectionString = f"DRIVER={driver};SERVER={azureServer};PORT=1433;DATABASE={azureDB};UID={userName};PWD={password}"
    conn = pyodbc.connect(connectionString)
    sqlCMD = conn.cursor()

    sqlCMD.execute('SELECT * FROM inventoryDatabase.dbo.Hardware')
    sqlCMD.execute('''
                    INSERT INTO inventoryDatabase.dbo.Hardware(serialNumber, modelName, userName, machineActive)
                    VALUES
                    ('D69745', 'L7410', 'BBARKER', 'False')
                    ''')
    conn.commit()