What is wrong with this SQL statement in my Python code?

I’m working on a simple inventory app that will manage hardware and software inventory. Right now I’m just trying to simply enter in the data a user inputs into the text boxes into my database. The program runs but when I enter text and click the button to enter the data the cursor spins for second and the app closes out. Any ideas? I have tried multiple formats for the sql statement. I did at one point get it where it was entering blank/null rows. The print functions are there just to make sure I was retrieving the data from the text boxes.

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


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.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.testTextbox = QLineEdit()

        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)
        layout.addWidget(self.testTextbox)

        self.enButton.clicked.connect(lambda: enterNewHardware(self))


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)


def enterNewHardware(textboxes):

    serial_number = textboxes.snTextBox.text()
    model_name = textboxes.modelTextBox.text()
    user_name = textboxes.userTextBox.text()
    test_textbox = textboxes.testTextbox.text()

    print(serial_number)
    print(model_name)
    print(user_name)
    print(test_textbox)

    azureServer = "pythonserver6974.database.windows.net"
    azureDB = "inventoryDatabase"
    userName = "na"
    password = "na"
    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)
    cursor = conn.cursor()

    sql_statement = 'INSERT INTO inventoryDatabase.dbo.Hardware (serialNumber, modelName, userName, machineActive) VALUES (?, ?, ?, ?)'
    data = (serial_number, model_name, user_name, test_textbox)

    cursor.execute(sql_statement, data)
    conn.commit()


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

Answer

Your code works perfectly for me, I created a new DB named “inventoryDatabase” with a table named “Hardware” just as below:

I tried your code on my side but everything works for me perfectly:

Based on all the info you provided, it all works, so could you pls check your table design and have you added your local public IP to your Azure SQL firewall rules(see here to add your local public rules to your Azure SQL firewall)?

Sometimes, your public IP address detected by Azure Portal is not so accurate, you can double-check your IP here.

If these 2 points can’t solve your issue, please provide me with some detailed exception information.

Advertisement

Answer