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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
--
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.
