date:20160202

Re: [sqlalchemy] Is this a correct way to set up table inheritance? (circular dependency issue on delete attempt)

2016-02-02 Thread Mike Bayer




On 02/02/2016 04:27 PM, Brian Leach wrote:

This question is sparked by some trouble that I am having with deleting
model instances. Please see this
question: 
http://stackoverflow.com/questions/35163325/sqlalchemy-circular-dependency-on-delete

I have set up my models like below. I have several kinds of "tests"
defined in my database. Each "test" shares certain columns like
'status', 'date', 'who preformed it', etc. Each different test type then
defines columns specific to it.

|classHasID(object):@declared_attrdefid(cls):returnColumn('id',Integer,Sequence('test_id_seq'),primary_key=True)...classTestParent(HasID,Model)__tablename__
='tests'discriminator =Column(String(50))__mapper_args__
={'polymorphic_on':discriminator}...classFooTest(TestParent,Model):__tablename__
='footests'__mapper_args__ ={'polymorphic_identity':'footests'}id
=Column(Integer,ForeignKey('tests.id'),primary_key=True)parent_id
=Column(Integer,ForeignKey('footests.id'))children
=relationship('FooTest',foreign_keys='FooTest.id',lazy='joined',join_depth=2,cascade='save-update,
merge, delete,
delete-orphan')...classBarTest(TestParent,Model):__tablename__
='bartests'__mapper_args__ ={'polymorphic_identity':'bartests'}id
=Column(Integer,ForeignKey('tests.id'),primary_key=True)...|



Now, I am wondering if this is a correct way to set up the FooTest, as
one instance of FooTest may have several child instances of FooTest as
children.

I am unable to delete any test instance (BarTest or otherwise) and am
getting a circular dependency error referring to the FooTest table.


I think the relationship on FooTest needs to be:

 children = relationship('FooTest',
foreign_keys='FooTest.parent_id',
lazy='joined',
join_depth=2,
cascade='save-update, merge, delete, 
delete-orphan')



"foreign_keys" here refers to which column has the ForeignKey constraint 
on it within the relationship.   For a self-referential relationship 
there is also the concept of "remote_side", which in this case would be 
"FooTest.id", however this will be the default setup here as it assumes 
one-to-many by default.






Am I missing any fundamental concepts related to table inheritance?


Thanks everyone,

Brian Leach

--
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.


--
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] Is this a correct way to set up table inheritance? (circular dependency issue on delete attempt)

2016-02-02 Thread Brian Leach

This question is sparked by some trouble that I am having with deleting 
model instances. Please see this 
question: 
http://stackoverflow.com/questions/35163325/sqlalchemy-circular-dependency-on-delete

I have set up my models like below. I have several kinds of "tests" defined 
in my database. Each "test" shares certain columns like 'status', 'date', 
'who preformed it', etc. Each different test type then defines columns 
specific to it. 

class HasID(object):
@declared_attr
def id(cls):
return Column('id', Integer, Sequence('test_id_seq'), primary_key=True)
...

class TestParent(HasID, Model)
__tablename__ = 'tests'
discriminator = Column(String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
...

class FooTest(TestParent, Model):
__tablename__ = 'footests'
__mapper_args__ = {'polymorphic_identity': 'footests'}
id = Column(Integer, ForeignKey('tests.id'), primary_key=True)
parent_id = Column(Integer, ForeignKey('footests.id'))
children = relationship('FooTest',
foreign_keys='FooTest.id',
lazy='joined',
join_depth=2,
cascade='save-update, merge, delete, delete-orphan')
...

class BarTest(TestParent, Model):
__tablename__ = 'bartests'
__mapper_args__ = {'polymorphic_identity': 'bartests'}
id = Column(Integer, ForeignKey('tests.id'), primary_key=True)
...



Now, I am wondering if this is a correct way to set up the FooTest, as one 
instance of FooTest may have several child instances of FooTest as 
children. 

I am unable to delete any test instance (BarTest or otherwise) and am 
getting a circular dependency error referring to the FooTest table. 

Am I missing any fundamental concepts related to table inheritance?


Thanks everyone,

Brian Leach

-- 
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] query.delete(fetch) gets confused by aliased

2016-02-02 Thread Mike Bayer

On 02/02/2016 09:05 AM, Rick Otten wrote:

 > However, when the fetch query actually runs, it includes the
non aliased
 > table name as well as the aliased table name in the from
statement:
 >
 > select t.idfrom *some_table, some_table as t* where
t.some_column =
 > /someValue/

well that's not a DELETE statement.  query.delete() emits a DELETE
statement and you'd have to look in the SQL logs to see it.

There's no "bug" here as DELETE does not support deleting
directly from
an alias of a table; it's likely emitting DELETE .. FROM and
assuming
you want to do some kind of join against the aliased form of
your table.

Otherwise what SQL are you looking for this to produce that has
DELETE
but also an aliased form of the table?

I know that isn't the delete statement.  It is the FETCH statement
before the delete is called.
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.delete

oh im sorry, you mean the actual "SELECT" as part of the "fetch" 
strategy.   I wasn't sure what motivation there would be to send an 
aliased table into delete(), as I think your DELETE statement is also 
going to be not what you're expecting so that would be the more 
immediate issue.

I have taken to the habit of using "aliased" for many of my sqlalchemy
queries because it makes it much easier for me to read the query on the
database when I have to troubleshoot an issue.   Most queries are not as
simple as the example I posted.

OK, well sure, for a DELETE that doesn't really apply.

Aliased tables do not work with query.delete(), at least with
/synchronize_session='fetch'/.  I can live with that.  I was just
surprised by it and thought I'd share my experience.

Well an aliased table should probably not work with query.delete() ever, 
because there's no SQL for that and otherwise the intention is not 
clear.  So this is a bug, thanks for reporting.   At the very least it 
should emit a warning.

As far as I can see, it *doesn't* work:

class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)

a1 = aliased(A)
s.query(a1).filter(a1.id == 5).delete(synchronize_session='fetch')

output:

sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM-clause 
entry for table "a_1"

LINE 1: DELETE FROM a WHERE a_1.id = 5
^
 'DELETE FROM a WHERE a_1.id = %(id_1)s' {'id_1': 5}

I apologize for not focusing on the "fetch" part of this but that 
delete() even accepts the alias is a bigger issue, and I'm trying to 
understand more fully what you're doing.

--
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.

--
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] query.delete(fetch) gets confused by aliased

2016-02-02 Thread Rick Otten

No problem.  It makes sense now.  Adding aliased before I did anything with
a sqlalchemy query had become sort of a programming pattern and I wasn't
thinking any more at that time about what happens when the alias gets
translated into a SQL DELETE statement and how that wouldn't work anyhow.

To implement certain business rules, I often construct filters on queries
dynamically:

myQuery = dbsession.query(mytable)

if doNotIncludeFish:
myQuery = myQuery.filter(something that removes fish)

This particular delete request was constructed along those lines - with
some business logic adding supplemental filters before calling delete.

Thanks for taking a look!




On Tue, Feb 2, 2016 at 9:56 AM, Mike Bayer  wrote:

>
>
> On 02/02/2016 09:05 AM, Rick Otten wrote:
>
>>
>>  > However, when the fetch query actually runs, it includes the
>> non aliased
>>  > table name as well as the aliased table name in the from
>> statement:
>>  >
>>  > select t.idfrom *some_table, some_table as t* where
>> t.some_column =
>>  > /someValue/
>>
>> well that's not a DELETE statement.  query.delete() emits a DELETE
>> statement and you'd have to look in the SQL logs to see it.
>>
>> There's no "bug" here as DELETE does not support deleting
>> directly from
>> an alias of a table; it's likely emitting DELETE .. FROM and
>> assuming
>> you want to do some kind of join against the aliased form of
>> your table.
>>
>> Otherwise what SQL are you looking for this to produce that has
>> DELETE
>> but also an aliased form of the table?
>>
>>
>>
>>
>> I know that isn't the delete statement.  It is the FETCH statement
>> before the delete is called.
>>
>> http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.delete
>>
>
>
> oh im sorry, you mean the actual "SELECT" as part of the "fetch"
> strategy.   I wasn't sure what motivation there would be to send an aliased
> table into delete(), as I think your DELETE statement is also going to be
> not what you're expecting so that would be the more immediate issue.
>
>
>
>> I have taken to the habit of using "aliased" for many of my sqlalchemy
>> queries because it makes it much easier for me to read the query on the
>> database when I have to troubleshoot an issue.   Most queries are not as
>> simple as the example I posted.
>>
>
> OK, well sure, for a DELETE that doesn't really apply.
>
>
>> Aliased tables do not work with query.delete(), at least with
>> /synchronize_session='fetch'/.  I can live with that.  I was just
>> surprised by it and thought I'd share my experience.
>>
>
> Well an aliased table should probably not work with query.delete() ever,
> because there's no SQL for that and otherwise the intention is not clear.
> So this is a bug, thanks for reporting.   At the very least it should emit
> a warning.
>
> As far as I can see, it *doesn't* work:
>
> class A(Base):
> __tablename__ = 'a'
> id = Column(Integer, primary_key=True)
>
>
> a1 = aliased(A)
> s.query(a1).filter(a1.id == 5).delete(synchronize_session='fetch')
>
> output:
>
> sqlalchemy.exc.ProgrammingError: (ProgrammingError) missing FROM-clause
> entry for table "a_1"
> LINE 1: DELETE FROM a WHERE a_1.id = 5
> ^
>  'DELETE FROM a WHERE a_1.id = %(id_1)s' {'id_1': 5}
>
> I apologize for not focusing on the "fetch" part of this but that delete()
> even accepts the alias is a bigger issue, and I'm trying to understand more
> fully what you're doing.
>
>
>
>
>
>
>
>>
>>
>> --
>> 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.
>>
>
> --
> You received this message because you are subscribed to a topic in the
> Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/juYYYqqbVfo/unsubscribe.
> To unsubscribe from this group and all its topics, 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.
>

