The following is a quick howto on setting up SQLAlchemy connections using megrok.rdb

import grok
from megrok import rdb
from z3c.saconfig import (EngineFactory, GloballyScopedSession)
from z3c.saconfig.interfaces import (IEngineFactory, IScopedSession, IEngineCreatedEvent)
DSN = "postgresql://user@localhost:5432/mydb"

engine_factory = EngineFactory(DSN, echo=False)
grok.global_utility(engine_factory, provides=IEngineFactory, direct=True)

scoped_session = GloballyScopedSession()
grok.global_utility(scoped_session, provides=IScopedSession, direct=True)

metadata = rdb.MetaData()

@grok.subscribe(IEngineCreatedEvent)
def create_engine_created(event):
    rdb.setupDatabase(metadata)             # To generate database create SQL
#    rdb.setupDatabaseSkipCreate(metadata)  # To skip database creation commands

A database engine factory is first installed as a global utility.  It is important to note that the initialisation of a global utility is never repeated after initial setup, regardless of how many times it is used.

Second, a global session factory is created, and also installed as a gobal utility. 

Essentially, our defining global utilities in this manner allows other parts of the code (in this case the megrok.rdb module) to easily and quickly locate our pluggable components. This means that our own modules can retrieve a session really easily by simply calling rdb.Session().

 Why use SQLAlchemy?

SQLAlchemy is a generic Object Relational Mapper (ORM) for Python.  There are many reasons for using an ORM, the most obvious and frequently quoted being immunity against SQL injection attacks, and late binding for performance gains.  In addition, the ability to work with your mapped table objects as if they were just persistent Python objects saves a huge amount of work.

Django comes with it's own built in ORM, and Django programmers must usually (^*) define table models in Python code, specifying column types and table relationships for each table.  In effect, the Django program owns the database schema.  The down sides to maintaining the schema in Python are many, not least being the continued re-synchronisation required every time a table definition changes.  Inability to use some stored database functions, and other restrictions on database features (^**), sometimes preventing an optimal database are also concerns.  Such issues are not limited to Django ORM, and these problems are easily demonstrated in other ORM implementations as well.

One particular feature of SQLAlchemy, namely the ability to build mapped Python classes by introspecting a database at runtime neatly sidesteps these problems.  We call this "table reflection".  Instead of maintaining the schema in Python, which is really poorly suited to the task, one can maintain the schema in SQL and have the Python program re-read the schema definition upon startup.  This eliminates the need for manual synchronisation, and simultaneously allows for ad-hoc database changes such as column addition/removal, referential integrity changes or stored function changes, often without needing an application restart.

Even though Grok/Zope already comes with an object database, requirements for portability and accessibility or some of the more advanced features offered by a modern RDBMS may indicate use of databases other than ZODB.  In such cases, SQLAlchemy really shines.

How does zope.schema fit into things with SQLAlchemy and mapped tables?

Schemas as defined using zope.schema fields are not directly related to SQL database schemas, although in theory one might at least partially derive a zope.schema interface from an RDBMS table.  Details such as a minimum and maximum value for integer fields, or specialised fields such as password fields are not normally seen in relational table specifications.  Such detail is specific to producing or validating HTML forms, and make little sense when defining tables.

One may gain the best of both worlds by defining a zope.schema interface, and then declaring that a mapped table class implements the interface.  That will allow automatic form generation from the mapped table class, together will all the bells and whistles a zope.schema gives, such as automatic server side updating and validation or field type-specific widget rendering and behaviour.

 megrok.rdb defines all of the plumbing one would normally need to declare mapped classes with or without reflection, column types, queries and methods.

_{* Django does allow for a workflow in which the table structure is reflected in modules.py through introspection, but most Django programmers prefer to use migrations as a benefit of defining the schema in Python.
** Imposition of mandatory sequenced ID primary key fields leads to poor database design in some cases.  Django does hide the convoluted SQL queries that result from this quite admirably.  Also, failure to define indexes on fields in tables which should be indexed or unique, often due to the presence of that automatic primary key, can lead to poor performance.}