Delete non cascading tables in sqlalchemy

Question

I am developing an extension of an existing application that uses sqlalchemy 0.6.

The application has sqlalchemy tables created in a non-declarative way. I am trying to create a new table in my extension with a foreign key column pointing to the primary key of the main table in the application database, and I create it declaratively.

All this works fine, and the table is created after loading the extension and without any complaints. My table prints and demonstrates that new rows were added in order. What I want and think is possible (but I don’t know how I never used sql or any other database) so that the corresponding row in my table is deleted when the row in the main application table with the corresponding foreign key is deleted .

So far, when many permutations have been tried, nothing has worked. I thought there shouldn't be a problem with setting up backref and with the relationship defined with the delete cascade. Since the new table is defined in the extension, which should be just a plugin, I don’t want to edit the code in the main application at all, at least this is my goal. One of the problems that I have is that the main application table I want to reference does not have member variables defined in its class, does not declare its primary key in its mapper and only has a primary key declared in table, This makes it difficult to create a sentence relation (ship), the first argument of which should be a class or a cartographer (in this case, none of them will declare a primary key). Is there any way to achieve this?

ps are some of the code I'm using. LocalFile is a declarative class. All connection details will be taken care of by the main application.

if not self.LocalFile.__table__.exists(bind=Engine): self.LocalFile__table__.create(bind=Engine) 

Here is the LocalFile class - Base is a declarative base class with bind = Engine passed in the constructor:

  class LocalFile(Base): __tablename__ = 'local_file' _id = Column(Integer, Sequence('local_file_sequence', start=1, increment=1), primary_key=True) _filename = Column(String(50), nullable=False) _filepath = Column(String(128), nullable=False) _movieid = Column(Integer, ForeignKey(db.tables.movies.c.movie_id, onupdate='CASCADE', ondelete='CASCADE')) #movies = relation(db.Movie, backref="local_file", cascade="all") @property def filename(self): return self._filename @filename.setter def filename(self, filename): self._filename = filename @property def filepath(self): return self._filepath @filepath.setter def filepath(self, filepath): self._filepath = filepath @property def movieid(self): return self._movieid @movieid.setter def movieid(self, movieid): self._movieid = movieid @property def id(self): return self._id @id.setter def id(self, id): self._id = id filename = synonym('_filename', descriptor=filename) movieid = synonym('_movieid', descriptor=movieid) filepath = synonym('_filepath', descriptor=filepath) id = synonym('_id', descriptor=id) def __init__(self, filename, filepath, movieid): self._filename = filename self._filepath = filepath self._movieid = movieid def __repr__(self): return "<User('%s','%s', '%s')>" % (self.filename, self.filepath, self.movieid) 

Edit:

The backend is sqlite3. Below is the code for creating the table created using the echo command (thanks for pointing out that this is very useful - I already suspect that the existing application generates much more sql than necessary). After the created table, the sql table creates the code generated when the row is deleted. I personally do not see any instruction that refers to the possible removal of a row in the local file table, but I know very little sql at the moment. Thank you

  2011-12-29 16:29:18,530 INFO sqlalchemy.engine.base.Engine.0x...0650 CREATE TABLE local_file ( _id INTEGER NOT NULL, _filename VARCHAR(50) NOT NULL, _filepath VARCHAR(128) NOT NULL, _movieid INTEGER, PRIMARY KEY (_id), FOREIGN KEY(_movieid) REFERENCES movies (movie_id) ON DELETE CASCADE ON UPDATE CASCADE 

)

  2011-12-29T16:29:18: I: sqlalchemy.engine.base.Engine.0x...0650(base:1387): CREATE TABLE local_file ( _id INTEGER NOT NULL, _filename VARCHAR(50) NOT NULL, _filepath VARCHAR(128) NOT NULL, _movieid INTEGER, PRIMARY KEY (_id), FOREIGN KEY(_movieid) REFERENCES movies (movie_id) ON DELETE CASCADE ON UPDATE CASCADE 

)

 2011-12-29 16:29:18,534 INFO sqlalchemy.engine.base.Engine.0x...0650 () 2011-12-29T16:29:18: I: sqlalchemy.engine.base.Engine.0x...0650(base:1388): () 2011-12-29 16:29:18,643 INFO sqlalchemy.engine.base.Engine.0x...0650 COMMIT 2011-12-29T16:29:18: I: sqlalchemy.engine.base.Engine.0x...0650(base:1095): COMMIT 

for a row in a table for two tables, the following is created:

local file table: (, u '310 To Yuma') (, u 'Ravenous')

table of films in the existing application: (, u'IMDb - 3:10 to Hume) (, u'Ravenous')

The code when deleting a line is so long that I cannot include it here (200 lines or so - is it not too much to delete one line?), But does not refer to deleting a line in localfile. There are statements such as:

  2011-12-29 17:09:17,141 INFO sqlalchemy.engine.base.Engine.0x...0650 UPDATE movies SET poster_md5=?, updated=? WHERE movies.movie_id = ? 2011-12-29T17:09:17: I: sqlalchemy.engine.base.Engine.0x...0650(base:1387): UPDATE movies SET poster_md5=?, updated=? WHERE movies.movie_id = ? 2011-12-29 17:09:17,142 INFO sqlalchemy.engine.base.Engine.0x...0650 (None, '2011-12-29 17:09:17.141019', 2) 2011-12-29T17:09:17: I: sqlalchemy.engine.base.Engine.0x...0650(base:1388): (None, '2011-12-29 17:09:17.141019', 2) 2011-12-29 17:09:17,150 INFO sqlalchemy.engine.base.Engine.0x...0650 DELETE FROM posters WHERE posters.md5sum = ? 2011-12-29T17:09:17: I: sqlalchemy.engine.base.Engine.0x...0650(base:1387): DELETE FROM posters WHERE posters.md5sum = ? 2011-12-29 17:09:17,157 INFO sqlalchemy.engine.base.Engine.0x...0650 (u'083841e14b8bb9ea166ea4b2b976f03d',) 

Answer 1

In SQLite, you must explicitly enable foreign key support, or simply ignore any SQL associated with foreign keys.

 engine = create_engine(database_url) def on_connect(conn, record): conn.execute('pragma foreign_keys=ON') from sqlalchemy import event event.listen(engine, 'connect', on_connect)