[Sqlalchemy-tickets] Issue #4342: Unable to use cascading deletion in SQLite database (zzzeek/sqlalchemy)

[wuhuanyan <iss...@bi...>]

New issue 4342: Unable to use cascading deletion in SQLite database
https://bitbucket.org/zzzeek/sqlalchemy/issues/4342/unable-to-use-cascading-deletion-in-sqlite

wuhuanyan:

I use SQLite database to test cascading deletion.

It is found that the MySQL database can be cascaded and deleted normally.

However, SQLite database cannot be cascaded and deleted.



Is the Interface class or InterfaceSource class definition incorrect?

Sqlalchemy:1.2.12

The test code is as follows:

```
#!python

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint
from sqlalchemy.orm import relationship, backref, Session
import sqlalchemy as sqla

Base = declarative_base()


class Interface(Base):
    __tablename__ = 't_interface'
    id = Column(Integer, primary_key=True)
    interface_name = Column(String(100), unique=False, nullable=True)
    interfacesource = relationship("InterfaceSource", cascade="all, delete-orphan", passive_deletes=True)


class InterfaceSource(Base):
    __tablename__ = 't_interface_source'
    id = Column(Integer, primary_key=True)
    interface_id = Column(Integer, ForeignKey('t_interface.id', ondelete='CASCADE'))
    interface = relationship("Interface")
    table_name = Column(String(100), unique=False, nullable=True)


def get_local_db():
    ip_address = 'localhost'
    username = 'root'
    password = 'Mysql123'
    schema = 'datatools'
    port = '3306'
    uri = 'mysql+pymysql://{}:{}@{}:{}/{}'.format(username, password, ip_address, port, schema)
    engine = sqla.create_engine(uri)
    Base.metadata.create_all(engine)
    return engine


def get_sqlite3_db():
    sqlite3_path = 'sqlite:{}'.format('///../datatools1.db')
    engine = sqla.create_engine(sqlite3_path)
    Base.metadata.create_all(engine)
    return engine


def insert(db):
    session = Session(db)

    interface = Interface()  # create Interface object
    interface.id = 1
    interface.interface_name = 'interface one'

    interface_source = InterfaceSource()  # create InterfaceSource object
    interface_source.id = 1
    interface_source.interface_id = 1
    interface_source.table_name = 'table one'

    session.add(interface)
    session.add(interface_source)
    session.commit()
    session.close()
    print('insert ok')


def select(db):
    session = Session(db)
    q_obj = session.query(Interface).filter(Interface.id == 1)
    for q in q_obj.all():
        print(q.id, q.interface_name)
    q_obj = session.query(InterfaceSource).filter(InterfaceSource.id == 1)
    for q in q_obj.all():
        print(q.id, q.interface_id, q.table_name)
    session.close()
    print('select ok')


def delete(db):
    session = Session(db)
    session.query(Interface).filter(Interface.id == 1).delete()
    session.commit()
    session.close()
    print('delete ok')


mysql_db = get_local_db()  # get mysql engine
sqlite_db = get_sqlite3_db()  # get sqlite engine
print('mysql')
insert(mysql_db)
select(mysql_db)
delete(mysql_db)
select(mysql_db)
print('\n\n')
print('sqlite')
insert(sqlite_db)
select(sqlite_db)
delete(sqlite_db)
select(sqlite_db)
```
![1537950826380.jpg](https://bitbucket.org/repo/ngx7X7k/images/2304889715-1537950826380.jpg)