How do I display the records fetched from my database to my listwidget?

[Abhishek_98]

I am trying to display the records from my database and display them on my listwidget. The records get fetched (I've tried fetching on cmd and it works), but don't display on my listwidget. How do I fix this?

My code:

def details(self):
        import sqlite3
        db=sqlite3.connect("cricket.db")
        cur=db.cursor()
        sql="SELECT player from statistics WHERE ctg='BAT';"
        sql1="SELECT player from statistics WHERE ctg='BWL';"
        sql2="SELECT player from statistics WHERE ctg='AR';"
        sql3="SELECT player from statistics WHERE ctg='WK';"
        cur=db.cursor()
        if(self.radioButton.isChecked()==True):
            cur.execute(sql)
            rec=cur.fetchall()
            print(rec)
            for item in rec:
                self.listWidget.addItem(item.text())
           # self.lineEdit.setText(len(rec))
        elif(self.radioButton_2.isChecked()==True):
            cur.execute(sql1)
            rec=cur.fetchall()
            print(rec)
            for item in rec:
                self.listWidget.addItem(item.text())
           # self.lineEdit_2.setText(len(rec))
        elif(self.radioButton_3.isChecked()==True):
            cur.execute(sql2)
            rec=cur.fetchall()
            print(rec)
            for item in rec:
                self.listWidget.addItem(item.text())
           # self.lineEdit_3.setText(len(rec))
        elif(self.radioButton_4.isChecked()==True):
            cur.execute(sql3)
            rec=cur.fetchall()
            print(rec)
            for item in rec:
                self.listWidget.addItem(item.text())

[Kent-Dorfman]

being that this is python (indentation specific scope) please repost your code using the "</>" code block to preserve the indentation and make it readable.

[SGaist]

Hi and welcome to devnet,

What about using a QSqlQueryModel ?

[Abhishek_98]

@Kent-Dorfman Done it, please take a look

[JonB]

@Abhishek_98
Before you go any further, as @SGaist has suggested you should first decide whether you wish to use Qt's SQL classes rather than sticking with your Python ones. Qt's model classes (e.g. QSqlQueryModel) play directly with Qt's view classes (QTableView, QListView). Do you have a reason for desiring the Python ones, which will require more work to interoperate with the UI?

[Abhishek_98]

@SGaist I prefer python over Qt's SQL classes, as I am currently undergoing my python training classes and moreover I am much more comfortable with python rather than Qt's exclusive SQL language.

[jsulm]

@Abhishek_98 said in How do I display the records fetched from my database to my listwidget?:

Qt's exclusive SQL language

there is no such thing. Qt simply provides APIs to access SQL databases, but SQL syntax is same and depends on used SQL database, not Qt.

[Abhishek_98]

can anyone please just tell my why the items aren't being displayed in the listwidget? Is there something wrong with the code?

[jsulm]

@Abhishek_98 Did you check the length of rec?
"The records get fetched (I've tried fetching on cmd and it works)" - it may work in cmd but not in your app.
Best would be to debug to see what happens.

[JonB]

@Abhishek_98
Do you not get items added to the list widget, or do you get blank items? What is item.text() each time?

rec=cur.fetchall()

Does that not return a list of rows?

 for item in rec:

Then item would be a row, not a column value in a row. Even if it's a result set with just one row of one column. If item is a row, what is item.text()?

Python's print(rec) can print what it likes, probably str(rec).

[Kent-Dorfman]

two comments:

1. why do you do cur=db.cursor() twice?

2. your problem may be related to the format that the data is returned in rec (assuming rec actually has something in it). What does the print(rec) actually print out?

3)skip what I wrote above...as JonB ppointed out... cur.fetchall() will return a LIST of records, and within each record will be a LIST of fields. You are not parsing your SQL response properly.

[Tejaswini_14]

I have the same problem...Please help if you have the solution

[SGaist]

@Tejaswini_14 hi and welcome to devnet,

Did you follow what people already suggested/asked to do on this thread ?

[Tejaswini_14]

Actually I am at the beginning level in Python and I have made some changes to my code but whenever I click on QRadioButton it shows "Python has stopped working".

[JonB]

@Tejaswini_14
Sorry, but nobody is going to be able to help you from that description of a problem.

