使用 sqlalchemy 和 elixir 更新 sqlite 数据库架构
我创建了一个 python 应用程序，它使用 elixir/sqlalchemy 来存储数据。该软件的第二个版本要求更新在先前版本中创建的所有文件，以便添加/删除表和列。
我的问题是：我怎样才能实现这一目标？我知道 sqlalchemy-migrate，但我必须说我觉得它很混乱。它没有提及现有数据会发生什么。此外，sqlite 还减少了 ALTER TABLE 支持，所以如果我尝试删除列，迁移会做什么？还有其他使用 migrate 的方法吗？
I've created a python application which uses elixir/sqlalchemy to store data. The second release of the software requires any files created in the previous version to be updated in order to add/delete tables and columns.
My question is: how can I achieve this? I'm aware of sqlalchemy-migrate, but I must say I find it confusing. It doesn't mention what happens to existing data. Moreover, sqlite has reduced ALTER TABLE support, so what will migrate do if I try to delete a column? Are there any other approaches to using migrate?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
你所说的是一个众所周知且相当复杂的问题。这称为数据库迁移。每个好的项目都有一些策略来描述如何应用数据库模式和数据突变来从一个产品版本升级到另一个产品版本。
许多框架（例如 Django 或 Ruby on Rails）都有内置的迁移系统或作为插件提供。您的 SQLAlchemy 案例没有几个选项：
/tmp/migrate.sql，写下 ALTER/DROP/CREATE 语句，祈祷并将其应用到您的 SQLite 基础上。这通常是一个坏主意，因为它容易出错，但选择取决于您。可以通过使用临时名称创建具有所需属性的新列、将原始列中的所有数据复制到其中、删除原始列以及将新列重命名为原始列来解决缺少全功能 ALTER TABLE 语句的问题姓名。可以在表级别使用相同的技术。您已经询问过如果您尝试删除列，SQLAlchemy-migrate 会做什么。好吧，它将删除一列，从而删除其中的所有数据。表中的其他列将保持不变。
What you're talking about is a well known and quite complex problem. It is known as database migration. Every good project have some policy that describes how database schema and data mutations should be applied to advance from one product version to the other.
Many frameworks such as Django or Ruby on Rails have a migration system built-in or available as a plug-in. Your case with SQLAlchemy has few options:
/tmp/migrate.sqlby hands, write down ALTER/DROP/CREATE statements, cross the fingers and apply it to your SQLite base. It is generally a bad idea since it is error-prone, but the choice is up to you. Absence of full-featuredALTER TABLEstatement could be worked around by creating new column with desired properties with temporary name, copying all data from original column to it, removing original column, and renaming new column to the original name. The same technique could be used at table-level.And you've asked about what SQLAlchemy-migrate will do if you'll try to delete a column. Well, it will delete a column and so delete any data that was in it. Other columns in table will be left intact.
sqlalchemy-migrate 的最新替代方案是 alembic，由SQLAlchemy作者本人编写。尽管后者（“同一作者”）看起来像是一个有力的论据，但缺点可能是它不支持 SQLite 的表 ALTERation，即它没有针对 SQLite 缺少 ALTER 支持的内置解决方法。 （有人可能会说这超出了范围，并且可以通过专门的 python 包或 SQLite 扩展来解决。）
A more recent alternative to sqlalchemy-migrate is alembic, written by the author of SQLAlchemy himself. Although the latter ("same author") looks like a strong argument, a downside could be that it does not support table ALTERation with SQLite, i.e. it has no built-in workarounds for SQLite’s missing ALTER support. (One could argue that that is out of scope and could well be solved by a specialized python package or SQLite extension.)
sqlalchemy-migrate 中什么让您感到困惑？它有 --preview_sql 和 --preview_py 选项来预览它将要执行的操作。一般来说，不可能针对任何可能的情况进行正确的迁移，但您可以修改生成的迁移脚本以满足您的需求。剩下的问题只要尝试一下就很容易得到答案。
What does confuse you in sqlalchemy-migrate? It has --preview_sql and --preview_py options to preview what it's going to do. In general it's impossible to do right migration for any possible case, but you can modify generated migration script to suite your needs. It's easy to get answers to the rest by trying it.