sqlalchemy CompileError Unconsumed column names when deleting row from m2m table

Question

There is a m2m table which connects instances of one model making parent-child relations.

companies_connections = db.Table(
    'companies_connections',
    db.Column('parent_id', db.BigInteger(), db.ForeignKey('company.id'), primary_key=True),
    db.Column('child_id', db.BigInteger(), db.ForeignKey('company.id'), primary_key=True),
)

Try to delete row from table in after_insert event listener I have only Connection object because Session is dealing with other flush events. But using

q = companies_connections.delete(
    and_(
        companies_connections.c.parent_id == 10,
        companies_connections.c.child_id == 23
    )
)
connection.execute(q)

I get

CompileError: Unconsumed column names: parent_id_1, child_id_1

Why?

Answer 1

You should specify conditions inside where method:

q = companies_connections.delete().where(
   and_(
       companies_connections.c.parent_id == 10,
       companies_connections.c.child_id == 23
   )
)
connection.execute(q)

http://docs.sqlalchemy.org/en/latest/core/tutorial.html#deletes

Also, tables should be defined with metadata: http://docs.sqlalchemy.org/en/latest/core/tutorial.html#define-and-create-tables