[Tejaswini_14]

Here is my code

from PyQt5 import QtCore, QtGui, QtWidgets
import sqlite3
db=sqlite3.connect("fantcktdb.db")
cur=db.cursor()

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName("MainWindow")
        MainWindow.resize(800, 600)
        self.centralwidget = QtWidgets.QWidget(MainWindow)
        self.centralwidget.setObjectName("centralwidget")
        self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)
        self.gridLayout.setObjectName("gridLayout")
        self.frame = QtWidgets.QFrame(self.centralwidget)
        self.frame.setFrameShape(QtWidgets.QFrame.StyledPanel)
        self.frame.setFrameShadow(QtWidgets.QFrame.Raised)
        self.frame.setObjectName("frame")
        self.gridLayout_2 = QtWidgets.QGridLayout(self.frame)
        self.gridLayout_2.setObjectName("gridLayout_2")
        self.verticalLayout = QtWidgets.QVBoxLayout()
        self.verticalLayout.setObjectName("verticalLayout")
        self.wkRadio = QtWidgets.QRadioButton(self.frame)
        self.wkRadio.setObjectName("wkRadio")
        self.buttonGroup = QtWidgets.QButtonGroup(MainWindow)
        self.buttonGroup.setObjectName("buttonGroup")
        self.buttonGroup.addButton(self.wkRadio)
        self.verticalLayout.addWidget(self.wkRadio)
        self.arRadio = QtWidgets.QRadioButton(self.frame)
        self.arRadio.setObjectName("arRadio")
        self.buttonGroup.addButton(self.arRadio)
        self.verticalLayout.addWidget(self.arRadio)
        self.bowRadio = QtWidgets.QRadioButton(self.frame)
        self.bowRadio.setObjectName("bowRadio")
        self.buttonGroup.addButton(self.bowRadio)
        self.verticalLayout.addWidget(self.bowRadio)
        self.batRadio = QtWidgets.QRadioButton(self.frame)
        self.batRadio.setObjectName("batRadio")
        self.buttonGroup.addButton(self.batRadio)
        self.verticalLayout.addWidget(self.batRadio)
        self.buttonGroup.buttonToggled.connect(self.showList)
        self.listWidget = QtWidgets.QListWidget(self.frame)
        self.listWidget.setObjectName("listWidget")
        self.verticalLayout.addWidget(self.listWidget)
        self.gridLayout_2.addLayout(self.verticalLayout, 0, 0, 1, 1)
        self.gridLayout.addWidget(self.frame, 0, 0, 1, 1)
        MainWindow.setCentralWidget(self.centralwidget)
        self.menubar = QtWidgets.QMenuBar(MainWindow)
        self.menubar.setGeometry(QtCore.QRect(0, 0, 800, 26))
        self.menubar.setObjectName("menubar")
        MainWindow.setMenuBar(self.menubar)
        self.statusbar = QtWidgets.QStatusBar(MainWindow)
        self.statusbar.setObjectName("statusbar")
        MainWindow.setStatusBar(self.statusbar)

        self.retranslateUi(MainWindow)
        QtCore.QMetaObject.connectSlotsByName(MainWindow)

    def showList(self,MainWindow):
        if (self.batRadio.isChecked()==True):
            cur.execute('''SELECT Player FROM stats WHERE Ctg='BAT';''')
            records=cur.fetchall()
            self.listWidget.clear()
            for row in records:
                self.listWidget.addItems(row)
            self.listWidget.update()
        elif (self.bowRadio.isChecked()==True):
            cur.execute('''SELECT Player FROM stats WHERE Ctg='BOW';''')
            records=cur.fetchall()
            self.listWidget.clear()
            for row in records:
                self.listWidget.addItems(row)
            self.listWidget.update()
        elif (self.arRadio.isChecked()==True):
            cur.execute('''SELECT Player FROM stats WHERE Ctg='AR';''')
            records=cur.fetchall()
            self.listWidget.clear()
            for row in records:
                self.listWidget.addItems(row)
            self.listWidget.update()
        elif (self.wkRadio.isChecked()==True):
            cur.execute('''SELECT Player FROM stats WHERE Ctg='WK';''')
            records=cur.fetchall()
            self.listWidget.clear()
            for row in records:
                self.listWidget.addItems(row)
            self.listWidget.update()
    def retranslateUi(self, MainWindow):
        _translate = QtCore.QCoreApplication.translate
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow"))
        self.wkRadio.setText(_translate("MainWindow", "WK"))
        self.arRadio.setText(_translate("MainWindow", "AR"))
        self.bowRadio.setText(_translate("MainWindow", "BOW"))
        self.batRadio.setText(_translate("MainWindow", "BAT"))


