删除未级联到 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 定义的关系被级联，应该没有问题.因为新表是在应该只是插件的扩展中定义的，所以我根本不想在主应用程序中编辑代码，至少这是我的目标.但是，我遇到的问题之一是我要引用的主应用程序表在其类中未定义成员变量，未在其映射器中声明其主键，并且仅在表中声明了主键.这使得很难创建一个关系(船)子句，它的第一个参数必须是一个类或映射器(在这种情况下，它们都没有声明主键).有什么方法可以实现吗?
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 类 - Base 是一个声明性基类，在构造函数中传递了 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 为两个表生成以下内容:
for row in table produces the following for the two tables:
本地文件表:(, u' 310 To Yuma')(，你'贪婪')
the local file table: (, u' 310 To Yuma') (, u' Ravenous')
现有应用中的电影表:(, u'IMDb - 3:10 to Yuma')(，你'贪婪')
the movie table in the existing app: (, u'IMDb - 3:10 to Yuma') (, u'Ravenous')
删除一行时的代码太长了，我无法在此处包含它(大约 200 行 - 删除一行是不是有点太多了?)，但它没有提及删除本地文件表.有这样的语句:
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)
相关文章