-2

Progressing with Python pretty good so far. I need help with this code:

def btn_clk(self):
    sender = self.sender()
    if sender.text() == "Submit":
        entry = self.mod_num.text()
        conn = sqlite3.connect("uprightsbeams.db")
        c = conn.cursor()
        c.execute("SELECT * FROM uprightbeam WHERE catalogNo=?", [entry])
        results = c.fetchone()
        print(results)

There is more to this code but this is where my program breaks. Pretty much when the button is pressed I want this to perform a query against my uprightsbeams.db for a catalogNo so I can get the row values associated with it.

I had this the catalogNo is stored as a text value in sqlite3 so I would think it would be able to get it no problem, but I think this is breaking even before that. Do I need to have the connection to the db outside of the method? I have tried numerous different variations and I just can't figure this out.

Seeing as there is no error code when this happens here is the code of the program.:

import sys
import os
import sqlite3
from PyQt5.QtWidgets import QTableWidget, QMainWindow, QApplication, QLabel, QTableWidgetItem, QWidget, QTableView
from PyQt5.QtGui import QIcon, QPixmap
from PyQt5 import QtCore, QtWidgets, QtGui


class MyTable(QTableWidget):
    def __init__(self):
        super().__init__()
        self.setGeometry(48, 0, 1080, 451)
        self.setRowCount(34)
        self.setColumnCount(14)


