harvimt · February 2, 2013 20:41 · Feb 2, 2013
diff --git a/alchemical_model.py b/alchemical_model.py
@@ -0,0 +1,129 @@
+#!/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()
diff --git a/example.py b/example.py
@@ -0,0 +1,27 @@
+#(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)
diff --git a/qvariantalchemy.py b/qvariantalchemy.py
@@ -0,0 +1,68 @@
+#!/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)