使用SQLAlchemy连接两个数据库中的表 [英] Join tables in two databases using SQLAlchemy
问题描述
我正在使用两个MySQL数据库.我想将DB1中的表与SQLAlchemy中DB2中的表连接起来.
I am working with two MySQL Databases. I want to join a table from DB 1 with a table from DB2 in SQLAlchemy.
我正在使用automap_base，同时在sqlalchemy中创建数据访问层，如下所示...
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?
推荐答案
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处理您的模式并传递
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 [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__
警告是我不完全理解的，可能是两个表之间的外键引用导致重新反射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>
这篇关于使用SQLAlchemy连接两个数据库中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