class Sheet(QtWidgets.QWidget):
    def __init__(self):
        super().__init__()
        QMainWindow.setGeometry(self, 1, 30, 1918, 1050)
        self.form_widget = MyTable()
        self.row = 0
        self.col = 0
        col_headers = ['CATALOG NO.', 'DESCRIPTION', 'COLOR', 'QTY.', 'UN.', 'WGT.', 'Total Wgt.', 'List Price', 'Net', 'Total Net', 'Total List',"","Item Discount",""]
        self.form_widget.setHorizontalHeaderLabels(col_headers)
        self.form_widget.verticalHeader().setVisible(False)



        #h_box1 Layout
        self.cat_num_lab = QtWidgets.QLabel(self)
        self.cat_num_lab.setText("Part Number:")

        self.mod_num = QtWidgets.QLineEdit(self)

        self.color_label = QtWidgets.QLabel(self)
        self.color_label.setText("Color: ")

        self.color_Entry = QtWidgets.QLineEdit(self)

        self.qty_label = QtWidgets.QLabel(self)
        self.qty_label.setText("QTY:")

        self.qty_Entry = QtWidgets.QLineEdit(self)        

        self.sub_btn = QtWidgets.QPushButton("Submit")
        self.sub_btn.clicked.connect(self.btn_clk)


        #h_box2 Layout
        self.cat_man_Entry = QtWidgets.QLabel(self)
        self.cat_man_Entry.setText("Manual Entry")

        self.man_part_num = QtWidgets.QLabel(self)
        self.man_part_num.setText("Part Number:")

        self.man_part_Entry = QtWidgets.QLineEdit(self)

        self.man_disc = QtWidgets.QLabel(self)
        self.man_disc.setText("Description: ")

        self.man_disc_Entry = QtWidgets.QLineEdit(self)

        self.man_color_label = QtWidgets.QLabel(self)
        self.man_color_label.setText("Color: ")

        self.man_color_Entry = QtWidgets.QLineEdit(self)

        self.man_qty_label = QtWidgets.QLabel(self)
        self.man_qty_label.setText("QTY:")

        self.man_qty_Entry = QtWidgets.QLineEdit(self)

        self.man_unit_meas = QtWidgets.QLabel(self)
        self.man_unit_meas.setText("Unit Measure: ")

        self.man_unit_meas_Entry = QtWidgets.QLineEdit(self)

        self.man_wgt = QtWidgets.QLabel(self)
        self.man_wgt.setText("WGT.: ")

        self.man_wgt_Entry = QtWidgets.QLineEdit(self)

        self.man_list_price = QtWidgets.QLabel(self)
        self.man_list_price.setText("List Price: ")

        self.man_list_price_Entry = QtWidgets.QLineEdit(self)        

        self.man_sub_btn = QtWidgets.QPushButton("Man. Submit")
        self.man_sub_btn.clicked.connect(self.btn_clk)



        #Layout
        h_box1 = QtWidgets.QHBoxLayout()
        h_box1.addWidget(self.cat_num_lab)
        h_box1.addWidget(self.mod_num)        
        h_box1.addWidget(self.color_label)
        h_box1.addWidget(self.color_Entry)
        h_box1.addWidget(self.qty_label)
        h_box1.addWidget(self.qty_Entry)
        h_box1.addWidget(self.sub_btn)
        h_box1.addStretch()

        h_box2 = QtWidgets.QHBoxLayout()
        h_box2.addWidget(self.cat_man_Entry)
        h_box2.addWidget(self.man_part_num)
        h_box2.addWidget(self.man_part_Entry)
        h_box2.addWidget(self.man_disc)
        h_box2.addWidget(self.man_disc_Entry)
        h_box2.addWidget(self.man_color_label)
        h_box2.addWidget(self.man_color_Entry)
        h_box2.addWidget(self.man_qty_label)
        h_box2.addWidget(self.man_qty_Entry)
        h_box2.addWidget(self.man_unit_meas)
        h_box2.addWidget(self.man_unit_meas_Entry)
        h_box2.addWidget(self.man_wgt)
        h_box2.addWidget(self.man_wgt_Entry)
        h_box2.addWidget(self.man_list_price)
        h_box2.addWidget(self.man_list_price_Entry)
        h_box2.addWidget(self.man_sub_btn)
        h_box2.addStretch()

        h_box3 = QtWidgets.QHBoxLayout()
        h_box3.addWidget(self.form_widget)


        v_box = QtWidgets.QVBoxLayout()
        v_box.addLayout(h_box1)
        v_box.addLayout(h_box2)
        v_box.addLayout(h_box3)

        self.setLayout(v_box)



        self.show()



    def btn_clk(self):
        sender = self.sender()
        if sender.text() == "Submit":
            entry = QTableWidgetItem(self.mod_num.text())
            conn = sqlite3.connect("uprightsbeams.db")
            c = conn.cursor()
            c.execute("Select * FROM uprightbeam WHERE catalogNo=?", [entry])
            results = c.fetchone()
            print(results)
            col = 0
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, entry)
            col = 2
            color = QTableWidgetItem(self.color_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, color)
            col = 3
            qty = QTableWidgetItem(self.qty_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, qty)
            self.row += 1
            self.mod_num.clear()
            self.color_Entry.clear()
            self.qty_Entry.clear()
        else:
            col = 0
            cat_num = QTableWidgetItem(self.man_part_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, cat_num)
            col = 1
            man_disc = QTableWidgetItem(self.man_disc_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, man_disc)
            col = 2
            man_color = QTableWidgetItem(self.man_color_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, man_color)
            col = 3
            man_qty = QTableWidgetItem(self.man_qty_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, man_qty)
            col = 4
            man_unit = QTableWidgetItem(self.man_unit_meas_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, man_unit)
            col = 5
            man_wgt = QTableWidgetItem(self.man_wgt_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, man_wgt)
            col = 7
            man_price = QTableWidgetItem(self.man_list_price_Entry.text())
            self.form_widget.setCurrentCell(self.row, col)
            self.form_widget.setItem(self.row, col, man_price)
            self.row += 1
            self.man_part_Entry.clear()
            self.man_disc_Entry.clear()
            self.man_wgt_Entry.clear()
            self.man_list_price_Entry.clear()

app = QApplication(sys.argv)
sheet = Sheet()
sys.exit(app.exec_())

Update

