如何使用 sqlalchemy 有效管理频繁的架构更改？
我正在使用 sqlalchemy 编写一个 Web 应用程序。在开发的第一阶段，当网站还没有投入生产时，一切都很顺利。我可以通过简单地删除旧的 sqlite 数据库并从头开始创建一个新数据库来轻松更改数据库架构。
现在该网站已投入生产，我需要保留数据，但我仍然希望通过轻松地将数据库转换为新模式来保持原来的开发速度。
假设我有修订版 50 的 model.py 和修订版 75 的 model.py，描述数据库的架构。在这两个模式之间，大多数更改都是微不足道的，例如，使用默认值声明一个新列，我只想将此默认值添加到旧记录中。
最终，一些更改可能不是微不足道的，并且需要一些预先计算。
您如何（或将）如何处理快速变化的 Web 应用程序，例如每天使用一两个新版本的生产代码？
顺便说一句，如果这有什么区别的话，该网站是用 Pylons 编写的。
I'm programming a web application using sqlalchemy. Everything was smooth during the first phase of development when the site was not in production. I could easily change the database schema by simply deleting the old sqlite database and creating a new one from scratch.
Now the site is in production and I need to preserve the data, but I still want to keep my original development speed by easily converting the database to the new schema.
So let's say that I have model.py at revision 50 and model.py a revision 75, describing the schema of the database. Between those two schema most changes are trivial, for example a new column is declared with a default value and I just want to add this default value to old records.
Eventually a few changes may not be trivial and require some pre-computation.
How do (or would) you handle fast changing web applications with, say, one or two new version of the production code per day ?
By the way, the site is written in Pylons if this makes any difference.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
Alembic 是一个新的数据库迁移工具，由 SQLAlchemy 的作者编写。我发现它比 sqlalchemy-migrate 使用起来容易得多。它还可以与 Flask-SQLAlchemy 无缝协作。
从您的 SQLAlchemy 模型自动生成架构迁移脚本：
然后将新的架构更改应用到您的数据库：
更多信息请参见：http ://readthedocs.org/docs/alembic/
Alembic is a new database migrations tool, written by the author of SQLAlchemy. I've found it much easier to use than sqlalchemy-migrate. It also works seamlessly with Flask-SQLAlchemy.
Auto generate the schema migration script from your SQLAlchemy models:
Then apply the new schema changes to your database:
More info here: http://readthedocs.org/docs/alembic/
我们做什么。
使用“主要版本”.“次要版本”标识您的应用程序。主要版本是架构版本号。主要数字不是一些随机的“足够的新功能”之类的东西。这是与数据库架构兼容性的正式声明。
版本 2.3 和 2.4 均使用架构版本 2。
版本 3.1 使用版本 3 架构。
使架构版本非常非常明显。对于 SQLite，这意味着将架构版本号保留在数据库文件名中。对于 MySQL，请使用数据库名称。
编写迁移脚本。 2to3.py、3to4.py。这些脚本分两个阶段工作。 (1) 将旧数据查询到新结构中，创建简单的 CSV 或 JSON 文件。 (2) 从简单的 CSV 或 JSON 文件加载新结构，无需进一步处理。这些提取文件——因为它们具有正确的结构，加载速度快，并且可以轻松地用作单元测试装置。此外，您永远不会同时打开两个数据库。这使得脚本稍微简单一些。最后，加载文件可用于将数据移动到另一个数据库服务器。
“自动化”模式迁移非常非常困难。数据库手术很容易（而且很常见）如此深刻，以至于自动化脚本无法轻松地将数据从旧模式映射到新模式。
What we do.
Use "major version"."minor version" identification of your applications. Major version is the schema version number. The major number is no some random "enough new functionality" kind of thing. It's a formal declaration of compatibility with database schema.
Release 2.3 and 2.4 both use schema version 2.
Release 3.1 uses the version 3 schema.
Make the schema version very, very visible. For SQLite, this means keep the schema version number in the database file name. For MySQL, use the database name.
Write migration scripts. 2to3.py, 3to4.py. These scripts work in two phases. (1) Query the old data into the new structure creating simple CSV or JSON files. (2) Load the new structure from the simple CSV or JSON files with no further processing. These extract files -- because they're in the proper structure, are fast to load and can easily be used as unit test fixtures. Also, you never have two databases open at the same time. This makes the scripts slightly simpler. Finally, the load files can be used to move the data to another database server.
It's very, very hard to "automate" schema migration. It's easy (and common) to have database surgery so profound that an automated script can't easily map data from old schema to new schema.
使用 sqlalchemy-migrate。
它旨在支持敏捷的数据库设计方法，并在需要架构更改时更轻松地保持开发和生产数据库同步。它使模式版本控制变得容易。
将其视为数据库模式的版本控制。您将每个架构更改提交给它，它将能够在架构版本上前进/后退。这样，您就可以升级客户端，并且它将准确地知道要在该客户端的数据库上应用哪一组更改。
它会自动为您执行 S.Lott 在他的回答中提出的建议。使困难的事情变得容易。
Use sqlalchemy-migrate.
It is designed to support an agile approach to database design, and make it easier to keep development and production databases in sync, as schema changes are required. It makes schema versioning easy.
Think of it as a version control for your database schema. You commit each schema change to it, and it will be able to go forwards/backwards on the schema versions. That way you can upgrade a client and it will know exactly which set of changes to apply on that client's database.
It does what S.Lott proposes in his answer, automatically for you. Makes a hard thing easy.
处理问题的最佳方法是反映您的架构，而不是采用声明性方式。我在这里写了一篇关于反思方法的文章：
http://petrushev.wordpress.com/2010/ 06/16/sqlalchemy-usage 的反射方法/
但还有其他相关资源。通过这种方式，每次更改架构时，您所需要做的就是重新启动应用程序，反射将获取表中更改的新元数据。这非常快，并且 sqlalchemy 每个进程只执行一次。当然，你必须管理你自己做出的人际关系变化。
The best way to deal with your problem is to reflect your schema instead doing it the declarative way. I wrote an article about the reflective approach here:
http://petrushev.wordpress.com/2010/06/16/reflective-approach-on-sqlalchemy-usage/
but there are other resources about this also. In this manner, every time you make changes to your schema, all you need to do is restart the app and the reflection will fetch the new metadata for the changes in tables. This is quite fast and sqlalchemy does it only once per process. Of course, you'll have to manage the relationships changes you make yourself.