使用 SQLAlchemy 连接两个数据库中的表

2021-12-17 00:00:00 python join sqlalchemy mysql multiple-databases

我正在使用两个 MySQL 数据库.我想在 SQLAlchemy 中将 DB 1 中的表与 DB2 中的表连接起来.

I am working with two MySQL Databases. I want to join a table from DB 1 with a table from DB2 in SQLAlchemy.

我在 sqlalchemy 中创建数据访问层时使用 automap_base 如下...

I am using automap_base while creating data access layer in sqlalchemy as follows...

class DBHandleBase(object):

    def __init__(self, connection_string='mysql+pymysql://root:xxxxxxx@localhost/services', pool_recycle=3600):
            self.Base_ = automap_base()
            self.engine_ = create_engine(connection_string,
                                         pool_recycle = pool_recycle)
            self.Base_.prepare(self.engine_, reflect=True)
            self.session_ = Session(self.engine_)

我的桌子类就像

class T1D1_Repo():


    def __init__(self, dbHandle):
        # create a cursor
        self.Table_ = dbHandle.Base_.classes.t1
        self.session_ = dbHandle.session_

我是这样加入的，

db1_handle = DB1_Handle()
db2_handle = DB2_Handle()
t1d1_repo = T1D1_Repo(handle)
t1d2_repo = T1D2_Repo(person_handle)

result = t1d1_repo.session_.query(
            t1d1_repo.Table_,
            t1d2_repo.Table_).join(t1d2_repo.Table_, (
                t1d1_repo.Table_.person_id
                == t1d2_repo.Table_.uuid))

我收到这样的错误:

sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1146, "Table 'db1.t1d2' doesn't exist") [SQL: 'SELECT

我们在数据库 db1 中创建了表 t1，在数据库 db2 中创建了表 t2.

we have created table t1 in database db1 and table t2 in database db2.

在 sqlalchemy ORM 中可以跨两个数据库表进行连接吗?如何实现?

Do join is possible across two databases table in sqlalchemy ORM? How to achieve that?

推荐答案

在 MySQL 数据库与模式同义.例如，在 Postgresql 中，您可以在数据库中的多个模式之间进行查询，但不能在数据库之间(直接)进行查询，您可以在 MySQL 中的多个数据库之间进行查询，因为两者之间没有区别.

In MySQL databases are synonymous with schemas. Where for example in Postgresql you can query between multiple schemas in a database, but not between databases (directly), you can query between multiple databases in MySQL as there's no distinction between the two.

有鉴于此，MySQL 中多数据库查询的一个可能解决方案是使用单个引擎、会话和 Base 处理您的架构并传递 schema 关键字参数 到您的表，或反映两种模式，以便它们'完全合格.

In this light a possible solution to your multi-database query in MySQL could be to use a single engine, session, and Base handling both your schemas and passing the schema keyword argument to your tables, or reflecting both schemas so that they're fully qualified.

由于我没有你的数据，我在名为 sopython 和 sopython2 的测试服务器上创建了 2 个模式(MySQL 数据库):

Since I don't have your data, I made 2 schemas (MySQL databases) on a test server called sopython and sopython2:

mysql> create database sopython;
Query OK, 1 row affected (0,00 sec)

mysql> create database sopython2;
Query OK, 1 row affected (0,00 sec)

并在每个中添加一个表格:

and added a table in each:

mysql> use sopython
Database changed
mysql> create table foo (foo_id integer not null auto_increment primary key, name text);
Query OK, 0 rows affected (0,05 sec)

mysql> insert into foo (name) values ('heh');
Query OK, 1 row affected (0,01 sec)

mysql> use sopython2
Database changed
mysql> create table bar (bar_id integer not null auto_increment primary key, foo_id integer, foreign key (foo_id) references `sopython`.`foo` (foo_id)) engine=InnoDB;
Query OK, 0 rows affected (0,07 sec)

mysql> insert into bar (foo_id) values (1);
Query OK, 1 row affected (0,01 sec)

在 Python 中:

In Python:

In [1]: from sqlalchemy import create_engine

In [2]: from sqlalchemy.orm import sessionmaker

In [3]: from sqlalchemy.ext.automap import automap_base

In [4]: Session = sessionmaker()

In [5]: Base = automap_base()

在不指定默认使用的架构(数据库)的情况下创建引擎:

Create the engine without specifying which schema (database) you use by default:

In [6]: engine = create_engine('mysql+pymysql://user:pass@:6603/')

In [7]: Base.prepare(engine, reflect=True, schema='sopython')

In [8]: Base.prepare(engine, reflect=True, schema='sopython2')
/home/user/SO/lib/python3.5/site-packages/sqlalchemy/ext/declarative/clsregistry.py:120: SAWarning: This declarative base already contains a class with the same class name and module name as sqlalchemy.ext.automap.foo, and will be replaced in the string-lookup table.
  item.__name__

警告是我不完全理解的东西，可能是2个表之间的外键引用导致重新反射foo的结果，但它似乎并没有引起麻烦.

The warning is something I don't fully understand, and is probably a result of the foreign key reference between the 2 tables causing re-reflecting of foo, but it does not seem to cause trouble.

警告是第二次调用 prepare() 重新创建和替换第一次调用中反映的表的类的结果.避免这一切的方法是首先使用元数据反映两个模式中的表，然后准备:

The warning is the result of the second call to prepare() recreating and replacing the classes for the tables reflected in the first call. The way to avoid all that is to first reflect the tables from both schemas using the metadata, and then prepare:

Base.metadata.reflect(engine, schema='sopython')
Base.metadata.reflect(engine, schema='sopython2')
Base.prepare()

<小时>

毕竟你可以查询加入 foo 和 bar:

After all this you can query joining foo and bar:

In [9]: Base.metadata.bind = engine

In [10]: session = Session()

In [11]: query = session.query(Base.classes.bar).
    ...:     join(Base.classes.foo).
    ...:     filter(Base.classes.foo.name == 'heh')

In [12]: print(query)
SELECT sopython2.bar.bar_id AS sopython2_bar_bar_id, sopython2.bar.foo_id AS sopython2_bar_foo_id 
FROM sopython2.bar INNER JOIN sopython.foo ON sopython.foo.foo_id = sopython2.bar.foo_id 
WHERE sopython.foo.name = %(name_1)s

In [13]: query.all()
Out[13]: [<sqlalchemy.ext.automap.bar at 0x7ff1ed7eee10>]

In [14]: _[0]
Out[14]: <sqlalchemy.ext.automap.bar at 0x7ff1ed7eee10>

In [15]: _.foo
Out[15]: <sqlalchemy.ext.automap.foo at 0x7ff1ed7f09b0>

相关文章