1

I am having difficulty in achieving a delete scenario. I am trying to read a set of data using joins from 2 different source tables (using Sqlalchemy models) and passing the result to a delete function. Sqlalchemy throws the error,

Can't call Query.update() or Query.delete() when join(), outerjoin(), select_from(), or from_self() has been called.

Not sure what am missing here, any help is greatly appreciated! Here's a snippet of my code,

result_set = self.session.query(Table1). \
                    join(Table2,
                         and_(Table2.col1 == Table1.col1,
                              Table2.col2 == func.ifnull(Table1.col2, '0'))).\
                    filter(Table1.col1 == '004d5b46-54aa-4da6-a182-6069f4895e7f')
    
result_set.delete(synchronize_session=False)

Here's the actual Sql statement of the above,

select * from table1 t1
join table2 t2 on
t2.col1 = t1.col1 and
t2.col2 = ifnull(t1.col2,'0')
Prasanth S
  • 41
  • 1
  • 4
  • Does this answer your question https://stackoverflow.com/questions/25395756/sqlalchemy-how-to-delete-with-join? – snakecharmerb Dec 05 '20 at 17:13
  • It didn't help! I don't want to use 'session.execute()' since it literally makes me run raw SQL statements in it and defeats the purpose. As you can seen in my code, I tried the last suggestion in your above post `q.delete(synchronize_session=False)` and it didn't work either. That's what lead to the error I posted in the title of my question. – Prasanth S Dec 05 '20 at 19:42
  • The [code](https://github.com/sqlalchemy/sqlalchemy/blob/6eda3aa8c5f58e415a49ac8dc73eb8608f4be306/lib/sqlalchemy/orm/persistence.py#L1654) that raises the errors seems to prohibit using the result of `session.join` in any circumstances. The alternatives seem to be making the selection using `.filter` - as shown in the answers I suggested - or looping over the results with `session.delete(obj)`. – snakecharmerb Dec 06 '20 at 08:57

0 Answers0