Databases
StackOverflow2013
Postsdbo
POdelete not cascaded to table in sqlalchemy
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POdelete not cascaded to table in sqlalchemy
    primarykey
    data
    text
    <p>I am developing an extension to an existing app which uses sqlalchemy 0.6.</p> <p>The app has sqlalchemy tables created the non-declarative way. I am trying to create in my extension a new table with a foreign key column pointing at the primary key of the main table in the application database and I am creating it declaratively.</p> <p>This all works fine, with the table created once the extension is loaded, and with no complaints at all. My table prints out and demonstrates that new rows have been added ok. What I want and think is possible (but don't know as I have never used sql or any other database) is for the corresponding row in my table to be deleted when the row in the app's main table with the corresponding foreign key is deleted.</p> <p>So far, and with many permutations having been tried, nothing has worked. I thought that with a backref set and with a relation defined with delete being cascaded, there shouldn't be a problem. Because the new table is defined in an extension which should just plugin, I don't want to edit the code in the main app at all, at least that is my goal. One of the problems that I have, though, is that the main app table that I want to reference, has no member variables defined in its class, does not declare its primary key in its mapper and only has the primary key declared in the table. This makes it difficult to create a relation(ship) clause, the first argument of which must be to a class or mapper (in this case neither of which have the primary key declared). Is there any way of achieving this?</p> <p>ps - here is some of the code that I am using. LocalFile is the declarative class. All the connection details are taken care of by the main application.</p> <pre><code> if not self.LocalFile.__table__.exists(bind=Engine): self.LocalFile__table__.create(bind=Engine) </code></pre> <p>Here is the LocalFile class - Base is a declarative base class with bind=Engine passed in the constructor:</p> <pre><code> 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 "&lt;User('%s','%s', '%s')&gt;" % (self.filename, self.filepath, self.movieid) </code></pre> <p>Edit:</p> <p>The backend is sqlite3. Below is the code from the creation of the table produced by using the echo command (thanks for pointing that out, it's very useful - already I suspect that the existing application is generating far more sql than is necessary). Following the reported sql table creation is the code generated when a row is removed. I personally can't see any statement that references the possible deletion of a row in the local file table, but I know very little sql currently. Thanks. </p> <pre><code> 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 </code></pre> <p>)</p> <pre><code> 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 </code></pre> <p>)</p> <pre><code>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 </code></pre> <p>for row in table produces the following for the two tables:</p> <p>the local file table: (, u' 310 To Yuma') (, u' Ravenous')</p> <p>the movie table in the existing app: (, u'IMDb - 3:10 to Yuma') (, u'Ravenous')</p> <p>The code when deleting a row is so long that I cannot include it here (200 lines or so - isn't that a little too many for deleting one row?), but it makes no reference to deleting a row in the localfile table. There are statements like:</p> <pre><code> 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',) </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBill the Lizard
    1. USmiller the gorilla
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POdelete not cascaded to table in sqlalchemy
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POdelete not cascaded to table in sqlalchemy
      1. This table or related slice is empty.
      1. VTUpMod
    1. COCan you please turn `echo=True`, ensure that table `local_file` does not exist, and run your code and check/post the `SQL` generated for table creation. Also please mention the DB backend you are using.
      singulars
      1. POdelete not cascaded to table in sqlalchemy
      1. USvan
    2. COhey van, just done that -edited the main post to include the code. The backend is sqlite3. Thanks!
      singulars
      1. POdelete not cascaded to table in sqlalchemy
      1. USmiller the gorilla
    3. COjust for info: if you noticed, you see each statement logged twice. this happens when both you have echo=True and also configure the logging for sqlalchemy packages explicitely...
      singulars
      1. POdelete not cascaded to table in sqlalchemy
      1. USvan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload