-
-
Save harvimt/4699169 to your computer and use it in GitHub Desktop.
#!/usr/bin/env python2 | |
#-*- coding=utf-8 -*- | |
# © 2013 Mark Harviston, BSD License | |
from __future__ import absolute_import, unicode_literals, print_function | |
""" | |
Qt data models that bind to SQLAlchemy queries | |
""" | |
from PyQt4 import QtGui | |
from PyQt4.QtCore import QAbstractTableModel, QVariant, Qt | |
import logging # noqa | |
class AlchemicalTableModel(QAbstractTableModel): | |
""" | |
A Qt Table Model that binds to a SQL Alchemy query | |
Example: | |
>>> model = AlchemicalTableModel(Session, [('Name', Entity.name)]) | |
>>> table = QTableView(parent) | |
>>> table.setModel(model) | |
""" | |
def __init__(self, session, query, columns): | |
super(AlchemicalTableModel, self).__init__() | |
#TODO self.sort_data = None | |
self.session = session | |
self.fields = columns | |
self.query = query | |
self.results = None | |
self.count = None | |
self.sort = None | |
self.filter = None | |
self.refresh() | |
def headerData(self, col, orientation, role): | |
if orientation == Qt.Horizontal and role == Qt.DisplayRole: | |
return QVariant(self.fields[col][0]) | |
return QVariant() | |
def setFilter(self, filter): | |
"""Sets or clears the filter, clear the filter by setting to None""" | |
self.filter = filter | |
self.refresh() | |
def refresh(self): | |
"""Recalculates, self.results and self.count""" | |
self.layoutAboutToBeChanged.emit() | |
q = self.query | |
if self.sort is not None: | |
order, col = self.sort | |
col = self.fields[col][1] | |
if order == Qt.DescendingOrder: | |
col = col.desc() | |
else: | |
col = None | |
if self.filter is not None: | |
q = q.filter(self.filter) | |
q = q.order_by(col) | |
self.results = q.all() | |
self.count = q.count() | |
self.layoutChanged.emit() | |
def flags(self, index): | |
_flags = Qt.ItemIsEnabled | Qt.ItemIsSelectable | |
if self.sort is not None: | |
order, col = self.sort | |
if self.fields[col][3].get('dnd', False) and index.column() == col: | |
_flags |= Qt.ItemIsDragEnabled | Qt.ItemIsDropEnabled | |
if self.fields[index.column()][3].get('editable', False): | |
_flags |= Qt.ItemIsEditable | |
return _flags | |
def supportedDropActions(self): | |
return Qt.MoveAction | |
def dropMimeData(self, data, action, row, col, parent): | |
if action != Qt.MoveAction: | |
return | |
return False | |
def rowCount(self, parent): | |
return self.count or 0 | |
def columnCount(self, parent): | |
return len(self.fields) | |
def data(self, index, role): | |
if not index.isValid(): | |
return QVariant() | |
elif role not in (Qt.DisplayRole, Qt.EditRole): | |
return QVariant() | |
row = self.results[index.row()] | |
name = self.fields[index.column()][2] | |
return unicode(getattr(row, name)) | |
def setData(self, index, value, role=None): | |
row = self.results[index.row()] | |
name = self.fields[index.column()][2] | |
try: | |
setattr(row, name, value.toString()) | |
self.session.commit() | |
except Exception as ex: | |
QtGui.QMessageBox.critical(None, 'SQL Error', unicode(ex)) | |
return False | |
else: | |
self.dataChanged.emit(index, index) | |
return True | |
def sort(self, col, order): | |
"""Sort table by given column number.""" | |
self.sort = order, col | |
self.refresh() |
#(leaves out sqlalchemy & PyQt boilerplate, will not run) | |
#Define SQL Alchemy model | |
from qvariantalchemy import String, Integer, Boolean | |
from sqlalchemy.ext.declarative import declarative_base | |
Base = declarative_base() | |
class Entity(Base): | |
__tablename__ = 'entities' | |
ent_id = Column(Integer, primary_key=True) | |
name = Column(String) | |
enabled = Column(Boolean) | |
#create QTable Model/View | |
from alchemical_model import AlchemicalTableModel | |
model = AlchemicalTableModel( | |
Session, #FIXME pass in sqlalchemy session object | |
Entity, #sql alchemy mapped object | |
[ # list of column 4-tuples(header, sqlalchemy column, column name, extra parameters as dict | |
# if the sqlalchemy column object is Entity.name, then column name should probably be name, | |
# Entity.name is what will be used when setting data, and sorting, 'name' will be used to retrieve the data. | |
('Entity Name', Entity.name, 'name', {'editable': True}), | |
('Enabled', Entity.enabled, 'enabled', {}), | |
]) | |
table = QTableView(parent) | |
table.setModel(model) |
#!/usr/bin/env python2 | |
#-*- coding=utf-8 -*- | |
# © 2013 Mark Harviston, BSD License | |
from __future__ import absolute_import, unicode_literals, print_function | |
""" | |
SQLAlchemy types for dealing with QVariants & various QTypes (like QString) | |
""" | |
import datetime | |
from PyQt4.QtCore import QVariant | |
from sqlalchemy import types | |
def gen_process_bind_param(pytype, toqtype, self, value, dialect): | |
if value is None: | |
return None | |
elif isinstance(value, QVariant): | |
return pytype(toqtype(value)) | |
elif not isinstance(value, pytype): | |
return pytype(value) | |
else: | |
return value | |
class Integer(types.TypeDecorator): | |
impl = types.Integer | |
def process_bind_param(self, value, dialect): | |
return gen_process_bind_param( | |
long, lambda value: value.toLongLong(), | |
self, value, dialect) | |
class Boolean(types.TypeDecorator): | |
impl = types.Boolean | |
def process_bind_param(self, value, dialect): | |
return gen_process_bind_param( | |
bool, lambda value: value.toBool(), | |
self, value, dialect) | |
class String(types.TypeDecorator): | |
impl = types.Unicode | |
def process_bind_param(self, value, dialect): | |
return gen_process_bind_param( | |
unicode, lambda value: value.toString(), | |
self, value, dialect) | |
class Enum(types.TypeDecorator): | |
impl = types.Enum | |
def process_bind_param(self, value, dialect): | |
return gen_process_bind_param( | |
unicode, lambda value: value.toString(), | |
self, value, dialect) | |
class DateTime(types.DateTime): | |
impl = types.DateTime | |
def process_bind_param(self, value, dialect): | |
return gen_process_bind_param( | |
datetime.datetime, lambda value: value.toDateTime(), | |
self, value, dialect) |
As example, you write: model = AlchemicalTableModel(Session, [('Name', Entity.name)])
- but the AlchemicalTableModel has 3 parameters, not two, and even in the example you use Entity as second parameter, which produces an error, because it is no query.
This code is not working - is there an update from your side? - I can't figure it out.
Thank you! I am trying to write something similar, and a few lines of code really helped.
I am trying to add items to ComboBox from database, and items added, but console was printed in error: TypeError: PyQt4.QtCore.QVariant represents a mapped type and cannot be instantiated. This error is in method data(), in line: elif role not in (Qt.DisplayRole, Qt.EditRole):
return QVariant()
How can I fix this error?
I am new to QT and SQLAlchemy , but I think this is a fantastic code. Really great!!!