删除未在sqlalchemy中级联到表 [英] delete not cascaded to table in sqlalchemy
问题描述
我正在开发使用sqlalchemy 0.6的现有应用程序的扩展.
I am developing an extension to an existing app which uses sqlalchemy 0.6.
该应用程序具有以非声明方式创建的sqlalchemy表.我正在尝试在扩展程序中创建一个新表，该表的外键列指向应用程序数据库中主表的主键，并且我以声明方式创建它.
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.
这一切都很好，加载扩展程序后就创建了表，一点也没有抱怨.我的表将打印出来，并演示已经添加了新行.我想要并认为可能的(但不知道，因为我从未使用过sql或任何其他数据库)是在删除应用程序主表中具有相应外键的行时删除表中的相应行
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.
到目前为止，由于尝试了许多排列，因此没有任何效果.我认为设置了backref并定义了具有级联的delete的关系，应该不会有问题.因为新表是在扩展名中定义的，扩展名应该只是插件，所以我根本不想编辑主应用程序中的代码，至少这是我的目标.但是，我遇到的问题之一是我要引用的主应用程序表，在其类中没有定义成员变量，未在其映射器中声明其主键并且仅在表中声明了主键..这使得很难创建一个Relation(ship)子句，该子句的第一个自变量必须是类或映射器(在这种情况下，都没有声明主键).有什么办法可以做到这一点?
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?
ps-这是我正在使用的一些代码.LocalFile是声明性类.所有连接细节都由主应用程序处理.
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.
if not self.LocalFile.__table__.exists(bind=Engine):
self.LocalFile__table__.create(bind=Engine)
这里是LocalFile类-基类是声明性基类，在构造函数中传递了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)
后端是sqlite3.下面是使用echo命令生成的表的创建代码(感谢您指出这一点，它非常有用-我已经怀疑现有应用程序生成的SQL远远超出了必要).报告的sql表创建之后是删除行时生成的代码.我个人看不到任何引用本地文件表中可能删除行的语句，但是我目前几乎不了解sql.谢谢.
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.
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 row in table produces the following for the two tables:
本地文件表:(，u'310致尤马')(，u'Ravenous')
the local file table: (, u' 310 To Yuma') (, u' Ravenous')
现有应用中的电影表:(，u'IMDb-3:10 to Yuma')(，u'Ravenous')
the movie table in the existing app: (, u'IMDb - 3:10 to Yuma') (, u'Ravenous')
删除行时的代码太长，以至于我不能在此处包括它(大约200行-删除一行不是太多吗?)，但是它没有引用删除行中的行.localfile表.有如下语句:
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:
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',)
推荐答案
在SQLite中，您必须显式打开对外键的支持，否则它将忽略任何与外键相关的SQL.
In SQLite you must turn on support for foreign keys explicitly or it just ignores any SQL related to 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)
这篇关于删除未在sqlalchemy中级联到表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
