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

[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

[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

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

[Mike Bayer]

On 02/01/2016 11:18 AM, Rick Otten wrote:


When I used "aliased" with query.delete(), the fetch query seems to get
confused.

  * I'm using SQLAlchemy 1.0.11, which 'pip' tells me is the latest version.
  * My backend database is PostgreSQL 9.5


Code snippet:

|
fromsqlalchemy.orm importaliased

myTable =aliased(some_table,name='t')

purgeQuery =dbSession.query(myTable).filter(myTable.some_column ==someValue)

queryString
=str(purgeQuery.statement.compile(compile_kwargs={"literal_binds":True}))

purgeQuery.delete(synchronize_session='fetch')

|



*queryString* shows the expected query.  Something like:

select t.id, t.some_column, t.some_other_column from some_table as
*t* where t.some_column = /someValue
/



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?








This was a surprise to notice in the database logs.  This query seems to
get stuck on my database anyway, actually.

For now, I'll work around it by not aliasing my table name when I use a
query.delete().  I thought I'd mention it in case I'm doing something
wrong by using aliased() this way, or in case it was a bug ...

Thanks in advance for your attention to this matter!


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

[Rick Otten]

When I used "aliased" with query.delete(), the fetch query seems to get 
confused. 


   - I'm using SQLAlchemy 1.0.11, which 'pip' tells me is the latest 
   version.
   - My backend database is PostgreSQL 9.5
   

Code snippet:

from sqlalchemy.orm import aliased

myTable = aliased(some_table, name='t')

purgeQuery = dbSession.query(myTable).filter(myTable.some_column == 
someValue)

queryString = str(purgeQuery.statement.compile(compile_kwargs={
"literal_binds": True}))

purgeQuery.delete(synchronize_session='fetch')




*queryString* shows the expected query.  Something like:

select t.id, t.some_column, t.some_other_column from some_table as *t* where
>  t.some_column = 
> *someValue*



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.id from *some_table, some_table as t* where t.some_column = 
> *someValue*



This was a surprise to notice in the database logs.  This query seems to 
get stuck on my database anyway, actually.

For now, I'll work around it by not aliasing my table name when I use a 
query.delete().  I thought I'd mention it in case I'm doing something wrong 
by using aliased() this way, or in case it was a bug ...

Thanks in advance for your attention to this matter!


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