使用 Python 部署数据库更改
我想知道是否有人可以推荐一个通过 python 部署数据库更改的好模式。
在我的场景中，我有一个或多个 PostgreSQL 数据库，并且我正在尝试为每个数据库部署一个代码库。下面是我的 SQL 脚本的目录结构示例：
my_db/
main.sql
some_directory/
foo.sql
bar.sql
some_other_directory/
baz.sql
下面是 main.sql 中内容的示例
/* main.sql has the following contents: */
BEGIN TRANSACTION
\i some_directory/bar.sql
\i some_directory/foo.sql
\i some_other_directory/baz.sql
COMMIT;
正如您所看到的，main.sql 定义了特定的操作顺序和数据库更新的事务。
我还有一个 python/twisted 服务来监视 SVN 中此数据库代码的更改，并且我希望在从 svn 存储库中发现新内容时自动部署此代码。
有人可以推荐一个在这里使用的好模式吗？
我应该解析每个文件吗？ 我应该花钱去 psql 吗？ ...
I'm wondering if someone can recommend a good pattern for deploying database changes via python.
In my scenario, I've got one or more PostgreSQL databases and I'm trying to deploy a code base to each one. Here's an example of the directory structure for my SQL scripts:
my_db/
main.sql
some_directory/
foo.sql
bar.sql
some_other_directory/
baz.sql
Here's an example of what's in main.sql
/* main.sql has the following contents: */
BEGIN TRANSACTION
\i some_directory/bar.sql
\i some_directory/foo.sql
\i some_other_directory/baz.sql
COMMIT;
As you can see, main.sql defines a specific order of operations and a transaction for the database updates.
I've also got a python / twisted service monitoring SVN for changes in this db code, and I'd like to automatically deploy this code upon discovery of new stuff from the svn repository.
Can someone recommend a good pattern to use here?
Should I be parsing each file?
Should I be shelling out to psql?
...
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果你控制所有服务器并且它们都是 postgresql 服务器，那么你所做的实际上是一个不错的方法。
更通用的方法是拥有一个“迁移”目录，这些目录通常是带有 apply() 和 undo() 的类，它们实际上在数据库中执行工作，并且通常带有
像 .create_table() 这样的抽象可以生成特定于您正在使用的任何 RDBMS 的 DDL 指令。
通常，您有一些命名约定来确保迁移按照创建的顺序运行。
有一个名为 South 的 Python 迁移库，尽管它似乎专门针对 django 开发。
http://south.aeracode.org/docs/about.html
What you're doing is actually a decent approach if you control all the servers and they're all postgresql servers.
A more general approach is to have a directory of "migrations" which are generally classes with an apply() and undo() that actually do the work in your database, and often come with
abstractions like .create_table() that generate the DDL instructions specific to whatever RDBMS you're using.
Generally, you have some naming convention that ensures the migrations run in the order they were created.
There's a migration library for python called South, though it appears to be geared specifically toward django development.
http://south.aeracode.org/docs/about.html
我们刚刚集成了 sqlalchemy-migrate，它具有一些类似 Rails 的漂亮约定，但具有强大的功能SQLAlchemy 的。它正在成为一个非常出色的产品，但它确实有一些缺点。不过集成起来相当无缝。
We just integrated sqlalchemy-migrate which has some pretty Rails-like conventions but with the power of SQLAlchemy. It's shaping up to be a really awesome product, but it does have some downfalls. It's pretty seamless to integrate though.