sqlalchemy 现有数据库查询 [英] sqlalchemy existing database query

问题描述

我使用 SQLAlchemy 作为 Python 项目的 ORM.我创建了几个模型/模式，它工作正常.现在我需要查询一个现有的 MySQL 数据库，没有插入/更新只是 select 语句.

I am using SQLAlchemy as ORM for a python project. I have created few models/schema and it is working fine. Now I need to query a existing MySQL database, no insert/update just the select statement.

如何围绕现有数据库的表创建包装器?我已经简要浏览了 sqlalchemy 文档和 SO，但找不到任何相关内容.所有都建议执行方法，我需要在其中编写原始 sql 查询，而我想以与使用 SA 模型相同的方式使用 SQLAlchemy 查询方法.

How can I create a wrapper around the tables of this existing database? I have briefly gone through the sqlalchemy docs and SO but couldn't find anything relevant. All suggest execute method, where I need to write the raw sql queries, while I want to use the SQLAlchemy query method in same way as I am using with the SA models.

例如，如果现有数据库具有表名 User，那么我想使用 dbsession 查询它(只有选择操作，可能带有连接)

For example if the existing db has table name User then I want to query it using the dbsession ( only the select operation, probably with join)

推荐答案

你似乎有一个印象，SQLAlchemy 只能使用 SQLAlchemy 创建的数据库结构(可能使用 MetaData.create_all()) - 这是不正确的.SQLAlchemy 可以与预先存在的数据库完美配合，您只需要定义模型以匹配数据库表.一种方法是使用反射，正如 Ilja Everilä 建议的那样:

You seem to have an impression that SQLAlchemy can only work with a database structure created by SQLAlchemy (probably using MetaData.create_all()) - this is not correct. SQLAlchemy can work perfectly with a pre-existing database, you just need to define your models to match database tables. One way to do that is to use reflection, as Ilja Everilä suggests:

class MyClass(Base):
    __table__ = Table('mytable', Base.metadata,
                    autoload=True, autoload_with=some_engine)

(在我看来，这对于一次性脚本来说完全没问题，但如果数据库结构有可能随时间发生变化，则可能会导致真实"应用程序中出现令人难以置信的令人沮丧的错误)

(which, in my opinion, would be totally fine for one-off scripts but may lead to incredibly frustrating bugs in a "real" application if there's a potential that the database structure may change over time)

另一种方法是像往常一样简单地定义模型，注意定义模型以匹配数据库表，这并不难.这种方法的好处是您只能将数据库表的一个子集映射到模型，甚至只能将表列的子集映射到模型的字段.假设您在数据库中有 10 个表，但只对 users 表感兴趣，您只需要 id、name 和 email字段:

Another way is to simply define your models as usual taking care to define your models to match the database tables, which is not that difficult. The benefit of this approach is that you can map only a subset of database tables to you models and even only a subset of table columns to your model's fields. Suppose you have 10 tables in the database but only interested in users table from where you only need id, name and email fields:

class User(Base):
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String)
    email = sa.Column(sa.String)

(注意我们不需要定义一些只需要发出正确 DDL 的细节，例如 String 字段的长度或 email 字段具有索引的事实)

(note how we didn't need to define some details which are only needed to emit correct DDL, such as the length of the String fields or the fact that the email field has an index)

除非您在代码中创建或修改模型，否则 SQLAlchemy 不会发出 INSERT/UPDATE 查询.如果您想确保您的查询是只读的，您可以在数据库中创建一个特殊用户并仅授予该用户 SELECT 权限.或者/此外，您还可以尝试在应用程序代码中回滚事务.

SQLAlchemy will not emit INSERT/UPDATE queries unless you create or modify models in your code. If you want to ensure that your queries are read-only you may create a special user in the database and grant that user SELECT privileges only. Alternatively/in addition, you may also experiment with rolling back the transaction in your application code.

这篇关于sqlalchemy 现有数据库查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！