-- 
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

Re: [sqlalchemy] query.delete(fetch) gets confused by aliased

2016-02-02 Thread Rick Otten


>
>
>> > However, when the fetch query actually runs, it includes the non aliased
>> > table name as well as the aliased table name in the from statement:
>> >
>> > select t.idfrom *some_table, some_table as t* where t.some_column =
>> > /someValue/
>>
>> well that's not a DELETE statement.  query.delete() emits a DELETE
>> statement and you'd have to look in the SQL logs to see it.
>>
>> There's no "bug" here as DELETE does not support deleting directly from
>> an alias of a table; it's likely emitting DELETE .. FROM and assuming
>> you want to do some kind of join against the aliased form of your table.
>>
>> Otherwise what SQL are you looking for this to produce that has DELETE
>> but also an aliased form of the table? 
>
>
>
>
I know that isn't the delete statement.  It is the FETCH statement before 
the delete is called.
http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.delete

I have taken to the habit of using "aliased" for many of my sqlalchemy 
queries because it makes it much easier for me to read the query on the 
database when I have to troubleshoot an issue.   Most queries are not as 
simple as the example I posted.

Aliased tables do not work with query.delete(), at least with 
*synchronize_session='fetch'*.  I can live with that.  I was just surprised 
by it and thought I'd share my experience.



 

-- 
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] Is this a correct way to set up table inheritance? (circular dependency issue on delete attempt)

[sqlalchemy] Is this a correct way to set up table inheritance? (circular dependency issue on delete attempt)

Re: [sqlalchemy] query.delete(fetch) gets confused by aliased

Re: [sqlalchemy] query.delete(fetch) gets confused by aliased

Re: [sqlalchemy] query.delete(fetch) gets confused by aliased

5 matches

Site Navigation

Mail list logo

Footer information