Thank you very much. That worked :)
On Wednesday, 7 September 2016 14:56:25 UTC+1, Mike Bayer wrote:
>
>
>
> On 09/07/2016 05:08 AM, bsdz wrote:
> > No worries and thanks for the suggestions/advice so far.
> >
> > Tbh I tried the mixin approach first as described in the docs but had
> > problems when trying to query anything on the 2nd class.
> >
> > This is a boiled down version of what I tried:
>
> the result doesn't make sense because it is querying Country just fine,
> then seems to think User is not a declarative class. This seems like
> you just have the wrong symbol being referred to somewhere (in an import
> or similar).
>
> Here's a demo of what I had in mind:
>
> from sqlalchemy import *
> from sqlalchemy.orm import *
> from sqlalchemy.ext.declarative import declarative_base,
> DeferredReflection
>
> e = create_engine("sqlite://", echo=True)
> e.execute("""
> create table a (id integer primary key)
> """)
> e.execute("""
> create table b (id integer primary key)
> """)
>
> # note no 'c'
>
>
> Base = declarative_base()
>
>
> class Base(DeferredReflection, Base):
> __abstract__ = True
>
>
> class Prod(Base):
> __abstract__ = True
>
>
> class Dev(Base):
> __abstract__ = True
>
>
> class A(Prod, Dev):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
>
>
> class B(Prod, Dev):
> __tablename__ = 'b'
> id = Column(Integer, primary_key=True)
>
>
> class C(Dev):
> __tablename__ = 'c'
> id = Column(Integer, primary_key=True)
>
>
> Prod.prepare(e)
>
>
> s = Session(e)
> s.add_all([A(), B(), A(), B()])
> s.commit()
>
> print(s.query(A).all())
> print(s.query(B).all())
>
>
>
>
>
> >
> > |
> > import sqlalchemy as sa
> > from sqlalchemy.orm import sessionmaker, relationship
> > from sqlalchemy.ext.declarative import declarative_base,
> DeferredReflection
> >
> > e = sa.create_engine("sqlite://", echo=True)
> >
> > e.execute("""
> > create table country (
> > country_id integer primary key
> > )
> > """)
> >
> > e.execute("""
> > create table user (
> > user_id integer primary key
> > )
> > """)
> >
> > Base = declarative_base(cls=DeferredReflection)
> > Base2 = declarative_base(cls=DeferredReflection)
> >
> > # exists in PROD & DEV
> > class Country(Base):
> > __tablename__ = 'country'
> >
> > # exists only in PROD
> > class User(Base2):
> > __tablename__ = 'user'
> >
> > country_id = sa.Column(sa.Integer,
> > sa.ForeignKey('country.country_id'))
> > country = relationship("Country", uselist=False)
> >
> > class MyModel(object):
> > def __init__(self, env):
> > self._engine = e
> >
> > # Base.metadata.bind = self._engine - binding at this level
> > didn't help :(
> >
> > if env == "DEV":
> > Base2.metadata.bind = self._engine
> > Base2.prepare(self._engine)
> >
> > Base.metadata.bind = self._engine
> > Base.prepare(self._engine)
> >
> > def create_session_maker(self):
> > return sessionmaker(bind=self._engine)
> >
> > # This code is run in another module.
> > mymodel = MyModel("PROD")
> > Session = mymodel.create_session_maker()
> > session = Session()
> >
> > session.query(Country).count()
> > session.query(User).count()
> > |
> >
> > And the result:
> >
> > |
> >
> > 2016-09-07 10:03:43,889 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test plain returns' AS VARCHAR(60)) AS anon_1
> > 2016-09-07 10:03:43,891 INFO sqlalchemy.engine.base.Engine ()
> > 2016-09-07 10:03:43,892 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
> > 2016-09-07 10:03:43,893 INFO sqlalchemy.engine.base.Engine ()
> > 2016-09-07 10:03:43,894 INFO sqlalchemy.engine.base.Engine
> > create table country (
> > country_id integer primary key
> > )
> >
> > 2016-09-07 10:03:43,895 INFO sqlalchemy.engine.base.Engine ()
> > 2016-09-07 10:03:43,896 INFO sqlalchemy.engine.base.Engine COMMIT
> > 2016-09-07 10:03:43,897 INFO sqlalchemy.engine.base.Engine
> > create table user (
> > user_id integer primary key
> > )
> >
> > 2016-09-07 10:03:43,897 INFO sqlalchemy.engine.base.Engine ()
> > 2016-09-07 10:03:43,898 INFO sqlalchemy.engine.base.Engine COMMIT
> > 2016-09-07 10:03:43,902 INFO sqlalchemy.engine.base.Engine PRAGMA
> table_info("country")
> > 2016-09-07 10:03:43,903 INFO sqlalchemy.engine.base.Engine ()
> > 2016-09-07 10:03:43,904 INFO sqlalchemy.engine.base.Engine PRAGMA
> foreign_key_list("country")
> > 2016-09-07 10:03:43,905 INFO sqlalchemy.engine.base.Engine ()
> > 2016-09-07 10:03:43,906 INFO sqlalchemy.engine.base.Engine PRAGMA
> index_list("country")
> > 2016-09-07 10:03:43,906 INFO sqlalchemy.engine.base.Engine ()
> > 2016-09-07 10:03:43,910 INFO sqlalchemy.engine.base.Engine BEGIN
> (implicit)
> > 2016-09-07 10:03:43,912 INFO sqlalchemy.engine.base.Engine SELECT
> count(*) AS count_1
> > FROM (SELECT country.country_id AS country_country_id
> > FROM country) AS anon_1
> > 2016-09-07 10:03:43,912 INFO sqlalchemy.engine.base.Engine ()
> >
> > InvalidRequestError: SQL expression, column, or mapped entity expected -
> got '<class '__main__.User'>'
> >
> > |
> >
> > I'm pretty sure I also tried the abstract approach with similar results.
> > Perhaps I'm binding incorrectly?
> >
> > On Tuesday, 6 September 2016 18:37:14 UTC+1, Mike Bayer wrote:
> >
> >
> >
> > On 09/06/2016 12:50 PM, bsdz wrote:
> > > Thanks for your reply.
> > >
> > > I think you misread my code. The method "remove" does exist. It's
> > a method of MetaData and not tables. See
> >
> http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#sqlalchemy.schema.MetaData
>
> > <
> http://docs.sqlalchemy.org/en/rel_1_0/core/metadata.html#sqlalchemy.schema.MetaData>.
>
>
> >
> >
> > I did misread.
> >
> > >
> > > I'm not sure about automap. I'm not keen on reflecting all the
> > tables in my database and prefer fine grained control.
> >
> >
> > metadata.reflect() allows for limiting the set of tables using
> "only":
> >
> >
> http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=metadata.reflect#sqlalchemy.schema.MetaData.reflect.params.only
>
> > <
> http://docs.sqlalchemy.org/en/latest/core/metadata.html?highlight=metadata.reflect#sqlalchemy.schema.MetaData.reflect.params.only>
>
>
> >
> >
> >
> > > Furthermore, I would like to keep deferred reflection as it
> allows
> > one to choose an environment before binding to an engine.
> >
> > automap and deferred reflection kind of do the same thing, it's just
> > the
> > former does a lot more.
> >
> > in any case, launching prepare() from targeted mixins w/
> > DeferredReflection should work if I'm reading the code correctly
> (which
> > we've already demonstrated is challenging me today).
> >
> >
> >
> >
>
>
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
