Re: What is the right way to use PostgreSQL with PyQt5
Stephen Waterbury <[email protected]> Wed, 10 Jun 2020 19:34:07 -0400
| Newsgroups | gmane.comp.python.pyqt-pykde |
|---|---|
| Message-ID | <[email protected]> |
This is a multi-part message in MIME format.
--------------93767F0959D1569858A6C724
Content-Type: multipart/alternative;
boundary="------------84A86B2869C57B37A6130784"
--------------84A86B2869C57B37A6130784
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
You don't need to "invent a new MVC", just use the MVC in PyQt:
QTableView is usable with any type of model that can be built using,
for example, QAbstractTableModel. I'm attaching some example code
that has an "ODTableModel" for which the "model" is a list of OrderedDict
instances, and an "ObjectTableModel" that subclasses ODTableModel to
use arbitrary objects, which could be, for example, sqlalchemy objects.
You write your own code to do the sqlalchemy interactions with the
database, and just call them from the ObjectTableModel ...
that is one easy way to apply the PyQt MVC with an ORM back-end.
This example code is based on code I use in my app, which does
exactly what I describe.
You would want to create a subclass of QTableView that could be
called "ObjectTableView" that takes a list of object instances and a "view"
(a list of the names of the attributes that you want to display). That's
where you could create, say, a context menu that gives you various
options like to delete an object, etc. Again, the objects can be sqlalchemy
objects, and you can do db operations using them (sqlalchemy objects are
basically always in a transaction, which you can use to do operations and
commits, etc.).
Steve
On 6/10/20 5:42 PM, Nenad Lamza wrote:
> Thanks Sibylle, do you know how much work it takes, inventing
> completely new MVC? I haven't found any example of it (SQLAlchemy,
> psycopg2, QTableView, (QDataWidgetMapper) together) on the web. Can
> you put any link. It is much more practical and logical to use PyQt
> Sql classes with QTableView. I avoid QAbstractItemModel and use it
> only for very very special cases. It is so sad that Qt and PyQt
> haven't documented what PostgreSQL drivers and what version of them we
> should use with particular Qt/PyQt versions. And because of that you
> suggest me to turn the upside down the whole Qt MVC framework. I won't
> do that. The app with standard PyQt MVC, QSqlDatabase, QSqlQuery,
> QSqlQueryModel, QSqlQueryModel,..., QTableView works so great that I
> think it is easier to (randomly) find the right combination of drivers
> than write the whole new framework. Nenad Am 10.06.2020 um 14:34
> schrieb Nenad Lamza:
>> Thanks Barry (and also Dennis) to your answers.
>>
>> So, I have a choice:
>>
>> 1. Use standard PyQt MVC, QSqlDatabase, QSqlQuery, QSqlQueryModel,
>> QSqlQueryModel, QTableView,..., but without ORM like SqlAlchemy, and
>> potentially have problems with PostgreSQL drivers
>>
>> 2. Use other PostgreSQL drivers like psycopg2 and don't use PyQt MVC and
>> Sql classes and use ORM like SqlAlchemy with business logic and objects.
>> Isn't the whole point of (Py)Qt MVC to use all those classes I
>> mentioned? I can't imagine in that case (without PyQt Sql classes and
>> using SqlAlchemy) how would I present data to the user in eg. QTableView
>> or some other GUI table on screen? Building my own MVC (it takes years)?
>>
> You can use the Qt Model-View classes, just without QtSql. Instead you
> could create your own Model class, subclassed from QAbstractItemModel or
> use a QStandardItemModel (or subclass that). And put your data, got via
> SQLAlchemy or directly from psycopg2, into that model. QTableView and
> QDataWidgetMapper can use that just as well as the QSql...Model classes.
>
> HTH
> Sibylle
--------------84A86B2869C57B37A6130784
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p><font face="Helvetica, Arial, sans-serif">You don't need to
"invent a new MVC", just use the MVC in PyQt:<br>
QTableView is usable with any type of model that can be built
using,<br>
for example, QAbstractTableModel. I'm attaching some example
code<br>
that has an "ODTableModel" for which the "model" is a list of
OrderedDict<br>
instances, and an "ObjectTableModel" that subclasses
ODTableModel to<br>
use arbitrary objects, which could be, for example, sqlalchemy
objects.<br>
You write your own code to do the sqlalchemy interactions with
the<br>
database, and just call them from the ObjectTableModel ...<br>
that is one easy way to apply the PyQt MVC with an ORM back-end.<br>
This example code is based on code I use in my app, which does<br>
exactly what I describe.<br>
</font></p>
<p><font face="Helvetica, Arial, sans-serif">You would want to
create a subclass of QTableView that could be<br>
called "ObjectTableView" that takes a list of object instances
and a "view"<br>
(a list of the names of the attributes that you want to
display). That's<br>
where you could create, say, a context menu that gives you
various<br>
options like to delete an object, etc. Again, the objects can
be sqlalchemy<br>
objects, and you can do db operations using them (sqlalchemy
objects are<br>
basically always in a transaction, which you can use to do
operations and<br>
commits, etc.).<br>
</font></p>
<p><font face="Helvetica, Arial, sans-serif">Steve<br>
</font></p>
<p><font face="Helvetica, Arial, sans-serif">On 6/10/20 5:42 PM,
Nenad Lamza wrote:
<blockquote type="cite">
<meta http-equiv="Content-Type" content="text/html;
charset=UTF-8">
<pre class="moz-quote-pre" wrap=""><div class="moz-txt-sig">Thanks Sibylle,
do you know how much work it takes, inventing completely new MVC? I haven't found any example of it (SQLAlchemy, psycopg2, QTableView, (QDataWidgetMapper) together) on the web. Can you put any link. It is much more practical and logical to use PyQt Sql classes with QTableView. I avoid QAbstractItemModel and use it only for very very special cases.
It is so sad that Qt and PyQt haven't documented what PostgreSQL drivers and what version of them we should use with particular Qt/PyQt versions.
And because of that you suggest me to turn the upside down the whole Qt MVC framework. I won't do that. The app with standard PyQt MVC, QSqlDatabase, QSqlQuery, QSqlQueryModel, QSqlQueryModel,..., QTableView works so great that I think it is easier to (randomly) find the right combination of drivers than write the whole new framework.
Nenad
Am 10.06.2020 um 14:34 schrieb Nenad Lamza:
</div></pre>
<blockquote type="cite" style="color: #000000;">
<pre class="moz-quote-pre" wrap="">Thanks Barry (and also Dennis) to your answers.
So, I have a choice:
1. Use standard PyQt MVC, QSqlDatabase, QSqlQuery, QSqlQueryModel,
QSqlQueryModel, QTableView,..., but without ORM like SqlAlchemy, and
potentially have problems with PostgreSQL drivers
2. Use other PostgreSQL drivers like psycopg2 and don't use PyQt MVC and
Sql classes and use ORM like SqlAlchemy with business logic and objects.
Isn't the whole point of (Py)Qt MVC to use all those classes I
mentioned? I can't imagine in that case (without PyQt Sql classes and
using SqlAlchemy) how would I present data to the user in eg. QTableView
or some other GUI table on screen? Building my own MVC (it takes years)?
</pre>
</blockquote>
<pre class="moz-quote-pre" wrap="">You can use the Qt Model-View classes, just without QtSql. Instead you
could create your own Model class, subclassed from QAbstractItemModel or
use a QStandardItemModel (or subclass that). And put your data, got via
SQLAlchemy or directly from psycopg2, into that model. QTableView and
QDataWidgetMapper can use that just as well as the QSql...Model classes.
HTH
Sibylle</pre>
</blockquote>
<br>
</font></p>
<p><font face="Helvetica, Arial, sans-serif"></font><br>
</p>
</body>
</html>
--------------84A86B2869C57B37A6130784--
--------------93767F0959D1569858A6C724
Content-Type: text/x-python; charset=UTF-8;
name="custom_tablemodels.py"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="custom_tablemodels.py"
"""
Some custom TableModels for use with a QTableView.
"""
import sys # only needed for testing stuff
from collections import OrderedDict
# PyQt
from PyQt5.QtCore import Qt, QAbstractTableModel, QModelIndex, QVariant
# only needed for testing stuff:
from PyQt5.QtWidgets import QApplication, QWidget, QTableView, QVBoxLayout
test_od = [OrderedDict([('spam','00'), ('eggs','01'), ('more spam','02')]),
OrderedDict([('spam','10'), ('eggs','11'), ('more spam','12')]),
OrderedDict([('spam','20'), ('eggs','21'), ('more spam','22')])]
class ODTableModel(QAbstractTableModel):
"""
A table model based on a list of OrderedDict instances.
"""
def __init__(self, ods, parent=None, **kwargs):
"""
Args:
ods (list): list of OrderedDict instances
Keyword Args:
parent (QWidget): parent widget
"""
super().__init__(parent=parent, **kwargs)
# TODO: some validity checking on the data ...
self.ods = ods or [{0:'no data'}]
def columns(self):
return list(self.ods[0].keys())
def headerData(self, section, orientation, role=Qt.DisplayRole):
if role == Qt.DisplayRole and orientation == Qt.Horizontal:
return self.columns()[section]
return QAbstractTableModel.headerData(self, section, orientation, role)
def rowCount(self, parent=QModelIndex()):
return len(self.ods)
def columnCount(self, parent):
try:
return len(self.ods[0])
except:
return 1
def setData(self, index, value, role=Qt.UserRole):
"""
Reimplementation in which 'value' is an OrderedDict.
"""
if index.isValid():
if index.row() < len(self.ods):
self.ods[index.row()] = value
else:
print('* setData(): index is out of range')
# NOTE the 3rd arg is an empty list -- reqd for pyqt5
# (or the actual role(s) that changed, e.g. [Qt.EditRole])
self.dataChanged.emit(index, index, [])
return True
return False
def removeRows(self, row, count, parent=QModelIndex()):
if row < len(self.ods):
# self.beginRemoveRows()
self.beginResetModel()
del self.ods[row]
# self.endRemoveRows()
self.endResetModel()
# NOTE the 3rd arg is an empty list -- reqd for pyqt5
# (or the actual role(s) that changed, e.g. [Qt.EditRole])
idx = self.createIndex(row, 0)
self.dataChanged.emit(idx, idx, [])
return True
else:
return False
def data(self, index, role=Qt.DisplayRole):
if not index.isValid():
return QVariant()
elif role != Qt.DisplayRole:
return QVariant()
return self.ods[index.row()].get(
self.columns()[index.column()], '')
class ObjectTableModel(ODTableModel):
"""
A ODTableModel subclass based on a list of objects.
Attributes:
cname (str): class name of the objects
column_labels (list): list of column header labels (strings)
"""
def __init__(self, objs, view=None, parent=None, **kwargs):
"""
Args:
objs (list): list of objects of the same class
Keyword Args:
view (list): list of field names (columns)
parent (QWidget): parent widget
"""
print("* ObjectTableModel initializing ...")
self.objs = objs or []
print(" ... with {} objects.".format(len(objs)))
self.column_labels = ['No Data']
self.view = view or ['']
self.cname = ''
if self.objs:
self.cname = objs[0].__class__.__name__
if self.view:
# sanity-check view
self.view = [a for a in self.view if hasattr(objs[0], a)]
# NOTE: this works but may need performance optimization when
# the table holds a large number of objects
ods = [self.get_odict_for_obj(o, self.view) for o in self.objs]
self.column_labels = self.view
else:
ods = [{0:'no data'}]
self.view = ['id']
super().__init__(ods, parent=parent, **kwargs)
def get_odict_for_obj(self, obj, view):
"""
Return the OrderedDict representation of an object.
"""
odict = OrderedDict()
for name in view:
val = str(getattr(obj, name))
odict[name] = val
return odict
def headerData(self, section, orientation, role=Qt.DisplayRole):
if role == Qt.DisplayRole and orientation == Qt.Horizontal:
return self.column_labels[section]
return QAbstractTableModel.headerData(self, section, orientation, role)
def setData(self, index, obj, role=Qt.UserRole):
"""
Reimplementation using an object as the 'value' (based on underlying
ODTableModel setData, which takes an OrderedDict).
"""
try:
# apply ODTableModel.setData, which takes an OrderedDict as value
super().setData(index, self.get_odict_for_obj(obj, self.view))
# this 'dataChanged' should not be necessary, since 'dataChanged is
# emitted by the 'setData' we just called
super().dataChanged.emit(index, index)
return True
except:
return False
def add_object(self, obj):
self.objs.append(obj)
# NOTE: begin/endResetModel works better than begin/endInsertRows
new_row = len(self.objs) - 1
idx = self.createIndex(new_row, 0)
self.beginResetModel()
self.setData(idx, obj)
self.endResetModel()
return True
def mod_object(self, obj):
try:
row = self.objs.index(obj) # raises ValueError if problem
idx = self.index(row, 0, parent=QModelIndex())
self.beginResetModel()
self.setData(idx, obj)
self.endResetModel()
return idx
except:
# maybe my C++ object got deleted ...
print('object "{}" not in list.'.format(str(obj)))
return QModelIndex()
def removeRow(self, row, index=QModelIndex()):
if row < len(self.objs):
self.objs = self.objs[:row] + self.objs[row+1:]
self.removeRows(row, 1, index)
return True
else:
return False
def main():
w = Window()
w.show()
sys.exit(app.exec_())
class Window(QWidget):
def __init__(self, *args, **kwargs):
QWidget.__init__(self, *args, **kwargs)
tablemodel = ODTableModel(test_od)
tableview = QTableView()
tableview.setModel(tablemodel)
layout = QVBoxLayout(self)
layout.addWidget(tableview)
self.setLayout(layout)
if __name__ == "__main__":
app = QApplication(sys.argv)
main()
--------------93767F0959D1569858A6C724--