Best practice for binding the engine

[Bao Niu]

From reading the documentation I learned that you can either bind an engine to a session:

>>>engine = create_engine('sqlite:///{}'.format(dbPath), echo=False)

>>>Session = sessionmaker(bind=engine)

 or to a declarative_base:

>>>engine = create_engine('sqlite:///{}'.format(dbPath), echo=False)

>>>Sqlalchemy_base = declarative_base(engine)

Is there a best practice like always binding to a session?

I currently bind the engine to both:

>>>engine = create_engine('sqlite:///{}'.format(dbPath), echo=False)

>>>Session = sessionmaker(bind=engine)

>>>Sqlalchemy_base = declarative_base(engine)

From running my codes I didn't encounter any problem. But I wonder how SqlAlchemy resolves the conflict internally if there are two bindings? Is there some potential problems down the road if I kept using this manner? Thanks.

[Bao Niu]

Ok, let me try rephrasing my question.

Is binding an engine/connection simultaneously to a Session and a Table considered bad practice? I've looked up the documentation but not sufficiently confident about this. There seems no definite/official answer to this. Could some experienced users help give a definite clue here? Thanks.

[Simon King]

The order of resolution is described here:

http://docs.sqlalchemy.org/en/rel_0_9/orm/session.html#sqlalchemy.orm.session.Session.get_bind

Binds on the session take priority over binds on metadata (passing an
engine to declarative_base binds the metadata for that base class).

As far as best practice goes, I would say that it depends on the kind
of program you are writing. Tables (and mapped classes and so on) tend
to be created at module scope. Binding an engine at that point means
that you need to know the database credentials at the time your module
is imported. This is often fine for quick scripts, but can become
annoying when structuring a larger application. For a larger
application, it is probably better to define the structure of your
data model separately from an actual connection to the database.

Binding at the session level gives you more flexibility, as you can
decide on a per-session basis which engine you want to use (for
example if you had different engines for read and write access).

Hope that helps,

Simon

> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Bao Niu]

Yes it is clearly documented there, I need to refine my reading skills:) thxs Simon! Your explanation makes it much easier to understand for a newbie like me.

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/QpkScWlfWx8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Bao Niu]

Hi Simon,

I've got a follow-up question regarding the best practice. You seem to favour binding engine/connection to the Session over to the Metadata, if I understood correctly. However, if my application uses reflection, i.e., autoload=True, that would require that the Table being aware of which database it is referring to. In such circumstance, there seems to be no other choice but binging the engine/connection to Metadata. Do you see any potential problem in my scenario? 

And, in this scenario can I safely remove any bindings to the Session, because I can trust the bindings with the Tables?

Many thanks!

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/QpkScWlfWx8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.

[Simon King]

It is possible to trigger the reflection to happen later - see the
DeferredReflection class at:

http://docs.sqlalchemy.org/en/rel_0_9/orm/extensions/declarative.html#using-reflection-with-declarative

However, if you're happy doing things the way you are at the moment,
there's no real reason to switch. And you can trust that the Session
will use the bindings established on the metadata, if it is not
explicitly bound itself.

Simon