Populate a QTableView from SQLAlchemy

Question

I am trying to create some small GUI app using PyQt5 and SQLAlchemy as orm, however, I can't seem to find a way to populate a QTableView from a SQLAlchemy Query.

My goal is retrieve data using SQLAlchemy and then show the query result in QTableView.

I am able to fetch data using the code below:

def fetch_data():
    metadata = db.schema.MetaData(bind=engine, reflect=True)
    table = db.Table('tbl_inv', metadata, autoload=True)

    query = db.select([
         table.columns.code,
         table.columns.decription,
         table.columns.uom,
    ])

    result = conn.execute(query)
    result_set = result.fetchall()

    return result_set

I am trying to implement this, but to no success. Fastest way to populate QTableView from Pandas data frame.

I guess I have to put my data into dataframe then use the above links solution, but I am sure that this would be costly in terms of operations.

score 4 · Accepted Answer · answered Nov 06 '19 at 09:19

Step Of Procedure

Setup Environment

sudo apt install python3-pip
sudo pip3 install pyqt5
sudo pip3 install sqlalchemy

Run demo.py (it will insert test data to sqlite locally)

from PyQt5.QtWidgets import QApplication, QWidget, QTableView, QAbstractItemView, QVBoxLayout
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5 import QtGui, QtCore
import sqlalchemy as db
import os

# 1. rm demo.db
os.remove("demo.db")

engine = db.create_engine('sqlite:///demo.db?check_same_thread=False', echo=True)
# 2. Creates demo table
connection = engine.connect()
metadata = db.MetaData()
demoTable = db.Table('demo', metadata,
            db.Column('code', db.String(255), nullable=False),
            db.Column('decription', db.String(255), nullable=False),
            db.Column('uom', db.String(255), nullable=False)
            )

metadata.create_all(engine) 

# 3. Creates demo data
query = db.insert(demoTable) 
values_list = [
    {'code':'058176', 'decription':'01', 'uom':'rb1705,rb1710'},
    {'code':'058176', 'decription':'02', 'uom':'cu1705,cu1710'},
    {'code':'058176', 'decription':'03', 'uom':'zn1705,zn1710'},
    {'code':'058176', 'decription':'04', 'uom':'rb1705,rb1710'},
    {'code':'058176', 'decription':'01', 'uom':'zn1705,zn1710'},
    {'code':'058176', 'decription':'02', 'uom':'ru1705,ru1710'},
    {'code':'058176', 'decription':'02', 'uom':'ni1705,ni1710'},
    {'code':'058176', 'decription':'01', 'uom':'rb1705,rb1710'},
]
ResultProxy = connection.execute(query,values_list)

class DemoWindow(QWidget):
    def __init__(self, header, *args):
        QWidget.__init__(self, *args)
        self.setWindowTitle("Demo QTableView")

        self.table_model = DemoTableModel(self, header)
        self.table_view = QTableView()
        self.table_view.setModel(self.table_model)
        layout = QVBoxLayout(self)
        layout.addWidget(self.table_view)
        self.setLayout(layout)

class DemoTableModel(QAbstractTableModel):

    def __init__(self, parent, header, *args):
        QAbstractTableModel.__init__(self, parent, *args)
        # 5. fetch data
        results = connection.execute(db.select([demoTable])).fetchall()
        self.mylist = results
        self.header = header

    def rowCount(self, parent):
        return len(self.mylist)

    def columnCount(self, parent):
        return len(self.mylist[0])

    def data(self, index, role):
        # 5. populate data
        if not index.isValid():
            return None
        if (role == Qt.DisplayRole):
            return self.mylist[index.row()][index.column()]
        else:
            return QVariant()

    def headerData(self, col, orientation, role):
        if orientation == Qt.Horizontal and role == Qt.DisplayRole:
            return self.header[col]
        return None

if __name__ == '__main__':
    app = QApplication([])
    header = ['code', 'decription', 'uom']
    win = DemoWindow(header)
    win.show()
    app.exec_()

This demo works great, will mark as accepted answer once i have successfully applied to my app! — Josh21, Nov 07 '19 at 00:45
I just want to know how to display the dates? since its showing blank in my app, also, how can I refresh the `QTableView` whenever I modify the database? — Josh21, Nov 07 '19 at 01:22
just paste a gist here you can follow up. https://gist.github.com/345161974/dd5003ed9b706adc557ee12e6a344c6e. — suiwenfeng, Nov 07 '19 at 03:04
it`s a pyqt4 example, but it shows how to refresh QTableView. see updateModel — suiwenfeng, Nov 07 '19 at 03:05
Accepting this as an answer as I was able to implement it in my app. Thanks for the link to the `updateModel`, still trying to implement it in my app.! — Josh21, Nov 07 '19 at 07:56

Populate a QTableView from SQLAlchemy

1 Answers1

Step Of Procedure