tw2.sqla is a database layer for ToscaWidgets 2 and SQLAlchemy. It allows common database tasks to be achieved with minimal code. There are four main features:
- Session and transaction management
- Loading and saving data
- Populating selection fields
- Generating widget definitions
See the :ref:`design` document for a more detailed description of these.
tw2.sqla is designed to work fully however you define your model objects - traditional, declarative base, or Elixir.
If you are using tw2.sqla with another framework (e.g. Pyramid), the framework will already be providing session management. You do not need to use the session management within tw2.sqla. However, database objects used with tw2.sqla must have a query property.
For standalone tw2.sqla, the repoze.tm middleware needs to be installed in the stack. This can be done by passing repoze_tm=True to tw2.core.make_middleware or tw2.devtools.dev_server. For example:
To set the query property to use ZopeTransactionExtension, appropriate code must be added to your model. The examples below are for standalone tw2.sqla.
For declarative base:
from sqlalchemy.ext.declarative import declarative_base import tw2.sqla as tws Base = declarative_base() Base.query = tws.transactional_session().query_property()
import elixir as el, tw2.sqla as tws el.session = tws.transactional_session()
Once this is setup, the application does not need to explicitly deal with sessions.
TBD Provide further examples for other frameworks.
Loading and Saving Data
There are several Page subclasses that automatically load and save data. Each have an entity property that must be set to an SQLAlchemy object.
This presents a list of items.
This allows editing of a single item. The item is loaded based on primary key columns in the query string. When the form is posted, the data is saved back to the database. The user is redirected to the URL specified by the redirect parameter.
This allows editing of a multiple items, e.g. allow you to edit a whole list of users. This may be removed in future, if a way is found to incorporate this functionality with DbFormPage.
In addition, tw2.sqla.DbLinkField can be used to generate a link to a DbFormPage. It adds all the primary key columns from an object to the query string.
TBD There is no way to filter what is displayed in the list - although a partial workaround is to map the underlying SQLAlchemy object to a select statement, which performs the filtering. Also, DbFormPage has no protection against parameter tampering.
Populating selection fields
DbSelectionField automatically loads it's contents from a database table. It has an entity property that must be set to an SQLAlchemy object. The subclasses are:
- DbSingleSelectLink - LinkContainer with an inner DbSingleSelectField (experimental)
Note: composite primary keys are not supported by these fields.
Internally it uses tw2.sqla.RelatedValidator which converts ID values to and from objects. You must always apply the widget to a relation, not the underlying column. For example:
class User(Base): group_name = sa.Column(sa.String(), sa.ForeignKey('group')) group = sao.relationship('Group') class UserForm(twf.TableForm): group = tws.DbSingleSelectField(entity=Group)
TBD There is no way to filter what is displayed in the list - although a partial workaround is to map the underlying SQLAlchemy object to a select statement, which performs the filtering. Also, there is no protection against parameter tampering.
WidgetPolicy generates widgets from SQLAlchemy property objects. It uses the column type, name, and attributes such as nullable. Two subclasses are provided: ViewPolicy and EditPolicy. For example, EditPolicy generates SQLAlchemy Date columns as CalendarDataPicker widgets. Users can further subclass these policies to suit their own needs.
AutoContainer is a widget that generates its own children automatically, using an SQLAlchemy model object, and a WidgetPolicy. Several subclasses are provided:
class MyForm(tws.AutoTableForm): entity = model.MyObject
Individual fields can be overridden. For example, if address is automatically generated as a TextField but you need a TextArea, do this:
class MyForm(tws.AutoTableForm): entity = model.MyObject address = twf.TextArea()
To suppress a field, use tws.NoWidget.
- Sometimes you want a way to say "only include these fields"
- Hints on the model, using the info attribute - experimental; needs tests & doc
- There are experimental widgets for AutoListPage and AutoListPageEdit. The biggest issue is linking between them.