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 <mike...@zzzcomputing.com> 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
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto: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 sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
