[sqlalchemy] Re: Complex Constraints in Many to Many relationships
If I'm understanding correctly... You're on the right track. I'd use a composite primary key on |team_person|, consisting of foreign keys from |person| and |team|, and another composite key (or unique index) on the |team| to |tournament| table. This lets the database do all the work. -Derek On Wednesday, September 4, 2019 at 4:16:02 PM UTC-5, Michael P. McDonnell wrote: > > Hey - > I'm again at a loss of what to google, and as this will ultimately need to > be represented in some fashion in sqlalchemy, I figured this is a great > place to start: > > I have a |person| table and a |team| table with a many to many table in > between |team_person|. > Simple enough! > > Now - to make it fun. > |team| has a relationship to |tournament| > How can I prevent a user from joining more than 1 team in a given > tournament? > > I thought about adding a 3rd column to my M2M table, > (team_tournament_person), but that could still fail because it could be a > team from tournament x, tournament y's ID and a Person Q's ID. > > So any suggestions on what I should be googling, and then how to implement > in SA would be hugely appreciated! > > Thanks! > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.com.
[sqlalchemy] Re: Include total count in query results with limit and offset
Thanks, that does make more sense.
I ended up writing a little wrapper that allows me to create the query in
my service including the offset/limit and continue to add additional
filters, etc. when it's run.
class PagedQuery:
def __init__(self, query: orm.Query):
self.logger: logging.Logger = logging.getLogger(self.__module__)
self.query: orm.Query = query
self._limit: int = None
self._offset: int = None
def __getattr__(self, item: Text):
try:
return self.__getattribute__(item)
except AttributeError:
def call(*args, **kwargs):
self.query = getattr(self.query, item)(*args, **kwargs)
return self
return call
def limit(self, limit: int):
self._limit = limit
return self
def offset(self, offset: int):
self._offset = offset
return self
def all(self):
count = self.query.count()
self.logger.debug(f"Paging results, count={count},
offset={self._offset}, limit={self._limit}")
data = self.query \
.limit(self._limit) \
.offset(self._offset) \
.all()
return {
'total_count': count,
'data':data
}
On Monday, April 1, 2019 at 11:10:52 AM UTC-5, Derek Lambert wrote:
>
> Is it possible to return a query's results as a dictionary containing the
> total count and a limited portion of the results?
>
> query = session.query(Item)
>
> results = {
> 'total_count': query.count(),
> 'data':query.offset(0).limit(50).all(),
> }
>
>
> assert session.query(*magic_here*).*more_magic*().offset(0).limit(50).all()
> == results
>
> I feel like I accidentally hit on something like this a while back, but
> can't recall how or find any similar examples.
>
> Thanks,
> Derek
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Value deleter in examples/vertical/dictlike-polymorphic.py
I've implemented some property objects based on the dictlike-polymorphic.py example. I'm writing unit tests for my code and noticed the value deleter is attempting to call an undefined method _set_value. What was intended here? I've got some jankey code I'm using to delete properties from the relationship, curious if there's something cleaner I'm overlooking. Thanks, Derek -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Missing synonyms with multi-inheritance
Thanks. I'll use cascading as a workaround for the time being.
On Friday, October 19, 2018 at 3:55:37 PM UTC-5, Mike Bayer wrote:
>
> it's a recent regression as of 1.2.12:
>
> https://bitbucket.org/zzzeek/sqlalchemy/issues/4350/synonym-single-inheritance-mixins
>
>
> will fix
>
> On Fri, Oct 19, 2018 at 4:38 PM Mike Bayer > wrote:
> >
> > On Fri, Oct 19, 2018 at 2:30 PM Derek Lambert > wrote:
> > >
> > > I'm replying to my original post since it's related.
> > >
> > > I'm still seeing missing synonyms on child classes when they are
> defined in a mixin imported on a parent.
> > >
> > >
> > > import sqlalchemy as sa
> > > from sqlalchemy import orm
> > > from sqlalchemy.ext.declarative import declarative_base, declared_attr
> > >
> > >
> > > Base = declarative_base()
> > >
> > >
> > > class DirectoryEntry(Base):
> > > guid = sa.Column(sa.Integer, primary_key=True)
> > > _type = sa.Column(sa.String, nullable=False)
> > > distinguished_name = sa.Column(sa.String)
> > > name = sa.Column(sa.String)
> > >
> > > __tablename__ = 'directory_entry'
> > > __mapper_args__ = {
> > > 'polymorphic_on': _type,
> > > 'polymorphic_identity': 'directory_entry',
> > > }
> > >
> > >
> > > class DirectoryGroup(DirectoryEntry):
> > > __mapper_args__ = {
> > > 'polymorphic_identity': 'directory_group',
> > > }
> > >
> > >
> > > class ActiveDirectoryEntry:
> > > @declared_attr
> > > def distinguishedName(self):
> > > return orm.synonym('distinguished_name')
> > >
> > >
> > > class ActiveDirectoryGroup(ActiveDirectoryEntry, DirectoryGroup):
> > > __mapper_args__ = {
> > > 'polymorphic_identity': 'active_directory_group'
> > > }
> > >
> > >
> > > class AnotherChild(ActiveDirectoryGroup):
> > > __mapper_args__ = {
> > > 'polymorphic_identity': 'another_child'
> > > }
> > >
> > >
> > > engine =
> sa.create_engine('postgresql+psycopg2://postgres@localhost/postgres',
> echo=True, isolation_level='AUTOCOMMIT')
> > >
> > > engine.execute('DROP DATABASE IF EXISTS inherit_test')
> > > engine.execute('CREATE DATABASE inherit_test')
> > >
> > > engine =
> sa.create_engine('postgresql+psycopg2://postgres@localhost/inherit_test',
> echo=True)
> > >
> > > Base.metadata.create_all(engine)
> > >
> > > session = orm.sessionmaker(bind=engine)()
> > > group = ActiveDirectoryGroup(
> > > name='Users',
> > > distinguishedName='cn=Users,ou=domain',
> > > )
> > > child = AnotherChild(
> > > name='Admins',
> > > distinguishedName='cn=Admins,ou=domain',
> > > )
> > >
> > > session.add(group)
> > > session.add(child)
> > > session.flush()
> > > session.commit()
> > >
> > > group =
> session.query(ActiveDirectoryGroup).filter(ActiveDirectoryGroup.name ==
> 'Users').one()
> > > group_mapper = sa.inspect(group.__class__)
> > > group_synonyms = group_mapper.synonyms.keys()
> > >
> > > child = session.query(AnotherChild).filter(AnotherChild.name
> == 'Admins').one()
> > > child_mapper = sa.inspect(child.__class__)
> > > child_synonyms = child_mapper.synonyms.keys()
> > >
> > > assert child_synonyms == group_synonyms
> > >
> > >
> > > Maybe I'm off in unsupported land again? I could define AnotherChild
> identical to ActiveDirectoryGroup, but there are additional synonyms on
> ActiveDirectoryGroup they both should have.
> >
> > the synonym() is only applied to ActiveDirectoryGroup because it is
> > naturally inherited by AnotherChild:
> >
> > child_synonyms = child_mapper.inherits.synonyms.keys()
> >
> > assert child_synonyms == group_synonyms
> >
> > but it's not getting mapped anyway, which seems like a bug. works
> > with @cascading though:
> >
> > class ActiveDirectoryEntry:
> > @declared_attr.cascading
> > def distinguishedName(cls):
> > return orm.synonym
Re: [sqlalchemy] Missing synonyms with multi-inheritance
I'm replying to my original post since it's related.
I'm still seeing missing synonyms on child classes when they are defined in
a mixin imported on a parent.
import sqlalchemy as sa
from sqlalchemy import orm
from sqlalchemy.ext.declarative import declarative_base, declared_attr
Base = declarative_base()
class DirectoryEntry(Base):
guid = sa.Column(sa.Integer, primary_key=True)
_type = sa.Column(sa.String, nullable=False)
distinguished_name = sa.Column(sa.String)
name = sa.Column(sa.String)
__tablename__ = 'directory_entry'
__mapper_args__ = {
'polymorphic_on': _type,
'polymorphic_identity': 'directory_entry',
}
class DirectoryGroup(DirectoryEntry):
__mapper_args__ = {
'polymorphic_identity': 'directory_group',
}
class ActiveDirectoryEntry:
@declared_attr
def distinguishedName(self):
return orm.synonym('distinguished_name')
class ActiveDirectoryGroup(ActiveDirectoryEntry, DirectoryGroup):
__mapper_args__ = {
'polymorphic_identity': 'active_directory_group'
}
class AnotherChild(ActiveDirectoryGroup):
__mapper_args__ = {
'polymorphic_identity': 'another_child'
}
engine =
sa.create_engine('postgresql+psycopg2://postgres@localhost/postgres',
echo=True, isolation_level='AUTOCOMMIT')
engine.execute('DROP DATABASE IF EXISTS inherit_test')
engine.execute('CREATE DATABASE inherit_test')
engine =
sa.create_engine('postgresql+psycopg2://postgres@localhost/inherit_test',
echo=True)
Base.metadata.create_all(engine)
session = orm.sessionmaker(bind=engine)()
group = ActiveDirectoryGroup(
name='Users',
distinguishedName='cn=Users,ou=domain',
)
child = AnotherChild(
name='Admins',
distinguishedName='cn=Admins,ou=domain',
)
session.add(group)
session.add(child)
session.flush()
session.commit()
group =
session.query(ActiveDirectoryGroup).filter(ActiveDirectoryGroup.name ==
'Users').one()
group_mapper = sa.inspect(group.__class__)
group_synonyms = group_mapper.synonyms.keys()
child = session.query(AnotherChild).filter(AnotherChild.name ==
'Admins').one()
child_mapper = sa.inspect(child.__class__)
child_synonyms = child_mapper.synonyms.keys()
assert child_synonyms == group_synonyms
Maybe I'm off in unsupported land again? I could define AnotherChild
identical to ActiveDirectoryGroup, but there are additional synonyms on
ActiveDirectoryGroup they both should have.
Thanks,
Derek
On Wednesday, May 9, 2018 at 2:16:22 PM UTC-5, Derek Lambert wrote:
>
> That was my conclusion too after consulting the googles.
>
> I've done as you suggested and things are working as expected. Thanks!
>
> On Monday, April 30, 2018 at 4:26:02 PM UTC-5, Mike Bayer wrote:
>>
>> On Mon, Apr 30, 2018 at 4:18 PM, Derek Lambert
>> wrote:
>> >>
>> >> mmm what do you mean by "mixin" here, it looks like every class you
>> >> have is mapped.
>> >>
>> >
>> > They are mapped in the code, but that's only so I can query them. I
>> > attempted to make LdapEntry and ActiveDirectoryEntry true mixin's by
>> setting
>> > __abstract__ = True.
>> >
>> >>
>> >>
>> >> this a heavy set of inheritance and I might also use composition
>> >> instead, though that would change your DB design.
>> >>
>> >
>> > The design isn't in production yet so now would be the time to change
>> it.
>> > Are you aware of any SQLAlchemy projects using composition I could
>> review?
>>
>> mmm not specifically, it means you might do something like store
>> "Entry" concepts in one table and "User" concepts in another.
>> looking more closely this seems like it would be akward also.
>>
>> looking more closely at your mappings it looks like only
>> DirectoryEntry and DirectoryUser actually have any columns. The rest
>> is all synonyms. I'd likely use mixins for all those synonym sets.
>>
>>
>> >
>> > Thanks,
>> > Derek
>> >
>> > On Monday, April 30, 2018 at 1:30:51 PM UTC-5, Mike Bayer wrote:
>> >>
>> >> On Mon, Apr 30, 2018 at 1:33 PM, Derek Lambert
>> >> wrote:
>> >> > I'm running into an issue in a hierarchy of single-table inheritance
>> >> > objects
>> >> > with multiple inheritance. The objects represent users/groups/etc.
>> from
>> >> > various directories and applications.
>> >> >
>> >> > Retrieving the list of synonyms from an object at the bottom of the
>> >> > inheritance tree doe
Re: [sqlalchemy] How to customize base declarative class to add naming conventions
Just define the naming convention dict in a separate file and import it
into each declarative base?
On Wednesday, September 5, 2018 at 4:18:44 AM UTC-5, René-paul Debroize
wrote:
>
> It would have been great to be able to do it via a mixin, I have several
> DB using decalarative base constructed with this Base mixin and I liked to
> have the same naming_convention for all the DBs without repeating myself.
> If it's not I guess i can still manage to find an acceptable way of doing
> it using the decalarative_base arg.
>
> Thanks.
>
> Le mar. 4 sept. 2018 à 17:10, Simon King > a écrit :
>
>> On Tue, Sep 4, 2018 at 3:48 PM > wrote:
>> >
>> > I'd like to create a mixin to specify naming conventions.
>> >
>> > I tried both:
>> >
>> > class Base:
>> > metadata = MetaData(naming_convention={
>> > "ix": "ix_%(column_0_label)s",
>> > "uq": "uq_%(table_name)s_%(column_0_name)s",
>> > "ck": "ck_%(table_name)s_%(constraint_name)s",
>> > "fk":
>> "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
>> > "pk": "pk_%(table_name)",
>> > })
>> >
>> >
>> > and
>> >
>> > class Base:
>> > @declared_attr
>> > def metadata(cls):
>> > return MetaData(naming_convention={
>> > "ix": "ix_%(column_0_label)s",
>> > "uq": "uq_%(table_name)s_%(column_0_name)s",
>> > "ck": "ck_%(table_name)s_%(constraint_name)s",
>> > "fk":
>> "fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s",
>> > "pk": "pk_%(table_name)",
>> > })
>> >
>> > But if I inspect a model created using this base I always got:
>> >
>> > >>> Test.metadata.naming_convention
>> > immutabledict({'ix': 'ix_%(column_0_label)s'})
>> >
>> > while I correctly have:
>> >
>> > >>> Base.metadata.naming_convention
>> > {'ix': 'ix_%(column_0_label)s',
>> > 'uq': 'uq_%(table_name)s_%(column_0_name)s',
>> > 'ck': 'ck_%(table_name)s_%(constraint_name)s',
>> > 'fk': 'fk_%(table_name)s_%(column_0_name)s_%(referred_table_name)s',
>> > 'pk': 'pk_%(table_name)'}
>> >
>> > What is the correct way to do it? what am i doing wrong? Should I do
>> this in my migration tool (alembic) ?
>> > Also would it works for unique constraint on multiple column or do we
>> have to name them explicitly.
>> >
>>
>> Is it important to you to do this via a mixin? declarative_base
>> accepts a "metadata" parameter, so something like this should work:
>>
>> metadata = MetaData(naming_convention={...})
>> Base = declarative_base(metadata=metadata)
>>
>> Hope that helps,
>>
>> Simon
>>
>> --
>> SQLAlchemy -
>> The Python SQL Toolkit and Object Relational Mapper
>>
>> http://www.sqlalchemy.org/
>>
>> To post example code, please provide an MCVE: Minimal, Complete, and
>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
>> description.
>> ---
>> 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 https://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/d/optout.
>>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Re: Deleting child object in one-to-many relationship trying to load relationship?
Doh that's exactly what I need. Need to read through the docs again, I
missed that parameter.
Thanks!
On Monday, August 6, 2018 at 4:46:57 PM UTC-5, Mike Bayer wrote:
>
> On Mon, Aug 6, 2018 at 4:14 PM, Derek Lambert > wrote:
> > I do want it to tell me when stuff is being eager loaded so I can
> optimize
> > those queries.
> >
> > SQLAlchemy shouldn't need to know about the other end of the
> relationship to
> > delete the person should it?
> >
> > I didn't notice this before, but when I add passive_deletes=True on the
> > "many" side I get the warning:
> >
> > SAWarning: On Person.category, 'passive_deletes' is normally configured
> on
> > one-to-many, one-to-one, many-to-many relationships only.
> >
> > If I move the passive_deletes to the "one" side I get the exception
> again.
>
> the lazy load here doesn't need to actually fire off so just limit
> your raiseload to sql only:
>
> orm.raiseload('*', sql_only=True)
>
> test script passes
>
>
>
>
> >
> > On Friday, August 3, 2018 at 2:29:45 PM UTC-5, Derek Lambert wrote:
> >>
> >> When deleting a child object in a one-to-many relationship retrieved
> with
> >> the raiseload('*') query option an exception is raised. Adding
> >> passive_deletes=True to the relationship on the child prevents the
> >> exception, but that seems like a hack.
> >>
> >> I can remove the option from the query, but I'm trying to understand
> the
> >> behavior here. Is this expected?
> >>
> >>
> >> import sqlalchemy as sa
> >> import sqlalchemy.orm as orm
> >> from sqlalchemy.ext.declarative import declarative_base
> >>
> >>
> >> Base = declarative_base()
> >>
> >>
> >> class Person(Base):
> >> id = sa.Column(sa.Integer, autoincrement=True,
> >> primary_key=True)
> >> category_id = sa.Column(sa.Integer, sa.ForeignKey('category.id'),
> >> nullable=False)
> >> name= sa.Column(sa.String)
> >>
> >> category= orm.relationship('Category',
> back_populates='persons',
> >> innerjoin=True)
> >>
> >> __tablename__ = 'person'
> >>
> >>
> >> class Category(Base):
> >> id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
> >> name = sa.Column(sa.String, unique=True)
> >>
> >> persons = orm.relationship('Person', back_populates='category')
> >>
> >> __tablename__ = 'category'
> >>
> >>
> >> engine_url = 'postgresql+psycopg2://postgres@localhost/test_test'
> >> engine = sa.create_engine(engine_url, echo=True)
> >>
> >> engine.execute('DROP TABLE IF EXISTS "person"')
> >> engine.execute('DROP TABLE IF EXISTS "category"')
> >>
> >> Base.metadata.create_all(engine)
> >>
> >> session = orm.sessionmaker(bind=engine)()
> >> category1 = Category(name='Category1')
> >> session.add(category1)
> >> session.commit()
> >> session.flush()
> >>
> >> person = Person(category=category1, name='Some guy')
> >> session.add(person)
> >> session.commit()
> >> session.flush()
> >>
> >> session.expunge_all()
> >>
> >> person = session.query(Person).filter(Person.name == 'Some
> >> guy').options(orm.raiseload('*')).one()
> >>
> >> session.delete(person)
> >> session.commit()
> >>
> >> This raises:
> >>
> >> Traceback (most recent call last):
> >> File "/Users/dereklambert/Development/test/misc/test_raise_load.py",
> >> line 52, in
> >> session.commit()
> >> File
> >>
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
>
> >> line 943, in commit
> >> self.transaction.commit()
> >> File
> >>
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
>
> >> line 467, in commit
> >> self._prepare_impl()
> >> File
> >>
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
>
> >> line 447, in _prepare_impl
> >> self.session.flush()
> >> File
&
[sqlalchemy] Re: Deleting child object in one-to-many relationship trying to load relationship?
I do want it to tell me when stuff is being eager loaded so I can optimize
those queries.
SQLAlchemy shouldn't need to know about the other end of the relationship
to delete the person should it?
I didn't notice this before, but when I add passive_deletes=True on the
"many" side I get the warning:
SAWarning: On Person.category, 'passive_deletes' is normally configured on
one-to-many, one-to-one, many-to-many relationships only.
If I move the passive_deletes to the "one" side I get the exception again.
On Friday, August 3, 2018 at 2:29:45 PM UTC-5, Derek Lambert wrote:
>
> When deleting a child object in a one-to-many relationship retrieved with
> the raiseload('*') query option an exception is raised. Adding
> passive_deletes=True to the relationship on the child prevents the
> exception, but that seems like a hack.
>
> I can remove the option from the query, but I'm trying to understand the
> behavior here. Is this expected?
>
>
> import sqlalchemy as sa
> import sqlalchemy.orm as orm
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> class Person(Base):
> id = sa.Column(sa.Integer, autoincrement=True,
> primary_key=True)
> category_id = sa.Column(sa.Integer, sa.ForeignKey('category.id'),
> nullable=False)
> name= sa.Column(sa.String)
>
> category= orm.relationship('Category', back_populates='persons',
> innerjoin=True)
>
> __tablename__ = 'person'
>
>
> class Category(Base):
> id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
> name = sa.Column(sa.String, unique=True)
>
> persons = orm.relationship('Person', back_populates='category')
>
> __tablename__ = 'category'
>
>
> engine_url = 'postgresql+psycopg2://postgres@localhost/test_test'
> engine = sa.create_engine(engine_url, echo=True)
>
> engine.execute('DROP TABLE IF EXISTS "person"')
> engine.execute('DROP TABLE IF EXISTS "category"')
>
> Base.metadata.create_all(engine)
>
> session = orm.sessionmaker(bind=engine)()
> category1 = Category(name='Category1')
> session.add(category1)
> session.commit()
> session.flush()
>
> person = Person(category=category1, name='Some guy')
> session.add(person)
> session.commit()
> session.flush()
>
> session.expunge_all()
>
> person = session.query(Person).filter(Person.name == 'Some
> guy').options(orm.raiseload('*')).one()
>
> session.delete(person)
> session.commit()
>
> This raises:
>
> Traceback (most recent call last):
> File "/Users/dereklambert/Development/test/misc/test_raise_load.py",
> line 52, in
> session.commit()
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
> line 943, in commit
> self.transaction.commit()
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
> line 467, in commit
> self._prepare_impl()
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
> line 447, in _prepare_impl
> self.session.flush()
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
> line 2254, in flush
> self._flush(objects)
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
> line 2380, in _flush
> transaction.rollback(_capture_exception=True)
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py",
>
> line 66, in __exit__
> compat.reraise(exc_type, exc_value, exc_tb)
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
>
> line 249, in reraise
> raise value
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
>
> line 2344, in _flush
> flush_context.execute()
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 370, in execute
> postsort_actions = self._generate_actions()
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
>
> line 329, in _generate_actions
> if action.execute(self):
> File
> "/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/unitofwo
[sqlalchemy] Deleting child object in one-to-many relationship trying to load relationship?
When deleting a child object in a one-to-many relationship retrieved with
the raiseload('*') query option an exception is raised. Adding
passive_deletes=True to the relationship on the child prevents the
exception, but that seems like a hack.
I can remove the option from the query, but I'm trying to understand the
behavior here. Is this expected?
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Person(Base):
id = sa.Column(sa.Integer, autoincrement=True,
primary_key=True)
category_id = sa.Column(sa.Integer, sa.ForeignKey('category.id'),
nullable=False)
name= sa.Column(sa.String)
category= orm.relationship('Category', back_populates='persons',
innerjoin=True)
__tablename__ = 'person'
class Category(Base):
id = sa.Column(sa.Integer, autoincrement=True, primary_key=True)
name = sa.Column(sa.String, unique=True)
persons = orm.relationship('Person', back_populates='category')
__tablename__ = 'category'
engine_url = 'postgresql+psycopg2://postgres@localhost/test_test'
engine = sa.create_engine(engine_url, echo=True)
engine.execute('DROP TABLE IF EXISTS "person"')
engine.execute('DROP TABLE IF EXISTS "category"')
Base.metadata.create_all(engine)
session = orm.sessionmaker(bind=engine)()
category1 = Category(name='Category1')
session.add(category1)
session.commit()
session.flush()
person = Person(category=category1, name='Some guy')
session.add(person)
session.commit()
session.flush()
session.expunge_all()
person = session.query(Person).filter(Person.name == 'Some
guy').options(orm.raiseload('*')).one()
session.delete(person)
session.commit()
This raises:
Traceback (most recent call last):
File "/Users/dereklambert/Development/test/misc/test_raise_load.py", line
52, in
session.commit()
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
line 943, in commit
self.transaction.commit()
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
line 467, in commit
self._prepare_impl()
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
line 447, in _prepare_impl
self.session.flush()
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
line 2254, in flush
self._flush(objects)
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
line 2380, in _flush
transaction.rollback(_capture_exception=True)
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/util/langhelpers.py",
line 66, in __exit__
compat.reraise(exc_type, exc_value, exc_tb)
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/util/compat.py",
line 249, in reraise
raise value
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/session.py",
line 2344, in _flush
flush_context.execute()
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
line 370, in execute
postsort_actions = self._generate_actions()
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
line 329, in _generate_actions
if action.execute(self):
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
line 463, in execute
prop_has_changes(uow, delete_states, True) or
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/dependency.py",
line 234, in prop_has_changes
passive)
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/unitofwork.py",
line 225, in get_attribute_history
attributes.LOAD_AGAINST_COMMITTED)
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py",
line 753, in get_history
current = self.get(state, dict_, passive=passive)
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/attributes.py",
line 597, in get
value = callable_(state, passive)
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/strategies.py",
line 834, in __call__
return strategy._load_for_state(state, passive)
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/strategies.py",
line 589, in _load_for_state
self._invoke_raise_load(state, passive, "raise")
File
"/Users/dereklambert/Development/test/venv/lib/python3.6/site-packages/sqlalchemy/orm/strategies.py",
line 564, in _invoke_raise_load
"'%s' is not available due to
Re: [sqlalchemy] Query filter for class in inheritance mapping?
That's what I do in most circumstances. In this case I'm building a more
complicated query and was hoping to do something like
sess.query(Employee).filter(
or_(
and_(isclass(Manager), Manager.name.startswith('Jo')),
and_(isclass(Engineer),
Engineer.engineer_info.startswith('Design')),
...
...
)
)
instead of performing multiple queries.
On Wednesday, May 30, 2018 at 12:50:40 PM UTC-5, Mike Bayer wrote:
>
> On Wed, May 30, 2018 at 12:39 PM, Derek Lambert
> > wrote:
> > Outside of checking the discriminator value is it possible to filter a
> query
> > by a class in an inheritance mapping?
> >
> > class Employee(Base):
> > __tablename__ = 'employee'
> > id = Column(Integer, primary_key=True)
> > name = Column(String(50))
> > type = Column(String(20))
> >
> > __mapper_args__ = {
> > 'polymorphic_on':type,
> > 'polymorphic_identity':'employee'
> > }
> >
> > class Manager(Employee):
> > manager_data = Column(String(50))
> >
> > __mapper_args__ = {
> > 'polymorphic_identity':'manager'
> > }
> >
> > class Engineer(Employee):
> > engineer_info = Column(String(50))
> >
> > __mapper_args__ = {
> > 'polymorphic_identity':'engineer'
> > }
> >
> > and then query something like:
> >
> > engineers = session.query(Employee).filter(isclass(Engineer)).all()
>
> I'm sure you know the easiest way is query for that class:
>
> sess.query(Engineer).all()
>
>
> short of that you can try changing the entitiy:
>
> sess.query(Employee).with_entities(Engineer).all()
>
>
>
>
> >
> > --
> > SQLAlchemy -
> > The Python SQL Toolkit and Object Relational Mapper
> >
> > http://www.sqlalchemy.org/
> >
> > To post example code, please provide an MCVE: Minimal, Complete, and
> > Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> > description.
> > ---
> > 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 https://groups.google.com/group/sqlalchemy.
> > For more options, visit https://groups.google.com/d/optout.
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Query filter for class in inheritance mapping?
Outside of checking the discriminator value is it possible to filter a
query by a class in an inheritance mapping?
class Employee(Base):
__tablename__ = 'employee'
id = Column(Integer, primary_key=True)
name = Column(String(50))
type = Column(String(20))
__mapper_args__ = {
'polymorphic_on':type,
'polymorphic_identity':'employee'
}
class Manager(Employee):
manager_data = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'manager'
}
class Engineer(Employee):
engineer_info = Column(String(50))
__mapper_args__ = {
'polymorphic_identity':'engineer'
}
and then query something like:
engineers = session.query(Employee).filter(isclass(Engineer)).all()
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Missing synonyms with multi-inheritance
That was my conclusion too after consulting the googles.
I've done as you suggested and things are working as expected. Thanks!
On Monday, April 30, 2018 at 4:26:02 PM UTC-5, Mike Bayer wrote:
>
> On Mon, Apr 30, 2018 at 4:18 PM, Derek Lambert
> <dlam...@dereklambert.com > wrote:
> >>
> >> mmm what do you mean by "mixin" here, it looks like every class you
> >> have is mapped.
> >>
> >
> > They are mapped in the code, but that's only so I can query them. I
> > attempted to make LdapEntry and ActiveDirectoryEntry true mixin's by
> setting
> > __abstract__ = True.
> >
> >>
> >>
> >> this a heavy set of inheritance and I might also use composition
> >> instead, though that would change your DB design.
> >>
> >
> > The design isn't in production yet so now would be the time to change
> it.
> > Are you aware of any SQLAlchemy projects using composition I could
> review?
>
> mmm not specifically, it means you might do something like store
> "Entry" concepts in one table and "User" concepts in another.
> looking more closely this seems like it would be akward also.
>
> looking more closely at your mappings it looks like only
> DirectoryEntry and DirectoryUser actually have any columns. The rest
> is all synonyms. I'd likely use mixins for all those synonym sets.
>
>
> >
> > Thanks,
> > Derek
> >
> > On Monday, April 30, 2018 at 1:30:51 PM UTC-5, Mike Bayer wrote:
> >>
> >> On Mon, Apr 30, 2018 at 1:33 PM, Derek Lambert
> >> <dlam...@dereklambert.com> wrote:
> >> > I'm running into an issue in a hierarchy of single-table inheritance
> >> > objects
> >> > with multiple inheritance. The objects represent users/groups/etc.
> from
> >> > various directories and applications.
> >> >
> >> > Retrieving the list of synonyms from an object at the bottom of the
> >> > inheritance tree doesn't return the entire list of synonyms.
> >> >
> >> > When I make some of the "mixin" type objects abstract the synonyms
> >> > returned
> >> > are as expected, but I lose the ability to query those objects.
> >>
> >> mmm what do you mean by "mixin" here, it looks like every class you
> >> have is mapped.
> >>
> >> I will say that what you are doing here:
> >>
> >> class LdapUser(DirectoryUser, LdapEntry):
> >> givenName = orm.synonym('first_name')
> >> sn= orm.synonym('last_name')
> >> __mapper_args__ = {
> >> 'polymorphic_identity': 'ldap_user',
> >> }
> >>
> >> where DirectoryUser and LdapEntry are also both mapped, I'm amazed
> >> that even works. That's not at all anything that has ever been
> >> supported or attempted, as each mapper only "inherits" from at most
> >> one mapped class - while declarative supports actual "mixin" classes,
> >> where by "mixin" we mean "non-mapped class", nothing in SQLAlchemy ORM
> >> is expecting multiple inheritance at the mapper level. Above, I
> >> guess it's picking one superclass mapper at random to be "inherits",
> >> an ignoring the other, and that is likely the source of your issue.
> >> Unfortunately I think you have to work out this hierarchy in terms of
> >> single-inhertanace for classes that are actually mapped, which means
> >> adding some non-mapped "mixin" classes that just accommodate for the
> >> extra synonyms, something like:
> >>
> >> class DirectoryEntry(Base):
> >>
> >> class AbstractDirectoryUser(object):
> >># synonyms
> >>
> >> class DirectoryUser(AbstractDirectoryUser, DirectoryEntry):
> >>
> >> class LdapEntry(DirectoryEntry):
> >>
> >> class LdapUser(AbstractDirectoryUser, LdapEntry):
> >>
> >> this a heavy set of inheritance and I might also use composition
> >> instead, though that would change your DB design.
> >>
> >>
> >> >
> >> > Maybe I'm overlooking a simpler implementation, or simply using
> >> > SQLAlchemy
> >> > in a way that wasn't intended?
> >> >
> >> > Here's a simplified subset of the code. In practice any object ending
> >> > with
> >> > Entry and the base DirectoryUser and
Re: [sqlalchemy] Missing synonyms with multi-inheritance
>
>
> mmm what do you mean by "mixin" here, it looks like every class you
> have is mapped.
>
>
They are mapped in the code, but that's only so I can query them. I
attempted to make LdapEntry and ActiveDirectoryEntry true mixin's by
setting __abstract__ = True.
>
> this a heavy set of inheritance and I might also use composition
> instead, though that would change your DB design.
>
>
The design isn't in production yet so now would be the time to change it.
Are you aware of any SQLAlchemy projects using composition I could review?
Thanks,
Derek
On Monday, April 30, 2018 at 1:30:51 PM UTC-5, Mike Bayer wrote:
> On Mon, Apr 30, 2018 at 1:33 PM, Derek Lambert
> <dlam...@dereklambert.com > wrote:
> > I'm running into an issue in a hierarchy of single-table inheritance
> objects
> > with multiple inheritance. The objects represent users/groups/etc. from
> > various directories and applications.
> >
> > Retrieving the list of synonyms from an object at the bottom of the
> > inheritance tree doesn't return the entire list of synonyms.
> >
> > When I make some of the "mixin" type objects abstract the synonyms
> returned
> > are as expected, but I lose the ability to query those objects.
>
> mmm what do you mean by "mixin" here, it looks like every class you
> have is mapped.
>
> I will say that what you are doing here:
>
> class LdapUser(DirectoryUser, LdapEntry):
> givenName = orm.synonym('first_name')
> sn= orm.synonym('last_name')
> __mapper_args__ = {
> 'polymorphic_identity': 'ldap_user',
> }
>
> where DirectoryUser and LdapEntry are also both mapped, I'm amazed
> that even works. That's not at all anything that has ever been
> supported or attempted, as each mapper only "inherits" from at most
> one mapped class - while declarative supports actual "mixin" classes,
> where by "mixin" we mean "non-mapped class", nothing in SQLAlchemy ORM
> is expecting multiple inheritance at the mapper level. Above, I
> guess it's picking one superclass mapper at random to be "inherits",
> an ignoring the other, and that is likely the source of your issue.
> Unfortunately I think you have to work out this hierarchy in terms of
> single-inhertanace for classes that are actually mapped, which means
> adding some non-mapped "mixin" classes that just accommodate for the
> extra synonyms, something like:
>
> class DirectoryEntry(Base):
>
> class AbstractDirectoryUser(object):
># synonyms
>
> class DirectoryUser(AbstractDirectoryUser, DirectoryEntry):
>
> class LdapEntry(DirectoryEntry):
>
> class LdapUser(AbstractDirectoryUser, LdapEntry):
>
> this a heavy set of inheritance and I might also use composition
> instead, though that would change your DB design.
>
>
> >
> > Maybe I'm overlooking a simpler implementation, or simply using
> SQLAlchemy
> > in a way that wasn't intended?
> >
> > Here's a simplified subset of the code. In practice any object ending
> with
> > Entry and the base DirectoryUser and DirectoryGroup wouldn't be created.
> >
> > import sqlalchemy as sa
> > import sqlalchemy.orm as orm
> > from sqlalchemy.ext.declarative import declarative_base
> >
> >
> > Base = declarative_base()
> >
> >
> > class DirectoryEntry(Base):
> > guid = sa.Column(sa.Integer, primary_key=True)
> > _type = sa.Column(sa.String, nullable=False,
> index=True)
> > distinguished_name = sa.Column(sa.String, index=True)
> > name = sa.Column(sa.String, index=True)
> >
> > __tablename__ = 'directory_entry'
> > __mapper_args__ = {
> > 'polymorphic_on': _type,
> > 'polymorphic_identity': 'directory_entry',
> > }
> >
> >
> > class DirectoryUser(DirectoryEntry):
> > first_name = sa.Column(sa.String)
> > last_name = sa.Column(sa.String)
> > email = sa.Column(sa.String)
> > username = sa.Column(sa.String)
> >
> > __mapper_args__ = {
> > 'polymorphic_identity': 'directory_user',
> > }
> >
> >
> > class LdapEntry(DirectoryEntry):
> > cn = orm.synonym('name')
> >
> > __mapper_args__ = {
> > 'polymorphic_identity': 'ldap_entry',
> > }
> >
> >
> > class LdapUser(DirectoryUser, LdapEntry):
> > givenName = orm.synonym('first_name')
> >
[sqlalchemy] Missing synonyms with multi-inheritance
I'm running into an issue in a hierarchy of single-table inheritance
objects with multiple inheritance. The objects represent users/groups/etc.
from various directories and applications.
Retrieving the list of synonyms from an object at the bottom of the
inheritance tree doesn't return the entire list of synonyms.
When I make some of the "mixin" type objects abstract the synonyms returned
are as expected, but I lose the ability to query those objects.
Maybe I'm overlooking a simpler implementation, or simply using SQLAlchemy
in a way that wasn't intended?
Here's a simplified subset of the code. In practice any object ending with
Entry and the base DirectoryUser and DirectoryGroup wouldn't be created.
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class DirectoryEntry(Base):
guid = sa.Column(sa.Integer, primary_key=True)
_type = sa.Column(sa.String, nullable=False, index=True)
distinguished_name = sa.Column(sa.String, index=True)
name = sa.Column(sa.String, index=True)
__tablename__ = 'directory_entry'
__mapper_args__ = {
'polymorphic_on': _type,
'polymorphic_identity': 'directory_entry',
}
class DirectoryUser(DirectoryEntry):
first_name = sa.Column(sa.String)
last_name = sa.Column(sa.String)
email = sa.Column(sa.String)
username = sa.Column(sa.String)
__mapper_args__ = {
'polymorphic_identity': 'directory_user',
}
class LdapEntry(DirectoryEntry):
cn = orm.synonym('name')
__mapper_args__ = {
'polymorphic_identity': 'ldap_entry',
}
class LdapUser(DirectoryUser, LdapEntry):
givenName = orm.synonym('first_name')
sn= orm.synonym('last_name')
__mapper_args__ = {
'polymorphic_identity': 'ldap_user',
}
class ActiveDirectoryEntry(LdapEntry):
distinguishedName = orm.synonym('distinguished_name')
__mapper_args__ = {
'polymorphic_identity': 'active_directory_entry',
}
class ActiveDirectoryUser(LdapUser, ActiveDirectoryEntry):
mail = orm.synonym('email')
sAMAccountName = orm.synonym('username')
__mapper_args__ = {
'polymorphic_identity': 'active_directory_user'
}
engine_url = 'postgresql+psycopg2://postgres@localhost/inherit_test'
engine = sa.create_engine(engine_url, echo=True)
Base.metadata.create_all(engine)
session = orm.sessionmaker(bind=engine)()
ad_user = ActiveDirectoryUser(
cn='John Doe',
sAMAccountName='jdoe',
distinguishedName='ou=domain',
givenName='John'
)
session.add(ad_user)
session.commit()
user1 = session.query(DirectoryUser).filter(DirectoryUser.username ==
'jdoe').one()
user3 = session.query(LdapUser).filter(LdapUser.username == 'jdoe').one()
user2 =
session.query(ActiveDirectoryUser).filter(ActiveDirectoryUser.username ==
'jdoe').one()
user4 = session.query(DirectoryEntry).filter(DirectoryEntry.name == 'John
Doe').one()
assert(user1 == user2 == user3 == user4)
mapper = sa.inspect(ad_user.__class__)
synonyms = mapper.synonyms.keys()
assert(synonyms == ['mail', 'sAMAccountName', 'givenName', 'sn', 'cn',
'distinguishedName'])
Any help is appreciated!
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: Foreign key not set when appending related object to collection using a variable?
Thank you, excellent explanation! Setting autoflush off fixed the issue.
In the end I created the Filter objects directly and set directory and
category on them, which generated a single INSERT. It was generating a ton
of SELECT / INSERT the way I was initially doing it.
On Saturday, March 17, 2018 at 2:02:00 PM UTC-5, Derek Lambert wrote:
>
> I'm probably overlooking something simple, looking for feedback before
> opening an issue.
>
> I have some objects with relationships defined between. When I create a
> new related object and pass it in the append() method of the collection
> everything works as expected, the foreign key is set. When I assign the new
> related object to a variable and pass that to the append() method, the
> foreign key isn't set and I get a 'null value in column "directory_name"
> violates not-null constraint'. This is with SQLAlchemy 1.2.5 and python 3.6.
>
> import sqlalchemy as sa
> import sqlalchemy.orm as orm
> from sqlalchemy.ext.declarative import declarative_base
>
>
> Base = declarative_base()
>
>
> class Directory(Base):
> name = sa.Column(sa.String, primary_key=True)
>
> __tablename__ = 'directory'
>
>
> class Category(Base):
> name = sa.Column(sa.String, primary_key=True)
>
> __tablename__ = 'category'
>
>
> class Filter(Base):
> directory_name = sa.Column(sa.String, sa.ForeignKey('directory.name'),
> primary_key=True)
> category_name = sa.Column(sa.String, sa.ForeignKey('category.name'),
> primary_key=True)
> filter = sa.Column(sa.String, primary_key=True)
>
> directory = orm.relationship('Directory',
> backref=orm.backref('filters', lazy='joined'), lazy='joined')
> category = orm.relationship('Category',
> backref=orm.backref('filters', lazy='joined'), lazy='joined')
>
> __tablename__ = 'filter'
>
>
> engine =
> sa.create_engine('postgresql+psycopg2://postgres@localhost/bug_test')
> Base.metadata.create_all(engine)
> session = orm.sessionmaker(bind=engine)()
>
> directory = Directory(name='test')
> category_a = Category(name='category a')
> category_b = Category(name='category b')
>
> session.add(directory)
> session.add(category_a)
> session.add(category_b)
> session.commit()
>
> assert len(session.new) == 0
>
> # Instantiate object in call to append - works
> directory.filters.append(Filter(filter='test filter', category=category_a))
> session.commit()
>
> assert len(session.new) == 0
>
> # Instantiate object before call to append - fails
> new_filter = Filter(filter='new filter', category=category_b)
> directory.filters.append(new_filter)
>
> session.commit()
>
> assert len(session.new) == 0
>
>
> Thanks,
> Derek
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Foreign key not set when appending related object to collection using a variable?
I'm probably overlooking something simple, looking for feedback before
opening an issue.
I have some objects with relationships defined between. When I create a new
related object and pass it in the append() method of the collection
everything works as expected, the foreign key is set. When I assign the new
related object to a variable and pass that to the append() method, the
foreign key isn't set and I get a 'null value in column "directory_name"
violates not-null constraint'. This is with SQLAlchemy 1.2.5 and python 3.6.
import sqlalchemy as sa
import sqlalchemy.orm as orm
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Directory(Base):
name = sa.Column(sa.String, primary_key=True)
__tablename__ = 'directory'
class Category(Base):
name = sa.Column(sa.String, primary_key=True)
__tablename__ = 'category'
class Filter(Base):
directory_name = sa.Column(sa.String, sa.ForeignKey('directory.name'),
primary_key=True)
category_name = sa.Column(sa.String, sa.ForeignKey('category.name'),
primary_key=True)
filter = sa.Column(sa.String, primary_key=True)
directory = orm.relationship('Directory',
backref=orm.backref('filters', lazy='joined'), lazy='joined')
category = orm.relationship('Category', backref=orm.backref('filters',
lazy='joined'), lazy='joined')
__tablename__ = 'filter'
engine =
sa.create_engine('postgresql+psycopg2://postgres@localhost/bug_test')
Base.metadata.create_all(engine)
session = orm.sessionmaker(bind=engine)()
directory = Directory(name='test')
category_a = Category(name='category a')
category_b = Category(name='category b')
session.add(directory)
session.add(category_a)
session.add(category_b)
session.commit()
assert len(session.new) == 0
# Instantiate object in call to append - works
directory.filters.append(Filter(filter='test filter', category=category_a))
session.commit()
assert len(session.new) == 0
# Instantiate object before call to append - fails
new_filter = Filter(filter='new filter', category=category_b)
directory.filters.append(new_filter)
session.commit()
assert len(session.new) == 0
Thanks,
Derek
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Re: SQLAlchemy utility function to evaluate a string
I created a parser using pyparser that takes a filter string similar to
what you'd pass to Query.filter(), and returns an object you can pass to
Query.filter(). ex. "Child.last_name == 'Smith' AND Child.first_name LIKE
'J%' OR Child.parent_id IS NULL". This was to allow storing the query
string in the database as text and avoid using eval() or similar.
It's nowhere near complete operator-wise but it does grab the classes from
Base, sounds similar to what you're trying to do?
https://gist.github.com/djlambert/dc909c4405df12c8a824121b2d4d713b
On Thursday, March 15, 2018 at 1:04:46 PM UTC-5, Josh wrote:
>
> Currently, SQLA lets us use constructs like `child =
> relationship('Child')`, where 'Child' is Python code that is evaluated
> against some global-ish namespace. (More advanced: "Child.id == Parent.id",
> etc)
>
> Is this something that is available as a public library? We'd like to use
> a similar pattern in our own mixin - we have a list of models that a model
> can link to, and we'd like to be able to say `linked_models = ['ModelA',
> 'ModelB']` and have that later be able to return the actual classes. (The
> reason why we have this is a bit long and tedious, but it's basically a
> hack to avoid circular imports.)
>
> In other words, is there a function that can do `model_cls =
> looup_model(model_name)`?
>
> Thanks,
> Josh
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Association proxy to plain @property
Michael (or anyone listening), In my example code in #4202 (https://bitbucket.org/zzzeek/sqlalchemy/issues/4202/associationproxy-no-longer-resolved-to) you mentioned the assocaition proxy to the plain members @property is probably not a supported pattern. It's working in my code, but I'd prefer to do things the "right way". Is there a receipie/example you can point me to showing a supported pattern? Thanks, Derek -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
