Python 如何使用 sqlalchemy 有效管理频繁的架构更改？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4165452/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to efficiently manage frequent schema changes using sqlalchemy?
提问by ascobol
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.
我正在使用 sqlalchemy 编写 Web 应用程序。在网站未投入生产的第一阶段开发期间，一切都很顺利。我可以通过简单地删除旧的 sqlite 数据库并从头开始创建一个新的数据库来轻松更改数据库模式。
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.
因此，假设我有版本 50 的 model.py 和版本 75 的 model.py，描述了数据库的架构。在这两个模式之间，大多数更改都是微不足道的，例如，使用默认值声明了一个新列，而我只想将此默认值添加到旧记录中。
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 ?
您如何（或将）每天使用一两个新版本的生产代码来处理快速变化的 Web 应用程序？
By the way, the site is written in Pylons if this makes any difference.
顺便说一句，如果这有什么不同，该网站是用 Pylons 编写的。
采纳答案by Alan Hamlett
Alembicis 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.
Alembic是一个新的数据库迁移工具，由 SQLAlchemy 的作者编写。我发现它比 sqlalchemy-migrate 更容易使用。它还可以与 Flask-SQLAlchemy 无缝协作。
Auto generate the schema migration script from your SQLAlchemy models:
从您的 SQLAlchemy 模型自动生成模式迁移脚本：
alembic revision --autogenerate -m "description of changes"
Then apply the new schema changes to your database:
然后将新的架构更改应用于您的数据库：
alembic upgrade head
More info here: http://readthedocs.org/docs/alembic/
更多信息在这里：http: //readthedocs.org/docs/alembic/
回答by S.Lott
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.
使用“主要版本”.“次要版本”标识您的应用程序。主要版本是模式版本号。主要数字不是一些随机的“足够的新功能”之类的东西。这是与数据库模式兼容的正式声明。
版本 2.3 和 2.4 都使用模式版本 2。
版本 3.1 使用版本 3 模式。
使架构版本非常非常明显。对于 SQLite，这意味着在数据库文件名中保留架构版本号。对于 MySQL，请使用数据库名称。
编写迁移脚本。2to3.py，3to4.py。这些脚本分两个阶段工作。(1) 将旧数据查询到新结构中，创建简单的 CSV 或 JSON 文件。(2) 从简单的 CSV 或 JSON 文件加载新结构，无需进一步处理。这些提取文件——因为它们的结构正确，加载速度快，并且可以很容易地用作单元测试装置。此外，您永远不会同时打开两个数据库。这使得脚本稍微简单一些。最后，加载文件可用于将数据移动到另一个数据库服务器。
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.
“自动化”模式迁移非常非常困难。对数据库进行如此深刻的改造很容易（并且很常见），以至于自动化脚本无法轻松地将数据从旧模式映射到新模式。
回答by nosklo
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.
它会自动为您执行 S.Lott 在他的回答中提出的建议。让困难的事情变得容易。
回答by vonPetrushev
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.
处理您的问题的最佳方法是反映您的架构，而不是以声明方式进行。我在这里写了一篇关于反射方法的文章：http: //petrushev.wordpress.com/2010/06/16/reflective-approach-on-sqlalchemy-usage/，但还有其他关于此的资源。通过这种方式，每次对架构进行更改时，您所需要做的就是重新启动应用程序，反射将获取表中更改的新元数据。这非常快，而且 sqlalchemy 每个进程只执行一次。当然，您必须管理自己所做的关系变化。
