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

Question

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_())

Did you check the exception by wrapping the sql stuffs inside a try-except in enterNewHardware function? — krishg
– krishg, Commented Mar 8, 2021 at 11:40

Stanley Gong · Accepted Answer · 2021-03-08 03:45:51Z

1

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.

edited Mar 8, 2021 at 3:45

answered Mar 8, 2021 at 3:39

Stanley Gong

12.2k1 gold badge12 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Edward · Accepted Answer · 2021-03-08 23:28:08Z

My IP has been added and my firewall is setup correctly. Reason I know this is because I took the enterNewHardware function code and put it in it's own py file, removed the textbox variables out of the data variable and just added literal strings. This works and inputs the data. Something between my textbox variables and the database. It's not liking something. Also I created a new Hardware table called "HardwareThree" just for testing so that why it's renamed here.

import pyodbc

azureServer = "pythonserver5874.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.HardwareThree (serialNumber, 
modelName, userName, machineActive)
                VALUES (?, ?, ?, ?)'''
data = ('Test', 'Test', 'Test', 'Test')

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related