How can I read data from database and show it in a PyQt table

Question

I am trying to load data from database that I added to the database through this code PyQt integration with Sqlalchemy .I want the data from the database to be displayed into a table.I have tried this code.But,I am not getting it to load the data appropriately.I think the SqlAlchemy query may be wrong. Any help will be appreciated.

I am getting this error:

File "/Users/tunji/Desktop/plain.py", line 75, in load
    result = session(Employee).query.all()

NameError: global name 'Employee

but when I updated this line of code:

from employee import Employee

I am now having this error:

Traceback (most recent call last): File "plain.py", line 82, in load for column_number,data in enumerate(row_data): TypeError: 'Employee' object is not iterable

# Form implementation generated from reading ui file 'plain.ui'
#
# Created by: PyQt4 UI code generator 4.12.1
#
# WARNING! All changes made in this file will be lost!

from PyQt4 import QtCore, QtGui
import sqlalchemy 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, MetaData,Table
from sqlalchemy.orm import mapper,Session

try:
    _fromUtf8 = QtCore.QString.fromUtf8
except AttributeError:
    def _fromUtf8(s):
        return s

try:
    _encoding = QtGui.QApplication.UnicodeUTF8
    def _translate(context, text, disambig):
        return QtGui.QApplication.translate(context, text, disambig, _encoding)
except AttributeError:
    def _translate(context, text, disambig):
        return QtGui.QApplication.translate(context, text, disambig)

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName(_fromUtf8("MainWindow"))
        MainWindow.resize(800, 576)
        self.centralwidget = QtGui.QWidget(MainWindow)
        self.centralwidget.setObjectName(_fromUtf8("centralwidget"))
        self.tableWidget = QtGui.QTableWidget(self.centralwidget)
        self.tableWidget.setGeometry(QtCore.QRect(220, 120, 256, 192))
        self.tableWidget.setRowCount(15)
        self.tableWidget.setColumnCount(3)
        self.tableWidget.setObjectName(_fromUtf8("tableWidget"))
        self.btn_load = QtGui.QPushButton(self.centralwidget)
        self.btn_load.setGeometry(QtCore.QRect(310, 390, 113, 32))
        self.btn_load.setObjectName(_fromUtf8("btn_load"))
        MainWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QtGui.QStatusBar(MainWindow)
        self.statusbar.setObjectName(_fromUtf8("statusbar"))
        MainWindow.setStatusBar(self.statusbar)

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

    def retranslateUi(self, MainWindow):
        MainWindow.setWindowTitle(_translate("MainWindow", "MainWindow", None))
        self.btn_load.setText(_translate("MainWindow", "Load", None))

Base = declarative_base()
engine = sqlalchemy.create_engine("sqlite:///employee.db", echo='debug')
Base.metadata.create_all(engine)
metadata = MetaData()

DBsession = sqlalchemy.orm.sessionmaker(bind=engine) 
session = DBsession()




class MainWindow(QtGui.QMainWindow, Ui_MainWindow):
    def __init__(self, parent=None):
        super(MainWindow, self).__init__(parent)
        self.setupUi(self)
        self.btn_load.clicked.connect(self.load)    

    @QtCore.pyqtSlot()
    def load(self):
        #esult = Table('employees',metadata,autoload=True,autoload_with=engine)
        result = session(Employee).query.all()


        self.tableWidget.setRowCount(0)

        for row_number,row_data in enumerate(result):
            self.tableWidget.insertRow(row_number)
            for column_number,data in enumerate(row_data):
                self.tableWidget.setItem(row_number,column_number,QtGui.QTableWidgetItem(str(data)))


if __name__ == "__main__":
    import sys
    app = QtGui.QApplication(sys.argv)
    m = MainWindow()
    m.show()
    sys.exit(app.exec_())

This line in the `MainWindow.load()` method: `result = session(Employee).query.all()`. The variable `Employee` is not defined anywhere in your module. Either you need to define the `Employee` model in you module, or import it from somewhere. — SuperShoot, Nov 13 '18 at 09:10

score 1 · Answer 1 · answered Nov 16 '18 at 06:27

# -*- coding: utf-8 -*-

# Form implementation generated from reading ui file 'plain.ui'
#
# Created by: PyQt4 UI code generator 4.12.1
#
# WARNING! All changes made in this file will be lost!

from PyQt4 import QtCore, QtGui
import sqlalchemy 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine, MetaData,Table
from sqlalchemy.orm import mapper,Session
from employee import Employee

try:
    _fromUtf8 = QtCore.QString.fromUtf8
except AttributeError:
    def _fromUtf8(s):
        return s

try:
    _encoding = QtGui.QApplication.UnicodeUTF8
    def _translate(context, text, disambig):
        return QtGui.QApplication.translate(context, text, disambig, _encoding)
except AttributeError:
    def _translate(context, text, disambig):
        return QtGui.QApplication.translate(context, text, disambig)

class Ui_MainWindow(object):
    def setupUi(self, MainWindow):
        MainWindow.setObjectName(_fromUtf8("MainWindow"))
        MainWindow.resize(800, 576)
        self.centralwidget = QtGui.QWidget(MainWindow)
        self.centralwidget.setObjectName(_fromUtf8("centralwidget"))
        self.tableWidget = QtGui.QTableWidget(self.centralwidget)
        self.tableWidget.setGeometry(QtCore.QRect(100, 300, 700,300))
        self.tableWidget.setRowCount(15)
        self.tableWidget.setColumnCount(3)
        self.tableWidget.setObjectName(_fromUtf8("tableWidget"))
        self.btn_load = QtGui.QPushButton(self.centralwidget)
        self.btn_load.setGeometry(QtCore.QRect(360, 650, 200, 32))
        self.btn_load.setObjectName(_fromUtf8("btn_load"))
        MainWindow.setCentralWidget(self.centralwidget)
        self.statusbar = QtGui.QStatusBar(MainWindow)
        self.statusbar.setObjectName(_fromUtf8("statusbar"))
        MainWindow.setStatusBar(self.statusbar)

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

    def retranslateUi(self, MainWindow):
        MainWindow.setWindowTitle(_translate("MainWindow", "Display Database", None))
        self.btn_load.setText(_translate("MainWindow", "Load", None))

Base = declarative_base()
engine = sqlalchemy.create_engine("sqlite:///employee.db", echo='debug')
Base.metadata.create_all(engine)
metadata = MetaData()

DBsession = sqlalchemy.orm.sessionmaker(bind=engine) 
session = DBsession()




class MainWindow(QtGui.QMainWindow, Ui_MainWindow):
    def __init__(self, parent=None):
        super(MainWindow, self).__init__(parent)
        self.setupUi(self)
        self.btn_load.clicked.connect(self.load)    

    @QtCore.pyqtSlot()
    def load(self):
        employees = Table('employees',metadata,autoload=True,autoload_with=engine)
        result = session.query(employees).all()


        self.tableWidget.setRowCount(0)

        for row_number,row_data in enumerate(result):
            self.tableWidget.insertRow(row_number)
            for column_number,data in enumerate(row_data):
                self.tableWidget.setItem(row_number,column_number,QtGui.QTableWidgetItem(str(data)))


if __name__ == "__main__":
    import sys
    app = QtGui.QApplication(sys.argv)
    m = MainWindow()
    m.show()
    sys.exit(app.exec_())

How can I read data from database and show it in a PyQt table

1 Answers1

Linked