当前位置：文江博客话题详情

为什么拥有一个未绑定到 SQLAlchemy 引擎的 MetaData 对象很有用？

发布于 2024-11-28 21:02:47 字数 401 浏览 0 评论 0原文

我试图了解 MySQL 关于 MetaData 对象和 engine 对象的行为。这个SO-Answer描述了元数据 作为

表定义的集合

表定义和 engine

特定数据库的方言和连接详细信息

到目前为止一切顺利。但是什么时候把这两者分开有用呢？表定义不是链接到特定数据库吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

羁客 2024-12-05 21:02:48

SQLAlchemy 0.1 没有“元数据”对象 - Engine 直接绑定到每个 Table。这个想法很快就过时了，部分原因是人们希望在连接之前声明他们的 Table 对象，并且“绑定元数据”出现了。随后，出现了长期的群众混乱。人们有一个超级难的习惯（因为我告诉他们这样做），他们会说这样的话：

table.insert().execute()

result = table.select().execute()

即，没有交易，每次都使用新的连接。然后我们会说，“哦，你应该使用事务，你应该对连接更有效率”，然后我们基本上告诉他们必须重写他们的应用程序，而“sqlalchemy 有太多方法”模因像气球一样爆炸了。

与此同时，Pylons 和其他早期 WSGI 框架正在努力推动同时运行多个“应用程序”——在某些情况下，不同的“用户”在不同的数据库中拥有自己的一组表，之类的。更常见的是水平扩展方法，其中相同的表位于许多数据库中。我这里有一个应用程序，内置了一个“复制”系统，其中记录定期从“主”数据库复制到“历史”数据库，并且那里的表元数据也被共享。

关键是对于所有这些用例，用户都会使用 SQLA，并且他们对事物的理解始于“绑定元数据”。各地的博客和教程都使用了它。这些用户中的很大一部分需要突破该系统，并对整个“其他工作方式”感到完全困惑。因此，很明显，“绑定元数据”系统作为默认系统过于严格。理想情况下，我希望我根本没有实现过它，而且我自己也从来没有使用过它。这就是为什么它的文档现在只被推入一个部分，新用户只浏览文档，本质上给邮件列表增加了巨大的支持负担，找不到它，也不会感到困惑。该部分本身有大量的要点解释了它何时以及为何令人困惑。我假设您阅读了它，位于 http://www.sqlalchemy.org/docs/core/schema.html#binding-metadata-to-an-engine-or-connection 。

SQLAlchemy 0.1 didn't have a "metadata" object - the Engine was bound directly to each Table. That idea got old very quick, partially because folks wanted to declare their Table object before they connected, and "bound metadata" came along. Then, a long period of mass confusion occurred. People were in the super hard habit (since I told them to do it this way) of saying things like:

table.insert().execute()

result = table.select().execute()

I.e., no transaction, using a new connection each time. Then we'd say, "oh well you should be using a transaction, you should be more efficient about connections", and then we'd basically tell them they had to rewrite their app, and the "sqlalchemy has too many ways" meme blew up like a balloon.

At the same time, Pylons and other early WSGI frameworks were pushing hard to have multiple "apps" running at once - in some cases, different "users" would have their own set of tables each in different databases, kind of thing. More common are horizontal scaling approaches where the same tables are in many databases. I have an app here that has a "replication" system built in where records are copied from the "primary" database to the "history" database periodically, and the table metadata there is shared too.

The point is for all those use cases, users would come to SQLA and their understanding of things began with "bound metadata". Blogs and tutorials all over the place used it. And a good chunk of these users would need to break out of that system, and become completely confused that there was this whole "other way" of working. So it became clear that the "bound metadata" system was just too rigid as a default. Ideally I wish that I never implemented it at all, and I never use it myself. This is why the docs for it are pushed into just one section now, new users who only skim the docs, who by their nature add a huge support burden to the mailing list, don't find it and don't get confused. The section itself has plenty of bullets explaining exactly when and why it is confusing. I'm assuming you read it, at http://www.sqlalchemy.org/docs/core/schema.html#binding-metadata-to-an-engine-or-connection .

回复收藏 0 原文

肩上的翅膀 2024-12-05 21:02:48

事实上，表定义不链接到特定的数据库。 sqlalchemy 元数据对象和所有附加对象（表、列、索引等）定义了一个完全抽象的模式，不引用任何特定数据库。引擎本质上由三部分组成：连接信息、方言和连接池。方言是这里的重要部分，它定义了如何连接到特定的数据库，但更重要的是关于你的问题，它还定义了如何将sqla的抽象模式对象（元数据等）转换为特定于的sql命令该数据库和驱动程序。

这种分离有许多不同的用途：

应用程序可以在模块级别定义其架构，在导入模块时创建表和元数据...但不必定义引擎，直到应用程序实际启动并运行（因为了解完整的连接 url 和方言通常需要首先读取配置文件或获取用户输入）。
您可能会遇到将多个引擎绑定到同一组元数据的情况，例如一个 Web 应用程序，其中每个用户/密码都使用自己的凭据连接到数据库（这并不是说效率很高，但有时出于安全目的需要）。由于元数据是一个全局对象，因此在这种情况下将其绑定到特定引擎是没有意义的。
这种情况很少见，但您也可以拥有相反的情况，即单个引擎有多个元数据实例。当多个子组件共享同一数据库但使用不同的表名时，可能会发生这种情况。此外，当尝试将一个元数据实例中应用程序的当前架构与从服务器反映的另一个元数据实例进行比较时（例如出于架构迁移的目的），也可能会发生这种情况。这不会严格阻止您将它们中的每一个绑定到引擎，但确实有助于演示如何有效地存在多个元数据或引擎实例。

可能还有一些我现在无法想到的用例，但这应该能让您大致了解为什么它们在概念上彼此分离。

In fact, table definitions aren't linked to a specific database. The sqlalchemy metadata object, and all the attached objects (tables, columns, indexes, etc) define a completely abstract schema, without reference to any particular database. The engine is essentially three things: the connection info, the dialect, and a connection pool. The dialect is the important part here, it defines how to connect to a specific database, but more importantly with regards to your question, it also defines how to translate sqla's abstract schema objects (metadata et al) into sql commands specific to that database and driver.

This separation has a number of different uses:

Applications can define their schema at the module level, creating tables and metadata when the module is imported... but not bother defining an engine til the app actually is up and running (since knowing the full connection url & dialect usually requires reading a config file or getting user input first).
You can have the situation of multiple engines tied to the same set of metadata, such as a webapp where each user/password connects to the database with it's own credentials (Not that this is efficient, but sometimes needed for security purposes). Since metadata is a global object, it wouldn't make sense to ever bind it to a particular engine in this case.
It's rare, but you can also have the reverse case, multiple metadata instances for a single engine. This case can happen when multiple subcomponents are sharing the same database, but using different table names. Also, it can happen when trying to compare the app's current schema in one metadata instance against another which was reflected from the server (such as for the purpose of schema migration). This wouldn't strictly prevent you from binding each of them to the engine, but does help demonstrate how there can usefully be multiple metadata or engine instances.

There's probably some more use-cases I just can't think of right now, but that should give you the general idea of why they are conceptually separate from eachother.

回复收藏 0 原文

~没有更多了~