if __name__ == "__main__":
    import sys
    app = QtWidgets.QApplication(sys.argv)
    MainWindow = QtWidgets.QMainWindow()
    ui = Ui_MainWindow()
    ui.setupUi(MainWindow)
    MainWindow.show()
    sys.exit(app.exec_())

[JonB]

@Tejaswini_14
I think you need to read carefully through the posts above. If I say so myself, you/the OP did not respond to my own reply, which I think would indicate you cannot just go for row in records::

@Abhishek_98
Do you not get items added to the list widget, or do you get blank items? What is item.text() each time?

rec=cur.fetchall()

Does that not return a list of rows?

for item in rec:

Then item would be a row, not a column value in a row. Even if it's a result set with just one row of one column. If item is a row, what is item.text()?

Python's print(rec) can print what it likes, probably str(rec).

[Tejaswini_14]

I am fetching only one column which is "Player", then why should I use index values. Also I have tried to use index when I fetch all columns. e.g.

if (self.batRadio.isChecked()==True):
            bat='BAT'
            sql="SELECT * FROM stats WHERE Ctg='"+bat+"';"
            cur.execute(sql)
            records=cur.fetchall()
            for row in records:
                item=row[0]
                print(item)
                self.listWidget.addItem(item.text())

[JonB]

@Tejaswini_14 said in How do I display the records fetched from my database to my listwidget?:

I am fetching only one column which is "Player", then why should I use index values

Because the fact that your particular SELECT statement is only returning one column, instead of say SELECT column1, column2, column3, does not alter cur.fetchall()'s behaviour of returning a list or rows, each of which holds a list of columns. Hence I assume you need row[0] to pick out column #0.

Also I have tried to use index when I fetch all columns. e.g.

And? You don't say what happens? You have print(item) yet you don't bother to tell us what that shows, or even if it gets hit? And I asked

What is item.text() each time?

but you don't show that, so I don't know what you actually passing in self.listWidget.addItem(item.text())?

[Tejaswini_14]

I am getting error like this when I execute query seperately

Traceback (most recent call last):
File "C:\Python36\Scripts\FantasyCricket.py", line 17, in <module>
cur.execute('''SELECT * FROM stats WHERE Ctg="BAT";''')
sqlite3.OperationalError: no such table: stats

Even if I have the table stats stored in database

[mrjj]

@Tejaswini_14

Hi
make very, very sure that it finds
"fantcktdb.db"
else it will create new empty one
so if you have yours in say your project folder, then
when run it might create a new one in build folder.
so please use full path to it to be sure it find the one you have data in.

[JonB]

@Tejaswini_14
So since you are using Python sqlite3 library calls, not Qt ones, to access your database this is a Python issue not a Qt one, and you would be better asking that in a Python/SQLite forum. It also means that adding to a QListWidget is not itself an issue.

EDIT This post has crossed with @mrjj's which is quite possibly the cause. If you're not sure where the database file is being picked up, perhaps (temporarily) change your db=sqlite3.connect("fantcktdb.db") to specify the full path to the intended file location.

[Tejaswini_14]

@mrjj It helped me. Thanks a lot!! But I have provided the path as- "C:\Python36\fantcktdb.db"
and also I needed to open and close the database connection for every QRadioButton

[Tejaswini_14]

@JonB Thanks a lot!! It worked for me.

[JonB]

@Tejaswini_14 said in How do I display the records fetched from my database to my listwidget?:

But I have provided the path as- "C:\Python36\fantcktdb.db"

Be careful how you do this in Python. If you have a literal you must double the backslahes, e.g.

db=sqlite3.connect("C:\\Python36\\fantcktdb.db")

and also I needed to open and close the database connection for every QRadioButton

You should not be doing this. Opening/closing a db is "slow", you should keep a connection open and re-use it.