I have never needed to run a code from console so I never did, always ran through the IDLE. I ran the application through the console and this is the traceback:

Traceback (most recent call last):
  File "E:\srold.py", line 147, in btn_clk
    c.execute("Select * FROM uprightbeam WHERE catalogNo=?", entry)
ValueError: parameters are of unsupported type

UPDATE I have moved on from the ValueError as I was trying to pass a QTableWidgetItem on as a value for entry. I have changed that to just the QLineEdit text from self.mod_num.text().

Now I have this, which I'm not understanding at the moment:

Traceback (most recent call last):
  File "E:\srold.py", line 147, in btn_clk
    c.execute("SELECT * FROM uprightbeam WHERE catalogNo=?", entry)
sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 12 supplied.

I'm not understanding because in my code I have:

c.execute("SELECT * FROM uprightbeam WHERE catalogNo=?", [entry])

I set entry as a list, which I thought would have prevented the Incorrect number of bindings issue.

John Ruehs
  • 39
  • 8
  • Thank you for keeping the error message secret. – CL. Feb 25 '17 at 06:35
  • if there was an error code I would provided it. What happens is I click the button and then the application crashes with no error code in the shell. – John Ruehs Feb 25 '17 at 12:20
  • See [PyQt: No error msg (traceback) on exit](http://stackoverflow.com/questions/33736819/pyqt-no-error-msg-traceback-on-exit). – CL. Feb 25 '17 at 12:29
  • What do you mean by "no error code"? There is a Python traceback, which will clearly show what the (rather obvious) problem is. – ekhumoro Feb 25 '17 at 18:42
  • ekhumoro, thanks for belittling a beginning coder. if it is so obvious could you please let me in on the answer. I have spent a day and a half looking for this answer. – John Ruehs Feb 25 '17 at 19:58
  • @JohnRuehs. It is in no way "belittling" to point out that the traceback shows you exactly what the problem is. Are you saying that you don't know how to run a python script in a [console](https://en.wikipedia.org/wiki/Command-line_interface)? – ekhumoro Feb 25 '17 at 20:28
  • @ekhumoro I'm sorry, I'm getting frustrated in this. I added the traceback from running in the console. As far as that goes, I never had to run a program from the console before so I wasn't aware of the benefits of doing so. – John Ruehs Feb 25 '17 at 20:54
  • @JohnRuehs. If you're using IDLE, you can put a `try/except` block around the code that's failing and then print the exception. Anyway, the problem is the second argument you are passing to `execute`. Put `print(repr(entry))` before the line that's failing to see why the error message says it's an unsupported type. – ekhumoro Feb 25 '17 at 21:10
  • @ekhumoro I have moved on from that error now, and now I think onto a more manageable one. Now I am onto a bindings number mismatch issue. The issue I was having in the original problem was trying to pass the QTableWidgetItem off as a str instead of just taking the text right from self.mod_num.text() – John Ruehs Feb 25 '17 at 21:15
  • @JohnRuehs. None of the code examples you've shown will produce that traceback. Please follow the advice from the last sentence of my previous comment, since you still seem to be passing in an invalid type of some kind. It should be either a string, or a sequence of strings which match the number of parameters in the select statement. – ekhumoro Feb 25 '17 at 21:55

1 Answers1

1

I have since got this working.

To answer the original question was the fact I was trying to pass the a QTableWidgetItem's value to a variable, when I could just use the QLineEdit's value right from the box. I also had some syntax errors along the way, but the finished top part of my btn_clk code follows:

sender = self.sender()
    if sender.text() == "Submit":
        entry = self.mod_num.text()
        conn = sqlite3.connect("uprightsbeams.db")
        c = conn.cursor()
        c.execute("SELECT * FROM beamupright WHERE catalogNo=?", [entry])
        results = c.fetchone()
        print(results)

I am now able to move on with this project.

John Ruehs
  • 39
  • 8