使用Python从Server和Oracle进行内部连接查询

Question

假设我有这样的查询：

SELECT Oracle.Column1, SqlServer.Column1
FROM SqlServer
INNER JOIN Oracle
on SqlServer.ID = Oracle.ID

我已经使用Cx_Oracle和pyodbc在python中建立了到各自数据库的游标连接。单独的查询效果很好。子查询我可以做。但是，这个具有内部连接的查询需要连接到Oracle和Server，我迷路了。我如何打破这一点或以编程的方式处理这个问题？

我的当前版本涉及创建临时表，但这只适用于Server，而不是Oracle！目标是使用游标连接到它们各自的服务器(目前可以完成)，但是之后呢？

Answer 1

简单地说，您不能像运行本地表一样在远程数据库中运行查询。在数据库连接的每个游标中，其范围仅限于您指定的连接服务器、架构或目录/数据库。只有这些对象(表、函数/过程等)都是可用的。没有任何东西是被认可的。

尽管如此，如今大多数关系数据库都支持外部服务器连接，包括甲骨文的数据库链接、server的链接服务器、DB2的目录、Postgres的国外数据包装器、MySQL的联合存储引擎、MS Access‘链接表、SQLite的附附 (但只有其他SQLite数据库)。

因此，请考虑在Oracle或Server中建立远程连接，然后如果允许访问连接用户，则运行所需的JOIN查询。下面是连接和查询的简单示例。研究合适的连接参数。另外，请记住Oracle模式(只是用户)与Server模式(对象的命名空间容器)不同。

甲骨文

数据库链接是两个物理数据库服务器之间的连接，允许客户端作为一个逻辑数据库访问它们。

CREATE PUBLIC DATABASE LINK ... USING 'mssql_db';  -- TO BE RUN ONCE

SELECT o.Column1, s.Column1
FROM oracle_local_table o
INNER JOIN mssql_db.sql_server_remote_table s
  ON s.ID = o.ID

Server

链接服务器允许访问针对OLE数据源的分布式异构查询。创建链接服务器后，可以在此服务器上运行分布式查询，查询可以从多个数据源连接表。

EXEC master.dbo.sp_addlinkedserver           -- TO BE RUN ONCE
       @server = N'oracle_server', 
       @srvproduct=N'...', 
       @provider=N'...', 
       @provstr=N'...'

SELECT o.Column1, s.Column1
FROM oracle_server..user.oracle_remote_table o
INNER JOIN sql_server_local_table s
  ON s.ID = o.ID

-- ALTERNATIVELY
SELECT o.Column1, s.Column1
FROM OPENQUERY (oracle_server, 'SELECT * FROM scott.oracle_remote_table') o
INNER JOIN sql_server_local_table s
  ON s.ID = o.ID 

Python中的替代方法(如果上面的话)太难了，或者需要高级别的特权权限：

• 在任何一方创建临时表，并通过cursor.fetch和cursor.execute或cursor.executemany转储远程表记录，然后在本地运行JOIN查询。
• 将两个表导入SQLite数据库(磁盘或内存中的实例)并运行JOIN查询。注意:在Python3中，sqlite3是标准库的一部分，因此附带了Python安装。
• 使用Pythons的第三方模块熊猫将两个表导入数据帧并运行merge (与SQL的JOIN对应)。与金炼金术接口，熊猫甚至可以在一次调用中使用to_sql将数据帧转储到数据库。