[sqlalchemy] Re: Polymorphic self referential foreign key in sqlalchemy
Perfect, thanks!
On Monday, 20 July 2015 10:49:26 UTC-4, Douglas Russell wrote:
Apologies for cross-posting this, but I realised that this might be a
better place to get an answer than this Stackoverflow question
http://stackoverflow.com/questions/31393824/polymorphic-self-referential-foreign-key-in-sqlalchemy
that I asked a week ago.
I am trying to resolve a problem with a self-referential table with Joined
Table Inheritance where there is a foreign key linking the inheritance
relationships, but then also a case where a class has an additional
reference to an instance of its parent. Best to go with a simplified
example:
Class B inherits from Class A. Class B is linked to Class A by the id
column through the Foreign Key in Class B. Class B also has a column (a_id)
which references Class A which has nothing to do with the inheritance.
from sqlalchemy import Column, Integer,ForeignKey, create_enginefrom
sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.orm import
sessionmaker, relationship, backref
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
satype = Column(String(50))
__mapper_args__ = {
'polymorphic_identity': 'a',
'polymorphic_on': satype
}
id = Column(Integer, primary_key=True)
class B(A):
__tablename__ = 'b'
id = Column(Integer, ForeignKey('a.id'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': 'b'
}
a_id = Column(Integer, ForeignKey('a.id'))
a = relationship('A', backref='b')
engine = create_engine('sqlite:///:memory:',
echo=True)Base.metadata.create_all(engine)Session = sessionmaker(bind=engine)
session = Session()
As per the documentation
http://docs.sqlalchemy.org/en/latest/orm/relationship_persistence.html#rows-that-point-to-themselves-mutually-dependent-rows,
I would resolve the case where there are multiple ForeignKeys between
tables by explicitly specifying in the relationship which was to be used.
class B(A):
__tablename__ = 'b'
id = Column(Integer, ForeignKey('a.id'), primary_key=True)
__mapper_args__ = {
'polymorphic_identity': 'b'
}
a_id = Column(Integer, ForeignKey('a.id'))
# I know the primaryjoin is no longer needed in SA = 0.8
a = relationship('A', backref='b', foreign_keys=[a_id],
primaryjoin=a_id==A.id)
I think the problem is that I don't seem to be able to figure out how to
do the same for the polymorphic column id as I am not explicitly defining
that relationship.
Cheers,
Douglas
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] marking an object read-only / recursive expunge?
So my code above is just completely wrong. This code actually does what one expects: def recursive_expunge(obj, dbSession): def _recursive_expunge(_obj): _instance_state = sqlalchemy.inspection.inspect(_obj) _mapper = _instance_state.mapper try: dbSession.expunge(_obj) # print expunge | %s % _obj except sqlalchemy.orm.exc.UnmappedInstanceError: # print sqlalchemy.orm.exc.UnmappedInstanceError | %s % _obj pass except sqlalchemy.exc.InvalidRequestError: # print sqlalchemy.exc.UnmappedInstanceError | %s % _obj pass if _mapper: # _unloaded = [(_name, _rel) for (_name, _rel) in _mapper.relationships.items() if _name in _instance_state.unloaded] _loaded_rels = [i for i in _mapper.relationships.items() if i[0] not in _instance_state.unloaded] for (_name, _rel) in _loaded_rels: _loaded_rel_data = getattr(_obj, _name) if _loaded_rel_data: if not _rel.uselist: _recursive_expunge(_loaded_rel_data) else: for _i in _loaded_rel_data: _recursive_expunge(_i) _recursive_expunge(obj) -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] question about `sqlalchemy.orm.mapper.Mapper` and objects
Yeah, no error. I'll guess that: * My code isn't doing what I intended * but *SqlAlchemy isn't raising an error So I can work with that. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Disabling implicit lazy loading
On 7/23/15 10:09 AM, Mike Bayer wrote: On 7/23/15 1:24 AM, Yegor Roganov wrote: Hi all! Is there a way to disable implicit loading of relationships? For example, I want an exception to be thrown if I try to access 'address.user' unless user was explicitly loaded via options address = query(Address).options(joinedload(Address.user)).filter_by(id=id).first(); address.user # OK address = query(Address).get(id); address.user # should throw At first I thought that `noload` option is what I need, but it seems it disables event explicit loading. noload is how you'd disable implicit loading. As far as throwing on a lazyload, the easiest way is just to detach the objects from their parent Session so they no longer have any connectivity using session.expunge(object), but then you're no longer in the session. Otherwise, it seems the problem you are actually trying to solve is raising on unexpected SQL. lazy loading of relationships is not the only thing that goes on, there are loads of unloaded columns, columns that had server defaults emitted on the last flush, loads of joined-inheritance rows, all kinds. this is why the best approach is to just do real profiling of your applications using SQL logging, or perhaps using SQL events like before_execute() / before_cursor_execute() so that you can build yourself a with assert_no_sql(session): -style context manager for critical blocks that should have no SQL emitted. Guessing that's not what you want. Feel free to write your own NoLoader that just raises, example: whoops. Let's try that again, this one actually works: from sqlalchemy.orm import properties from sqlalchemy.orm import strategies from sqlalchemy.orm import state @properties.RelationshipProperty.strategy_for(lazy=raise) class RaiseLoader(strategies.NoLoader): note: this is *very SQLAlchemy 1.0 specific*!! it will need to be reviewed for 1.1 def create_row_processor( self, context, path, loadopt, mapper, result, adapter, populators): def invoke_no_load(state, passive): raise Exception(boom) set_lazy_callable = state.InstanceState.\ _instance_level_callable_processor( mapper.class_manager, invoke_no_load, self.key ) populators[new].append((self.key, set_lazy_callable)) -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Disabling implicit lazy loading
On 7/23/15 1:24 AM, Yegor Roganov wrote:
Hi all!
Is there a way to disable implicit loading of relationships?
For example, I want an exception to be thrown if I try to access
'address.user' unless user was explicitly loaded via options
address =
query(Address).options(joinedload(Address.user)).filter_by(id=id).first();
address.user # OK
address = query(Address).get(id); address.user # should throw
At first I thought that `noload` option is what I need, but it seems
it disables event explicit loading.
noload is how you'd disable implicit loading. As far as throwing on a
lazyload, the easiest way is just to detach the objects from their
parent Session so they no longer have any connectivity using
session.expunge(object), but then you're no longer in the session.
Otherwise, it seems the problem you are actually trying to solve is
raising on unexpected SQL. lazy loading of relationships is not the
only thing that goes on, there are loads of unloaded columns, columns
that had server defaults emitted on the last flush, loads of
joined-inheritance rows, all kinds. this is why the best approach is
to just do real profiling of your applications using SQL logging, or
perhaps using SQL events like before_execute() /
before_cursor_execute() so that you can build yourself a with
assert_no_sql(session): -style context manager for critical blocks that
should have no SQL emitted.
Guessing that's not what you want. Feel free to write your own
NoLoader that just raises, example:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import properties
from sqlalchemy.orm import strategies
@properties.RelationshipProperty.strategy_for(lazy=raise)
class RaiseLoader(strategies.NoLoader):
note: this is *very SQLAlchemy 1.0 specific*!! it will need to
be reviewed for 1.1
def create_row_processor(
self, context, path, loadopt, mapper,
result, adapter, populators):
def invoke_no_load(state, dict_, row):
raise Exception(boom)
populators[new].append((self.key, invoke_no_load))
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
bs = relationship(B, lazy=raise)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
a_id = Column(ForeignKey('a.id'))
e = create_engine(sqlite://, echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add(A(bs=[B(), B()]))
s.commit()
a1 = s.query(A).first()
a1.bs # boom
send me a PR that includes tests and I can consider this for 1.1.
If you are intrested why I need this kind of functionality, that's
because I found it hard to manage which relationships were loaded and
which not, and it may result in dozens of unwanted DB queries.
--
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+unsubscr...@googlegroups.com
mailto:sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com
mailto:sqlalchemy@googlegroups.com.
Visit this group at http://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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] natural vs surrogate keys for user groups?
Hi folks, I'm starting a green field project and one component is a stand alone auth micro service. It will have one job only, log people in and set their group list. After that, the group list and user id will be going in jwt tokens and will be used in subsequent apps as fields for the remote user in the wsgi env. This has me wondering whether I should be using natural primary keys for the group id to further simplify things, it seems like it would make life easier but as I have only, to this day, used surrogate integer keys, I don't really know the downsides of them. Would love to hear feedback from anyone on this or similar issues. thanks! (And it's nice to be getting back to SQAlchemy from Angular land, woot!) Iain -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] best books on DB design for sqlalchemy users?
I feel like I should really take up my db game for an upcoming set of projects, and am wondering if there are any real standout books on db design that fit well with the design philosophy of SQLA. Recos much appreciated! thanks Iain -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Many-to-Many Table
On 7/23/15 2:18 PM, Rich Shepard wrote:
Originally posted here on June 4th, but no one responded. I'm now
back on
this project and this should be the last table I need to add to the
schema.
I would appreciate your review of the below class and whether it is
good to
go or needs your modifications.
Section 2.1.15 in the 1.0.8 manual describes building an association
table
for many-to-many relationships. My application has a table that
associates
multiple locations with multiple types of data collected. That is, each
location can have multiple types of data collected, and each data type
can
be collected at multiple locations. There are other attributes associated
with each row. Will the following table declaration work? Or, do I
separate
site and param into a separate table from the other columns?
conceptually not a big deal but API-wise has many mistakes. Sequences
don't work with unicode, there is no value parameter, the table has no
primary key.
--
class Monitoring(Base):
__table_name__ = 'monitoring'
permit_nbr = Column(Unicode(24), ForeignKey('permits.nbr'))
permit = relationship(Permits, back_populates = 'locations')
data_type = Column(Unicode(16), CheckConstraint(data_type IN
('surface \
water', 'ground water', 'air', 'benthos', 'fish', 'microbes', \
'physical','weather')))
site = Column(Unicode(12), Sequence('location_seq'), nullable =
False, \
unique = True, ForeignKen('locations.site_id'))
param = Column(Unicode(24), nullable = False, unique = True,
ForeignKey(\
'conditions.param_name'))
mcl = Column(Float)
monit_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(monitor_freq IN ('Hour','Shift','Day','Week','2x
month',\
'Month','Quarter','Semi-Annual','Annual')))
rpt_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(rpt_freq IN ('Hour','Shift','Day','Week','2x
month',\
'Month','Quarter','Semi-Annual','Annual')))
start_date = Column(Date, value = today, nullable = False)
end_date = Column(Date)
site = relationship(Locations, back_populates = 'monitoring')
param = relationship(Conditions, back_populates = 'monitoring')
-
Rich
--
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+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Many-to-Many Table
On Thu, 23 Jul 2015, Mike Bayer wrote: conceptually not a big deal but API-wise has many mistakes. Sequences don't work with unicode, there is no value parameter, the table has no primary key. Mike, True, I don't want a sequence but the location ID, there needs to be a value column, and others should be moved into the parameter table. I thought a many-to-many table did not need a primary key because it would be accessed by (in this case) location or paramter. Thanks, Rich
Re: [sqlalchemy] question about `sqlalchemy.orm.mapper.Mapper` and objects
On 7/23/15 3:04 PM, Jonathan Vanasco wrote: so does dbSession.expunge(relationship) when `relationship` is an item from `sqlalchemy.inspection.inspect(obj).mapper.relationships.values()` somehow expunge the object-specific relationship, or all relationships ? that should raise an error, though I wouldn't be surprised if that ends up in an unchecked AttributeError right now. expunge is for instances, not mappings. -- 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+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] question about `sqlalchemy.orm.mapper.Mapper` and objects
This is an extension of my question about recursive object expunging (https://groups.google.com/forum/#!topic/sqlalchemy/lUhCDfkPc9k) Is the mapper that is accessed via `sqlalchemy.inspection.inspect(obj).mapper` and stored in `object.__mapper__` specific to the instance? I thought it was the global mapper, but looking at how it behaves as I use it... it seems local to the object. -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] question about `sqlalchemy.orm.mapper.Mapper` and objects
On 7/23/15 2:28 PM, Jonathan Vanasco wrote: This is an extension of my question about recursive object expunging (https://groups.google.com/forum/#!topic/sqlalchemy/lUhCDfkPc9k) Is the mapper that is accessed via `sqlalchemy.inspection.inspect(obj).mapper` and stored in `object.__mapper__` specific to the instance? I thought it was the global mapper, but looking at how it behaves as I use it... it seems local to the object. there's no object-local mappers. -- 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+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Many-to-Many Table
Originally posted here on June 4th, but no one responded. I'm now back on
this project and this should be the last table I need to add to the schema.
I would appreciate your review of the below class and whether it is good to
go or needs your modifications.
Section 2.1.15 in the 1.0.8 manual describes building an association table
for many-to-many relationships. My application has a table that associates
multiple locations with multiple types of data collected. That is, each
location can have multiple types of data collected, and each data type can
be collected at multiple locations. There are other attributes associated
with each row. Will the following table declaration work? Or, do I separate
site and param into a separate table from the other columns?
--
class Monitoring(Base):
__table_name__ = 'monitoring'
permit_nbr = Column(Unicode(24), ForeignKey('permits.nbr'))
permit = relationship(Permits, back_populates = 'locations')
data_type = Column(Unicode(16), CheckConstraint(data_type IN ('surface \
water', 'ground water', 'air', 'benthos', 'fish', 'microbes', \
'physical','weather')))
site = Column(Unicode(12), Sequence('location_seq'), nullable = False, \
unique = True, ForeignKen('locations.site_id'))
param = Column(Unicode(24), nullable = False, unique = True, ForeignKey(\
'conditions.param_name'))
mcl = Column(Float)
monit_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(monitor_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
rpt_freq = Column(Unicode(12), value = 'Month', nullable = False, \
CheckConstraint(rpt_freq IN ('Hour','Shift','Day','Week','2x month',\
'Month','Quarter','Semi-Annual','Annual')))
start_date = Column(Date, value = today, nullable = False)
end_date = Column(Date)
site = relationship(Locations, back_populates = 'monitoring')
param = relationship(Conditions, back_populates = 'monitoring')
-
Rich
Re: [sqlalchemy] best books on DB design for sqlalchemy users?
On Thu, 23 Jul 2015, Iain Duncan wrote: I feel like I should really take up my db game for an upcoming set of projects, and am wondering if there are any real standout books on db design that fit well with the design philosophy of SQLA. Recos much appreciated! Iain, Read Joe Celko's books, starting with the latest edition of SQL for Smarties. I read his columns in Database Advisor and other magazines in the 1980s and have read and used almost all his books. You can't go wrong taking the time to do it correctly from the gitgo. Rich
Re: [sqlalchemy] best books on DB design for sqlalchemy users?
not quite sqlalchemy related, but one of the best readings i've had about sql was SQL Anti-Patterns: https://pragprog.com/book/bksqla/sql-antipatterns it is not about how to do sql right, it's about not to do certain types of sql, lol. On 07/23/2015 01:35 PM, Rich Shepard wrote: On Thu, 23 Jul 2015, Iain Duncan wrote: I feel like I should really take up my db game for an upcoming set of projects, and am wondering if there are any real standout books on db design that fit well with the design philosophy of SQLA. Recos much appreciated! Iain, Read Joe Celko's books, starting with the latest edition of SQL for Smarties. I read his columns in Database Advisor and other magazines in the 1980s and have read and used almost all his books. You can't go wrong taking the time to do it correctly from the gitgo. Rich -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
