SQLAHelper 1.0 released, and a proposal
Mike Orr
container for a SQLAlchemy contextual session, declarative base, and
engines. This avoids circular dependencies between model modules, and
allows cooperating third-party libraries to use the application's
Session and Base (which must exist when tables are defined). It's
used by Akhet, and was originally part of the pyramid_sqla scaffold
that Akhet came out of.
Version 1.0 is mostly a documentation update. A ``set_base()``
function is added to customize the declarative base. An out-of-date
demo was deleted.
I haven't said much about SQLAHelper in recent months, so many users
may not be aware that it exists. This has led to other packages that
do approximately the same thing. There was one mentioned here a few
days ago (but I can't find the original message saying what it's
called), and Ptah has a similar feature built-in. But this kind of
thing only works if all packages agree on where the Session and Base
are located. So ChrisM and I would like to standardize on SQLAHelper
as the base package for this. So please try it out, and if you find
any omissions that prevent it from being suitable as a common
dependency between applications and third-party libraries, please let
me know.
--
Mike Orr <slugg...@gmail.com>
cd34
Github: https://github.com/Pylons/SQLAHelper
Docs: http://readthedocs.org/docs/sqlahelper/en/latest/index.html
Ahmed
I think it would be great to standardize on this sort of library for
Pyramid.
I am now currently coding my first Pyramid app, but I am separating
some biolerplate/common code in a separate library that I can reuse
with all my coming applications.
In this library, there is code that needs to point to my
DeclarativeBase (and sometimes the scoped session too) I use in my
application. But since I want the app depending on the library and not
the opposite, I needed a way to reference the base and session.
So to clarify again, the function of SQLAHelper is to help me
reference my declarative base(s) (or session) via
SQLAhelper.get_base() in the library code, without a direct reference
to the app? And if so, how does this work, does it do that by looking
into the thread or what?
My second question is about the use of a second engine. I looked at
SQLAHelper docs and found it supports a default engine plus additional
engines. Now I use one database but there is only one place where I
can need to look at a second database. What is the best practice here?
Do I use for the new database models the same DeclarativeBase as the
1st database models and use the SQLAHelper bind method for these
models, or do I give them a separate Base (but how will get_base()
then work???) ?
If SQLAHelper addresses these issues (and assure me that SQLAHelper
will be the standard way of managing this issue in Pyramid), I might
as well make SQLAHelper a dependency of my library or apps and recode
these parts immediately.
Cheers,
Ahmed
Mike Orr
> So to clarify again, the function of SQLAHelper is to help me
> reference my declarative base(s) (or session) via
> SQLAhelper.get_base() in the library code, without a direct reference
> to the app? And if so, how does this work, does it do that by looking
> into the thread or what?
No, there's no magic. Magic got a bad reputation in Pylons1, CherryPy,
etc. SQLAHelper defines a declarative base and a contextual session.
You can use them if you want to, or define your own, but to get the
sharing benefit you'll have to use its.
The Akhet manual has an example of the recommended usage.
http://docs.pylonsproject.org/projects/akhet/en/latest/model_examples.html
Model Examples
(Note: the 'akhet' *scaffold* will probably go away in the next
version, but the techniques in the manual will remain.)
In the normal case, you don't define a Base or Session, but instead
use the ones SQLAHelper has predefined.
# models.py
import sqlahelper
Base = sqlahelper.get_base()
Session = sqlahelper.get_session() # Contextual DBSession
However, if you're passing arguments to declarative_base() to
customize the Base, you'll have to do it differently. You'll have to
define the Base and then tell SQLAHelper to use it. This is a new
feature added to SQLAHelper 1.0 so it's not shown in the Akhet manual
yet.
# models.py
import sqlahelper
import sqlalchemy.ext.declarative as declarative
Session = sqlahelper.get_session()
class MyDeclarativeSuperclass(object):
@classmethod
def query(class_):
""""Return a query of the ORM subclass."""
return Session.query(class_)
Base = declarative.declarative_base(cls=MyDeclarativeSuperclass)
sqlahelper.set_base(Base)
In this case, since you're modifying SQLAHelper's base, you'll have to
execute this code before importing any modules that call get_base().
> My second question is about the use of a second engine. I looked at
> SQLAHelper docs and found it supports a default engine plus additional
> engines. Now I use one database but there is only one place where I
> can need to look at a second database. What is the best practice here?
> Do I use for the new database models the same DeclarativeBase as the
> 1st database models and use the SQLAHelper bind method for these
> models, or do I give them a separate Base (but how will get_base()
> then work???) ?
A Base has a metadata, and a metadata references tables by name. So as
long as two different tables don't have the same name, you can use the
same metadata for both databases, and thus the same Base. That's
*can*, not *must* or *should*. I can't tell you whether it's better to
use one Base for all your databases or a different one for each
database; it depends on the situation. You can ask the experts on the
SQLAlchemy list for advice on your specific databases. But SQLAHelper
has only one Base. So if you need a second one, you'll have to define
it outside SQLAHelper.
The purpose of SQLAHelper is to solve the most common problem, which
is sharing a single Base and Session across multiple modules,
including those written by third parties who don't know about your
application. But when you start getting multiple databases in the
application, it may get beyond what the shared Base can provide. In
that case, you can decide whether one database is general-purpose,
used by several modules and third-party libraries, while another
database is single-purpose, used only by one module. Then there's a
clear answer: use SQLAHelper's Base for the shared database, and your
own Base for the single-purpose database. For instance, the second
database may be specifically for site statistics, searching, an
external database you're consulting, etc. These would all be
single-purpose databases, which wouldn't have to be shared.
If you do have one general-purpose database and one (or more)
single-purpose database(s), you'd make the general-purpose engine the
default one. If you have two databases used equally widely, you can
choose one arbitrarily to be the default engine, or not define a
default engine at all.
The engine repository is probably the least-used part of SQLAHelper.
In the normal case, your Session is bound to an engine (or engines),
and you can always access the engine through it, so you don't need to
call get_engine(). If you bind the Base's metadata to an engine, the
same thing applies. So when would you ever call get_engine()? I don't
know, but it's possible that one module might want to define an
engine, and another module or function might want to access it by
name, rather than passing an engine object back and forth and storing
it in some ad hoc place. The engine repository provides a way to do
that.
--
Mike Orr <slugg...@gmail.com>
Chris Withers
> I haven't said much about SQLAHelper in recent months, so many users
> may not be aware that it exists. This has led to other packages that
> do approximately the same thing.
Yeah, I kinda feel the same way, and I guess I'm a bit bummed that my
perception is that people have consistently ignored mortar_rdb, which
covers exactly the same territory. More galling for me is that it's
existed since early 2010, but I wasn't allowed to open source it until
early 2011.
Still, I'd like to know what I did wrong that meant people just ignored
it. My only guess here is that mortar_rdb felt "too heavyweight", given
the (optional) mention of sqlalchemy-migrate in the usage docs? Or maybe
I didn't publicise it enough? Seriously, I'd like to know, since I feel
the same about testfixtures and mailinglogger...
Nonetheless, things SQLAHelper is missing from my perspective:
- the ability to pass parameters to the declarative base
(see
http://packages.python.org/mortar_rdb/api.html#mortar_rdb.declarative_base)
- control over the session that is stored. As I read it, you'd need to
call Session.configure in every request.
(see
http://packages.python.org/mortar_rdb/api.html#mortar_rdb.registerSession)
I'm planning quite a big chunk of work on mortar_rdb soon (to replace
sqlalchemy-migrate with alembic, pep8 the api and do a bit of weeding)
so I'd like to avoid duplicating sqlahelper if possible, but then it may
just be that sqlahelper covers the common case and mortar_rdb covers the
less common but more complicated cases)
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
Michael Bayer
On Dec 26, 2011, at 11:58 PM, Mike Orr wrote:
>
> The purpose of SQLAHelper is to solve the most common problem, which
> is sharing a single Base and Session across multiple modules,
> including those written by third parties who don't know about your
> application.
What's the use case for a Base being shared to a third party module that doesn't know about your application? This sounds like a bad idea. A Base might have any number of behaviors on it, like certain columns, naming conventions, __mapper_args__(), attribute names, that can't be anticipated by a third party library.
The only reason I can think of, is the "well you can't link via ForeignKey or relationship() with a separate base". But this is not really true, for a variety of reasons.
That said I have a feeling some libraries are already doing this but I wish we could work out a better way than using inheritance as a third party plugin point - one of Ben's key rationales for dumping Pylons altogether is that it was built on this model for extensibility.
> But when you start getting multiple databases in the
> application, it may get beyond what the shared Base can provide. In
> that case, you can decide whether one database is general-purpose,
> used by several modules and third-party libraries, while another
> database is single-purpose, used only by one module. Then there's a
> clear answer: use SQLAHelper's Base for the shared database, and your
> own Base for the single-purpose database. For instance, the second
> database may be specifically for site statistics, searching, an
> external database you're consulting, etc. These would all be
> single-purpose databases, which wouldn't have to be shared.
Why not standardize SQLAHelper on a "one by default, many if desired" model ? Also as an alternative to the getter/setter style, why not just:
helper = SQLAHelper()
# default objects: "base", "session", "engine":
default_base = helper.base
default_session = helper.session
helper.engine = create_engine(...)
# namespaces for each, "default" points to the "default":
helper.bases.default is helper.base
helper.sessions.default is helper.session
helper.engines.default is helper.engine
# alternates to the "default":
helper.bases.generic_base = Base()
helper.sessions.alternate_session = scoped_session(...)
helper.engines.alt1 = create_engine(...)
I think multiple sessions should be supported. My current app uses two simultaneously as many classes are represented in two different databases at the same time - one is the "live" database the other is "historical". An application that switches between "master" and "slave" databases at the session level needs to do this also.
Chris Withers
>
>> The purpose of SQLAHelper is to solve the most common problem, which
>> is sharing a single Base and Session across multiple modules,
>> including those written by third parties who don't know about your
>> application.
>
> What's the use case for a Base being shared to a third party module that doesn't know about your application?
I've hit the situation where I've needed to share a base across multiple
python packages, only some of which might be used in a particular project.
> A Base might have any number of behaviors on it, like certain columns, naming conventions, __mapper_args__(), attribute names, that can't be anticipated by a third party library.
Yep, that's why I built the registry of bases the way I did in mortar_rdb:
http://packages.python.org/mortar_rdb/api.html#mortar_rdb.declarative_base
> The only reason I can think of, is the "well you can't link via ForeignKey or relationship() with a separate base". But this is not really true, for a variety of reasons.
I still think it'd be great if the registry tied to the base was
actually in the metadata object. One metadata object instead of two, but
I have a feeling we've had this conversation before ;-)
> Why not standardize SQLAHelper on a "one by default, many if desired" model ? Also as an alternative to the getter/setter style, why not just:
>
> helper = SQLAHelper()
>
> # default objects: "base", "session", "engine":
>
> default_base = helper.base
> default_session = helper.session
> helper.engine = create_engine(...)
>
> # namespaces for each, "default" points to the "default":
>
> helper.bases.default is helper.base
> helper.sessions.default is helper.session
> helper.engines.default is helper.engine
I'm fairly indifferent to the above, I prefer registries myself....
> # alternates to the "default":
>
> helper.bases.generic_base = Base()
....but this doesn't solve my use case of having a base shared across
multiple packages, only some of which may be installed, and some of
which may be third party.
> I think multiple sessions should be supported. My current app uses two simultaneously as many classes are represented in two different databases at the same time - one is the "live" database the other is "historical". An application that switches between "master" and "slave" databases at the session level needs to do this also.
Yep: http://packages.python.org/mortar_rdb/api.html#mortar_rdb.getSession
...just register each ression with a different name.
Michael Bayer
On Dec 27, 2011, at 12:18 PM, Chris Withers wrote:
>
>> # alternates to the "default":
>>
>> helper.bases.generic_base = Base()
>
> ....but this doesn't solve my use case of having a base shared across multiple packages, only some of which may be installed, and some of which may be third party.
why not ? the "helper" here is just a namespace. are you referring to being able to get at the namespace from 3rd parties without any interaction? OK so you do some kind of magic global lookup thing, maybe with entrypoints, but that's not really what I'm talking about here.
>
> Yep: http://packages.python.org/mortar_rdb/api.html#mortar_rdb.getSession
>
> ...just register each ression with a different name.
what's with the camelCase + getter/setter thing?
Mike Orr
> Yeah, I kinda feel the same way, and I guess I'm a bit bummed that my
> perception is that people have consistently ignored mortar_rdb, which covers
> exactly the same territory. More galling for me is that it's existed since
> early 2010, but I wasn't allowed to open source it until early 2011.
> Still, I'd like to know what I did wrong that meant people just ignored it.
> My only guess here is that mortar_rdb felt "too heavyweight", given the
> (optional) mention of sqlalchemy-migrate in the usage docs? Or maybe I
> didn't publicise it enough? Seriously, I'd like to know, since I feel the
> same about testfixtures and mailinglogger...
I didn't know it existed. I don't go looking on PyPI for things unless
I need something in particular. And this is the kind of thing you may
not know you need.
The migration part is fine for a utility, but it's too heavy-handed
for a dependency you want everybody to use. I for one am reluctant to
use any migration utility because it's one more thing to learn and a
potential point of failure. For small changes like adding a column or
changing its type, I just do it directly. For large changes I make a
bin script.
> Nonetheless, things SQLAHelper is missing from my perspective:
>
> - the ability to pass parameters to the declarative base
> (see
> http://packages.python.org/mortar_rdb/api.html#mortar_rdb.declarative_base)
That's what ``set_base()`` is for.
> - control over the session that is stored. As I read it, you'd need to call
> Session.configure in every request.
> (see
> http://packages.python.org/mortar_rdb/api.html#mortar_rdb.registerSession)
You only need to call it at the beginning of the application, once you
have your engine.
--
Mike Orr <slugg...@gmail.com>
Mike Orr
> What's the use case for a Base being shared to a third party module that doesn't know about your application? This sounds like a bad idea. A Base might have any number of behaviors on it, like certain columns, naming conventions, __mapper_args__(), attribute names, that can't be anticipated by a third party library.
The problem is that applications and libraries are all creating their
own Bases and Sessions, and then it becomes complicated to make them
work together. So we need to figure out some common way to handle
this, so that they can all cooperate, and the application can make the
library use its Session and Base if it wants to. Because some
libraries are just defining a few tables with a name prefix, and don't
need a separate database.
> That said I have a feeling some libraries are already doing this but I wish we could work out a better way than using inheritance as a third party plugin point - one of Ben's key rationales for dumping Pylons altogether is that it was built on this model for extensibility.
Ben has also been recommending SQLAHelper.
Originally SQLAHelper was created to avoid circular imports or a
'meta' module in the model. Pylons used a 'model.meta' module to hold
the Session, Base, and engine. We wanted to avoid that in Pyramid, but
we also wanted to avoid defining the Session and Base in
model.__init__, which submodules would import, thus creating a
circular import. So SQLAHelper replaced the 'meta' module, and because
it's preinitialized so it can be imported at any time. The issue about
how it could also help in this cooperation between the application and
libraries was discovered later.
Well, I'm inclined to do whatever MikeB suggests. But what would the
module contain in this case; i.e., what would its globals be? You'd
still need a module with globals in order to be a well-known rendevous
point. 'helper = SQLAHelper()' as a variable in the application
doesn't do that. Or would 'helper' be the global in the package?
One thing SQLAHelper does is that if you call ``add_engine(engine)``
without an engine name, it becomes the default engine, and the Session
and Base.metadata are automatically bound to it. This is so that
applications with a single database only need to make one function
call to set everything up. It looks like the only way to do that in
your 'helper' structure would be a 'helper.set_default_engine()'
method? And 'helper.engines.default' would be None initially, because
the engine is not known at import time?
--
Mike Orr <slugg...@gmail.com>
Mike Orr
https://github.com/Pylons/SQLAHelper/wiki/Version-2.0-Proposal
* Does anybody object to this API?
* What use cases doesn't it cover?
* Do we need a SQLAHelper class, or can its attributes be module
globals instead?
* If we do need a class and instance, is there a better name than
'helper' or 'sqlahelper'?
--
Mike Orr <slugg...@gmail.com>
Ahmed
important.
Here is the use case I am working on now.
I am currently writing classes for User, Group, Permission to store
security data.
But since this scheme would be used in subsequent applications, I will
separate it into a separate module as a library, to use it with later
apps.
These classes are in the form of sqlalchemy Mixin classes. However,
the code includes a many-to-many relationship, which needs an
association table. Constructing a table in sqlalchemy needs to
reference the metadata of your declarative base.
user_groups = Table('user_groups', base.metadata,
Column('user_id', Integer,
ForeignKey('User.id')),
Column('group_id', Integer,
ForeignKey('Group.id'))
)
class UserMixin(object)
@declared_attr
def groups(self):
return relationship("Group", secondary=user_groups)
So you see, without the ability to refer to my application base, I
cannot add this part of to the boilerplate code in the separate
library and will have to recode it everytime I will start coding a new
app, which decreases the utility of code reuse.
I was then wondering if this is a good use case for sqlahelper.
#get my application's declarative base (thru sqlahelper)
import sqlahelper
base = sqlahelper.get_base()
user_groups = Table('user_groups', base.metadata,
Column('user_id', Integer,
ForeignKey('User.id')),
Column('group_id', Integer,
ForeignKey('Group.id'))
)
class UserMixin(object)
@declared_attr
def groups(self):
return relationship("Group", secondary=user_groups)
Ahmed
On Dec 28, 4:36 pm, Mike Orr <sluggos...@gmail.com> wrote:
Michael Bayer
On Dec 28, 2011, at 2:19 AM, Ahmed wrote:
> I guess being able to refer to the bases from third party libraries is
> important.
>
>
> user_groups = Table('user_groups', base.metadata,
> Column('user_id', Integer,
> ForeignKey('User.id')),
> Column('group_id', Integer,
> ForeignKey('Group.id'))
> )
>
> class UserMixin(object)
> @declared_attr
> def groups(self):
> return relationship("Group", secondary=user_groups)
>
>
> So you see, without the ability to refer to my application base, I
> cannot add this part of to the boilerplate code in the separate
That's fine, I'm talking more about class hierarchies that need to share the same base. Your system is based on mixins, which means they have less of an assumption about schema.
Michael Bayer
On Dec 28, 2011, at 1:36 AM, Mike Orr wrote:
> On Tue, Dec 27, 2011 at 8:05 AM, Michael Bayer <mik...@zzzcomputing.com> wrote:
>> What's the use case for a Base being shared to a third party module that doesn't know about your application? This sounds like a bad idea. A Base might have any number of behaviors on it, like certain columns, naming conventions, __mapper_args__(), attribute names, that can't be anticipated by a third party library.
>
> The problem is that applications and libraries are all creating their
> own Bases and Sessions, and then it becomes complicated to make them
> work together.
Regarding Session, if someone builds a third party library that works on some tables of it's own, and the author made the system use its own Session with no way to plug into the main transaction of the calling application, that's just bad design. If it calls session.commit() on its own yet is designed to work inline with the same database and within a series of events inside of a view, also bad design. It shouldn't assume transactional scope.
I would think if the plugin is designed for Pyramid, it would be based around ZopeSQLAlchemy, which provides a master transaction for everyone to integrate towards. Do the third party plugins at least integrate with that ?
I would say that's just something library authors would need to know how to do. They need to understand that transactions are defined by the calling application, and how a Session relates to that. It's just one click more complicated than having to know nothing at all about how transactions work. A convention here, a "how to" document of best practices for 3rd party stuff, would make it clear how these should be done. Using the transaction manager recommended with Pyramid would be best, assuming the transaction manager is capable of this.
Also, just as a note, I've never seen a 3rd party plugin that uses SQLAlchemy before, which is using a Session, defining tables, etc. Can I see one ? Are there a lot , or like half a dozen ?
Regarding Base, Base corresponds to a class hierarchy, meaning it's how your class structure is designed. SQLAHelper doesn't need to change here, it if course can expose the default "Base" to everyone, and that's great. A shop that has multiple apps of its own but are designed to work together can certainly have them all call into SQLAHelper's "Base" and that is fine.
As far as a 3rd party thing, like "download this library and now you have a standalone auth model/schema" (is there at least *one* 3rd party thing that is *not* about auth?), that probably shouldn't use the same Base, as it implies the app can't assume any kinds of conventions or behaviors on the Base class, or if it does it means my own app now can't do X, Y, or Z because it will break the 3rd party library. So I'd rather the standard practice is to share configuration and namespaces, but not class structure except for mixins. But if someone shows me an example here why they really want to share out the Base that of course can make me more aware.
The sharing of configuration can integrate with the schema of the target system by sharing MetaData(). It can integrate at the relationship() level using real class objects, or by sharing _decl_class_registry, which has always been something that could be monkeypatched, but not public...so r76d872dc77b9 now contains this feature:
from sqlalchemy.ext.declarative import declarative_base
reg = {}
Base1 = declarative_base(class_registry = reg)
Base2 = declarative_base(class_registry = reg)
class A(Base1):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
class B(Base2):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
aid = Column(Integer, ForeignKey(A.id))
as_ = relationship("A")
assert B.as_.property.mapper.class_ is A
If you additionally share a MetaData() between two bases, now those two will entirely share the same class name and table name registry.
Also note ticket 2338: http://www.sqlalchemy.org/trac/ticket/2338 , which I'm leaning towards, would add the full module path of things to the registry, so you could say:
group = relationship("myauth.classes.Group")
>
> Well, I'm inclined to do whatever MikeB suggests. But what would the
> module contain in this case; i.e., what would its globals be? You'd
> still need a module with globals in order to be a well-known rendevous
> point. 'helper = SQLAHelper()' as a variable in the application
> doesn't do that. Or would 'helper' be the global in the package?
never mind that part here, if it's a module global already that's fine.
>
> One thing SQLAHelper does is that if you call ``add_engine(engine)``
> without an engine name, it becomes the default engine, and the Session
> and Base.metadata are automatically bound to it. This is so that
> applications with a single database only need to make one function
> call to set everything up. It looks like the only way to do that in
> your 'helper' structure would be a 'helper.set_default_engine()'
> method? And 'helper.engines.default' would be None initially, because
> the engine is not known at import time?
perhaps helper.engine = some_engine would be the equivalent of add_engine(engine) - or do we think using a "setter" approach belies that nothing more than just a simple assignment is going on ?
Mike Orr
>
> I would say that's just something library authors would need to know how to do. They need to understand that transactions are defined by the calling application, and how a Session relates to that. It's just one click more complicated than having to know nothing at all about how transactions work. A convention here, a "how to" document of best practices for 3rd party stuff, would make it clear how these should be done. Using the transaction manager recommended with Pyramid would be best, assuming the transaction manager is capable of this.
>
I adjusted the proposal to use globals instead of a class. If I hear
no objections in the next couple days, I'll make a SQLAHelper 2 with
the new API, but also keeping the old API for backward compatibility.
Does anyone know of a better name than 'sqlahelper'?
>> One thing SQLAHelper does is that if you call ``add_engine(engine)``
>> without an engine name, it becomes the default engine, and the Session
>> and Base.metadata are automatically bound to it. This is so that
>> applications with a single database only need to make one function
>> call to set everything up. It looks like the only way to do that in
>> your 'helper' structure would be a 'helper.set_default_engine()'
>> method? And 'helper.engines.default' would be None initially, because
>> the engine is not known at import time?
>
> perhaps helper.engine = some_engine would be the equivalent of add_engine(engine) - or do we think using a "setter" approach belies that nothing more than just a simple assignment is going on ?
It does more than just setting an attribute, it also modifies other
variables (the default base and session).
> I would think if the plugin is designed for Pyramid, it would be based around ZopeSQLAlchemy, which provides a master transaction for everyone to integrate towards. Do the third party plugins at least integrate with that ?
Question: if multiple scoped sessions are created, each using
ZopeTransactionExtension, would they all automatically fit into the
global commit/rollback?
Can we use the same ZopeTransactionExtension *instance* for all the
scoped sessions, or would they each need a separate instance?
> Also, just as a note, I've never seen a 3rd party plugin that uses SQLAlchemy before, which is using a Session, defining tables, etc. Can I see one ? Are there a lot , or like half a dozen ?
I can't remember everywhere I've seen things. There are few libraries
using SQLAlchemy yet. I mainly wanted to avoid a future mess if
everyone did things in different ways, and then had trouble
interoperating. The API you've suggested sounds the most flexible; it
won't get into anyone's way but it's there if you want it, and it can
scale to multiple datbases, Bases, and Sessions. Libraries can be
documented to either use the default Session, a specific named
Session, or to take an attribute name specifying which Session to use.
And the user can set ``sqlahelper.sessions.library =
sqlahelper.sessions.default`` to force a library to share a session
(or base or engine) if it's not set up to do so.
--
Mike Orr <slugg...@gmail.com>
Michael Bayer
On Dec 28, 2011, at 3:28 PM, Mike Orr wrote:
>
>> I would think if the plugin is designed for Pyramid, it would be based around ZopeSQLAlchemy, which provides a master transaction for everyone to integrate towards. Do the third party plugins at least integrate with that ?
>
> Question: if multiple scoped sessions are created, each using
> ZopeTransactionExtension, would they all automatically fit into the
> global commit/rollback?
>
> Can we use the same ZopeTransactionExtension *instance* for all the
> scoped sessions, or would they each need a separate instance?
yeah I'd like to get guidance from Chris McD or Lawrence Rowe on what a multiple-session integration path might be (or if all apps should really use one Session with zope.sqlalchemy). also my apologies for screwing up it's name.
>
>> Also, just as a note, I've never seen a 3rd party plugin that uses SQLAlchemy before, which is using a Session, defining tables, etc. Can I see one ? Are there a lot , or like half a dozen ?
>
> I can't remember everywhere I've seen things. There are few libraries
> using SQLAlchemy yet. I mainly wanted to avoid a future mess if
> everyone did things in different ways, and then had trouble
> interoperating. The API you've suggested sounds the most flexible; it
> won't get into anyone's way but it's there if you want it, and it can
> scale to multiple datbases, Bases, and Sessions.
I only ask because you know it's really hard to design a system that we're going to recommend for everyone, without a good set of concrete examples of what they need. You know we've been around this block a bunch.
Wyatt Baldwin
[...]
Also note ticket 2338: http://www.sqlalchemy.org/trac/ticket/2338 , which I'm leaning towards, would add the full module path of things to the registry, so you could say:
group = relationship("myauth.classes.Group")
+1 to that.
Wyatt Baldwin
[...]
Does anyone know of a better name than 'sqlahelper'?
/bikeshedding
Mike Orr
I like 'SQLAHub'. There's a precent in Python web frameworks for 'hub'
as a database rendevous point, though it's been so many years I don't
remember where I saw it. Changing the name would have three other
benefits: (1) No need for backward compatibility code in the new
module, (2) jettison the long revision history that dates back to
'pyramid_sqla' and is irrelevent now, (3) it's a shorter name.
'sqlahelper' was pretty long as a module name in code.
--
Mike Orr <slugg...@gmail.com>
Michael Merickel
yeah I'd like to get guidance from Chris McD or Lawrence Rowe on what a multiple-session integration path might be (or if all apps should really use one Session with zope.sqlalchemy). also my apologies for screwing up it's name.
On Dec 28, 2011, at 3:28 PM, Mike Orr wrote:
>
>> I would think if the plugin is designed for Pyramid, it would be based around ZopeSQLAlchemy, which provides a master transaction for everyone to integrate towards. Do the third party plugins at least integrate with that ?
>
> Question: if multiple scoped sessions are created, each using
> ZopeTransactionExtension, would they all automatically fit into the
> global commit/rollback?
>
> Can we use the same ZopeTransactionExtension *instance* for all the
> scoped sessions, or would they each need a separate instance?
Mike Orr
>> I can't remember everywhere I've seen things. There are few libraries
>> using SQLAlchemy yet. I mainly wanted to avoid a future mess if
>> everyone did things in different ways, and then had trouble
>> interoperating. The API you've suggested sounds the most flexible; it
>> won't get into anyone's way but it's there if you want it, and it can
>> scale to multiple datbases, Bases, and Sessions.
>
> I only ask because you know it's really hard to design a system that we're going to recommend for everyone, without a good set of concrete examples of what they need. You know we've been around this block a bunch.
Yes, in general I agree. But in this case there needs to be something,
and we have to anticipate where it might go. A well-designed API is
ready for future use cases, as I think has been proven with Python and
Pyramid in several ways. But that's why I'm asking everyone what they
want for database interoperability, so that we can dig out as many of
those use cases as we can, and look for potential conflicts.
--
Mike Orr <slugg...@gmail.com>
Mike Orr
>> > Question: if multiple scoped sessions are created, each using
>> > ZopeTransactionExtension, would they all automatically fit into the
>> > global commit/rollback?
>> >
>> > Can we use the same ZopeTransactionExtension *instance* for all the
>> > scoped sessions, or would they each need a separate instance?
>>
>> yeah I'd like to get guidance from Chris McD or Lawrence Rowe on what a
>> multiple-session integration path might be (or if all apps should really use
>> one Session with zope.sqlalchemy). also my apologies for screwing up it's
>> name.
>
>
> Sorry, I'm not Chris or Lawrence, but I can tell you that the "transaction"
> package, which the ZTE and many other transaction-aware packages support,
> utilizes a threadlocal manager to which each ZopeTransactionExtension (ZTE)
> instance joins. The short answer is that if you have multiple ScopedSession
> objects that are using the ZTE, they will all be controlled by the same
> global transaction, and when pyramid_tm does transaction.commit() all of the
> sessions that are marked dirty will be committed. The ZTE supports two-phase
> transactions, but only if the ScopedSession is initialized with
> "twophase=True", thus ideally all sessions are done this way:
>
> DBSession
> = scoped_session(sessionmaker(extension=ZopeTransactionExtension(),
> twophase=True))
Is there any downside to setting twophase=True by default? Would it
make life more complex for simple applications?
Also, again, I need to know whether it's safe to share a _zte instance
between two sessionmakers. Because if it's not, the docs will have to
warn people to use it only with the default session.
--
Mike Orr <slugg...@gmail.com>
Michael Bayer
Twophase should not be on by default. On a DB like PG, it significantly alters the way transactions are done and can complicate management, as when things fail it can easily leave around prepared transactions that can lock up whole sets of tables until someone goes in and rolls them back manually. The flag shouldn't be on unless someone knows what they're doing and really wants that behavior.
>
> --
> Mike Orr <slugg...@gmail.com>
>
> --
> You received this message because you are subscribed to the Google Groups "pylons-discuss" group.
> To post to this group, send email to pylons-...@googlegroups.com.
> To unsubscribe from this group, send email to pylons-discus...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/pylons-discuss?hl=en.
>
Michael Merickel
Michael Merickel
Michael Merickel
Ahmed
> utilizes a shared global is just asking for problems when two independent
> packages want to use that library.
is not multiple bases or sessions, which is to be addressed in Mike's
V2 api. It is about multiple apps/packages accessing sqlahelper.
> Without it
> I would expect the library to dynamically define the User class based on a
> Base and Session that I supply to it, it would then return me the new User
> class using that Base.metadata, and I could track that User class within my
> app.
approach.
However, the way sqlalchemy functions now, makes it impossible to go
further with this approach if you will be using relationships. (Mixins
were a great addition to sqlalchemy 0.7 which gives us more
flexibility to using sqlalchemy as a third party lib. However, there
are still missing bits.)
I wouldn't have resorted to sqlahelper if not for two things (might be
actually one):
groups = relationship("Group", secondary=user_groups_table)
the secondary argument only accepts passing a Table object. if it
accepts a string (a table name to look up, for example, that is
evaluated at mapping time) I wouldn't have resorted to
passing my base around. The obstacle is that to build a table you have
to have your base at hand.
Table('tablename', base.metadata, Column('column'))
Otherwise putting a "complete" auth schema in a third party library
won't be feasible, unless you leave out the tables and relationships,
which kind of defeats the purpose.
I wonder if it is ok for sqlalchemy either to accept a string for the
secondary argument or to code a Table schema that gets attached later
to a base metadata. This would add more flexibility to sqlalchemy, to
be used in third party libraries.
I want to hear what Mike Bayer thinks about this.
Jasper
On Dec 29, 12:26 am, Michael Merickel <mmeri...@gmail.com> wrote:
> I hate to be posting this without an awesome solution, but I'd like someone
> to convince me that this is actually a good idea. I have not yet heard of a
> good use-case
sqlalchemy.
I don't know what it means to make sqlahelper 'standard' in pyramid,
but I,
a) prefer Classical Mapping over Declarative (as I can separate model
and data that way)
and
b) never thought session factories were a big deal.
Than again, my application may not be all that complex session-wise.
Michael Bayer
On Dec 29, 2011, at 12:20 AM, Ahmed wrote:
>> Without it
>> I would expect the library to dynamically define the User class based on a
>> Base and Session that I supply to it, it would then return me the new User
>> class using that Base.metadata, and I could track that User class within my
>> app.
>
> Before I heard about sqlahelper, I was starting to code using this
> approach.
> However, the way sqlalchemy functions now, makes it impossible to go
> further with this approach if you will be using relationships. (Mixins
> were a great addition to sqlalchemy 0.7 which gives us more
> flexibility to using sqlalchemy as a third party lib. However, there
> are still missing bits.)
>
> I wouldn't have resorted to sqlahelper if not for two things (might be
> actually one):
>
> groups = relationship("Group", secondary=user_groups_table)
>
> the secondary argument only accepts passing a Table object. if it
> accepts a string (a table name to look up, for example, that is
> evaluated at mapping time) I wouldn't have resorted to
> passing my base around. The obstacle is that to build a table you have
> to have your base at hand.
It's documented that you can pass a lambda:
relationship("Group", secondary=lambda: Base.metadata.tables['user_groups_table'])
the string is accepted there as well, just not documented (this is fixed in r58937c3f4abe and is building now):
relationship("Group", secondary="user_groups_table")
So with that a given, do we still have a strong need for sqlahelper ?
Laurent DAVERIO
> I like 'SQLAHub'. There's a precent in Python web frameworks for 'hub'
> as a database rendevous point, though it's been so many years I don't
> remember where I saw it.
I think you're referring to SQLObject, which is/was used in TurboGears.
I used it for a couple of years before switching to SQLAlchemy.
Laurent.
Ahmed
I think for my case it can be solved (I can dispense with sqlahelper)
although it is very handy. I am not sure if I speak for all use cases
though.
Ahmed
On Dec 30 2011, 2:29 am, Michael Bayer <mike...@zzzcomputing.com>
wrote:
Jonathan Vanasco
Here's my .02¢ :
- I think SqlAlchemy is the best ORM out there, and it's honestly been
the deciding factor for me going with Pylons and sticking with
Pyramid.
- I think Pyramid core really needs to have a concrete DB/ORM plugin
functionality / method / api / style. Whether you use SqlAlchemy or
ZopeDB or something else, I think Pyramid should have a standard /
recommended and documented place + support for starting up the DB
connectivity. This doesn't need to be lines of code , it could just
be a very well documented coding style / paster template.
- I think the SqlAlchemy support needs to handle one or more
databases , and both declaritive and reflective styles ( is there a
third kind )?
Pyramid is simply too free / open ended right now in regards to
database support. It has the most potential out of all the
frameworks, but its just frustrating at times.
I started with the stock sqlahelper, and have been hacking at my
integration to get more core SqlAlchemy functionality that it no
longer resembles anything in published docs... if i had to hand this
project over to someone else, i'd be worried that its so custom.
Mike Orr
> - I think SqlAlchemy is the best ORM out there, and it's honestly been
> the deciding factor for me going with Pylons and sticking with
> Pyramid.
> - I think Pyramid core really needs to have a concrete DB/ORM plugin
> functionality / method / api / style. Whether you use SqlAlchemy or
> ZopeDB or something else, I think Pyramid should have a standard /
> recommended and documented place + support for starting up the DB
> connectivity. This doesn't need to be lines of code , it could just
> be a very well documented coding style / paster template.
> - I think the SqlAlchemy support needs to handle one or more
> databases , and both declaritive and reflective styles ( is there a
> third kind )?
With the opposition to SQLAHelper, I'm tempted to just go back to meta modules.
# myapp/models/meta.py
Session = ...
engine = ...
Base = ...
The default style is whatever's in the 'alchemy' scaffold.
# myapp/models/__init__.py
DBSession = ...
Base = ...
I don't think there can be an "official" way to do multiple databases
because it depends heavily on the situation. The Akhet and SQLAHelper
manuals show a few ways to do it.
http://docs.pylonsproject.org/projects/akhet/en/latest/model_examples.html
http://readthedocs.org/docs/sqlahelper/en/latest/usage.html
Reflection is more difficult because you can't map classes to tables
until they've been reflected, and I'm not sure how it affects
declarative syntax. So some of the global code you have to put in an
init function, at minimum the table definitions and mapper calls, and
at maximum the entire declarative classes. Again the Akhet manual
discusses this.
PS. The online Akhet manual is messed up because it's showing an
unfinished development version (Akhet 2.0b1, which doesn't exist yet).
The page I linked to is unchanged from the stable version, but some of
the earlier pages are an incoherent mishmash.
--
Mike Orr <slugg...@gmail.com>
Mike Orr
repository, but I'm unmotivated to finish the docs and make a release
unless somebody motivates me.
--
Mike Orr <slugg...@gmail.com>
Siddhartha Kasivajhula
Michael Merickel
Sorry if this is a bit off-topic, but does Elixir (http://elixir.ematia.de/trac/wiki) fit in anywhere in this discussion? I'm a relative newcomer to pyramid and I've been meaning to use Elixir on top of SQLAlchemy when I do write a SQL-based app (so far my only experience with pyramid has been simple apps that didn't need a SQL layer). Seems like SQLAHelper may be for more nuanced use cases but just thought I'd ask for clarification while this email thread is active. Thanks :)-Sid
Siddhartha Kasivajhula
Don't hijack threads, this is an active mailing list and other threads will receive answers as well.
Elixir has not seen a release since 2009 and is largely the inspiration for SQLAlchemy's current declarative syntax which should be used in new projects.
--
Mike Orr
<CountV...@gmail.com> wrote:
> Sorry if this is a bit off-topic, but does Elixir
> (http://elixir.ematia.de/trac/wiki) fit in anywhere in this discussion? I'm
> a relative newcomer to pyramid and I've been meaning to use Elixir on top of
> SQLAlchemy when I do write a SQL-based app (so far my only experience with
> pyramid has been simple apps that didn't need a SQL layer). Seems like
> SQLAHelper may be for more nuanced use cases but just thought I'd ask for
> clarification while this email thread is active.
Elixir and SQLAHelper address different questions. SQLAlchemy by
itself has an API to define tables and ORM classes. SQLAHelper is a
container to organize one's engines, sessions, and declarative bases
-- it does not deal directly with ORM classes. Elixir provides a
"super-declarative" syntax for defining ORM classes, and may provide
additional "active record" features beyond that.
Elixir was released at a time before SQLAlchemy's declarative syntax
existed. MikeB added declarative after many long clamors for this
feature, but he did it in a minimal way, to offer just enough syntax
sugar without getting too magical. Elixir may go beyond that but I'm
not sure. So I don't know whether Elixir has any added value now that
declarative is in standard SQLAlchemy.
In any case, there's two different issues: "Can I use Elixir with
Pyramid?" and "Will Pyramid officially support Elixir, or include it
as a default feature?" The answer to the first question is, "Probably.
You can ask about Elixir on this list or look in the Pyramid Cookbook
for user-contributed articles on it." The answer to the second
question is, "No, it's a high-level library, and Pyramid includes only
medium-level libraries. E.g., there's built-in support for SQLAlchemy,
but not for any particular form library."
SQLAHelper is kind of between official and unofficial support. Whether
it should be an official default or not, is what people in this thread
have been discussing.
--
Mike Orr <slugg...@gmail.com>
Michael Bayer
On Jan 3, 2012, at 2:32 PM, Mike Orr wrote:
>
> Reflection is more difficult because you can't map classes to tables
> until they've been reflected, and I'm not sure how it affects
> declarative syntax.
I've recently worked out a way to do this:
http://www.sqlalchemy.org/trac/wiki/UsageRecipes/DeclarativeReflectedBase
and in 0.7.5 or #2356, it will be slightly easier still:
http://www.sqlalchemy.org/trac/ticket/2356
> So some of the global code you have to put in an
> init function, at minimum the table definitions and mapper calls, and
> at maximum the entire declarative classes. Again the Akhet manual
> discusses this.
but yes the reflected approach still needs some point at which you say, "reflect and map !" even if declarative is used.
Jonathan Vanasco
thanks for the pointers. wish i had checked in on this thread before
coding last night :[
I already updated some old pylons code (and sqlalchemy .4 version) to
work under pyramid and take advantage of zope
i tossed it online here : https://gist.github.com/1686834
it /only/ works for reflected tables. perhaps there's a way to
integrate with the declarative stuff that mike has been supporting.
it does the following:
- uses a dict in the package as a registry of "wrapped engines"
-- a wrapped engine is a light object that just stores the following
sqlachemy objects: engine, sessionmaker (and the params used for it),
and session
- stores returns the correct session via looking up the name in the
engine registry. e.g. dbSession('read')
- traverses a package looking for objects that inherit from
"ReflectedTable"
- stores too much info for debugging
-- the 'Table' data is stashed into the reflected classes
-- EngineWrapper doesn't need half of the items in it
i'll likely make this more proper in the future, but its working right
now and I can get back to migrating a project.
Daniel Holth
Mike Orr
> I do not understand why I would want to share a declarative_base() between
> packages.
One reason is so you can call Base.metadata.create_all() to create all
tables, without having to go into each individual package or keep
track of what tables they have. (Of course, you'd have to ensure the
table names don't overlap, usually by the library defaulting to a
prefix that others are unlikely to use.)
But as I've said in a few places, I've gone back to 'meta' modules as
in Pylons, and that's what I'm putting first in the Pyramid/Pylons
guide. So the Sessions and Bases would not be coordinated unless the
application writer does it.
--
Mike Orr <slugg...@gmail.com>
Chris Withers
> I do not understand why I would want to share a declarative_base()
> between packages.
A table defined in one package may have foreign key relationships to a
table defined in another package.
I often hit this with authentication packages where other packages what
you reference users.
cheers,
Chris
--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk
Daniel Holth
DBSession = scoped_session(sessionmaker())
engine = create_engine(...)
DBSession.configure(bind=engine)
connection = DBSession.connection()
for base as (all declarative_base instances in dependency order):
base.metadata.create_all(connection)
# base.metadata.bind = no thanks
Mike Orr
> That's my point, foreign keys -do- work without problems even when you have
> more than one declarative base. That is the only feature that I mean to
> question. So my preferred SQLAlchemy style goes like so, with multiple
> declarative_base() and never-bound metadata. It might be helpful to remember
> that if you are using the ORM without declarative then you could have zero
> declarative_base() instances.
Would anyone like to take over maintaining SQLAHelper? The version 2
API is finished in the repository but hasn't been released, because
I'm unmotivated to spend any more time in these
use-a-shared-session-container-or-not controversies. The version 2
API expands the number of bases from one to an arbitrary number, and
replaces the getters/setters with attribute access. (Shims for the
version 1 API are retained for bacward compatibility.) I've gone back
to just using a 'meta' module in my own programs.
So if somebody wants to use SQLAHelper, I'd say use the development
version. If they want to maintain it, release that version.
--
Mike Orr <slugg...@gmail.com>