跨数据库之间的“连接”查询 - Oracle + PGsql

本文源自微信公众号《安费诺传感器学堂》
年后的小编在写一些代码，没有及时更新我们的公众号，干脆，先把其中涉及到数据库的的一些操作，尤其是不同数据库，多个表的“连接”查询的操作过程小结一下供各位参考。毕竟今天这个数据时代，大量数据的处理都离不开数据库这个工具。

说到数据库，它不仅是我们大量数据有序存储的地方，更是一种提供各种数据的汇总计算、组合统计的高效工具。换句话说，就是数据库存储数据不是主要目的，更重要的是怎么使用这些数据。

当我们的重要数据要存入数据库时，一般不会把所有的数据关联的特性都定义并放到一个表里，所以会存在多个表，而存在关联的表之间在查询数据时就会产生多表连接（join）查询。

举个书店和客户用数据库的简单栗子。比如客户的信息和客户的订单。

（1）从数据保存的角度来看，完全没必要在每个订单中把客户的详细信息再跟着书本订单进行重复保存（1是影响存储空间——在我们国家有些村的地址写下了的话，可以成一本书了，2是如果客户信息有调整时，不希望把所有的订单信息也要跟着改变），所以出现了两个表；

（2）从信息输出的角度而言，当我们要了解一个订单时，却希望同时知道客户和货物书本的详情。这个时候就需要同时用到两个表的数据，对于数据库的操作就要进行多表连接查询。

表-1：Customer

CustomerID	CustomerName
1	John Doe
2	Jane Doe
3	Max Smith

表-2：Order

OrderID	CustomerID	Book	Amount
2	1	Book1	200
3	2	Book2	150
4	3	Book3	300

那么，查询订单的详情，用sql实现的方式如下：

select A.CustomerName, B.book, B.Amount from
Customer A
Left join Order B
where A.CustomerID=B.CustomerID

刚刚的sql中用到的Left Join连接查询方式，是以表Customer为主。如果在Order中没有对应客户的CustomerID，那么对应Order部分的数据在输出结果中为空（NULL）。输出结果是：

CustomerName	Book	Amount
John Doe	Book1	200
Jane Doe	Book2	150
Max Smith	Book3	300
而如果上面sql用Right Join，那么以Order为主，输出的查询结果中Order部分都会输出，而Customer这部分字段如果没有对应的订单，则为空（NULL）。

如果使用Inner Join，则是以所有连接表都有对应记录的数据会随查询输出。简单的示意图如下所示。

简单的栗子如上所述。结合题目，我们提供一下这次的情景设定：

要整合两个数据库的数据，一个是Oracle，一个是Postgresql（PG）

在PG中保存了和货物相关的信息

前者只读，后者可读写，应用要求将Oracle中的查询结果和PG中的和货物相关的表的信息进行合并

在PG中和货物相关的两个表分别是书的作者(Author)，书的出版社(Publisher)

合并后的结果写入到PG的bookinfo表中

先要说明：两个独立的数据库之间是无法使用表之间的连接（join）查询的。我们直接提供解决方法：就是使用PG的临时表功能（其他数据库当然也有，物理的或者内存方式的）——TEMPLATE TABLE

基本的思路步骤如下：

查询Oracle中的数据

根据（1）的结果中的字段，在PG中建临时表

因为有了临时表，在PG中可以和其他的表进行连接查询

查询的结果再写入PG中

这里我们使用Python来实现相应的功能。示例中的表定义并不合理，大家关注的应该是实现过程。

• 查询Oracle的数据

import cx_Oracle    # Oracle的python扩展包
import psycopg2     # PostgreSQL数据库的适配器
from psycopg2 import extras

# Oracle connection & cursor
oracle_connection = cx_Oracle.connect('username', 'password', 'hostname:port/servicename')
oracle_cursor = oracle_connection.cursor()

oracle_query="""
select A.CustomerName, B.Goods, B.Amount
from Customer A Left join Order B
where A.CustomerID=B.CustomerID
"""

oracle_cursor.arraysize = 1000         # 准备多次读取
oracle_cursor.execute(oracle_query)    # Run query

# PG的连接和cursor
# 连接到 PostgreSQL 数据库
pgsql_conn = psycopg2.connect(database="mydatabase", user="myuser", password="mypassword", host="localhost", port="5432")
pgsql_cursor = pgsql_conn.cursor()  

# 准备好PG数据中插入数据的cursor和sql
# 假设pg的数据库中已经建好了需要的表，这个是为例避免在下面的示例代码中
# 的循环中出现这些需要额外的参数定义
#=======================================
pg_insert_query="""
"INSERT INTO book_info(
customername, bookname, amount, authorname, publishername)
VALUES (%s, %s, %s, %s, %s)"
"""
# 第二个PG数据库的cursor，用于数据插入操作，以区别于连接操作的cursor
pg_insert_cursor = pgsql_conn.cursor()

# 准备临时表删除用的sql
drop_table_query = "DROP TABLE IF EXISTS temp_table"
#=======================================

下面的操作都是在一个循环中完成，就不分段了。

在PG建临时表

• 将Oracle中查询的数据写入到PG的临时表
• PG内的多表连接查询（间接的方式与Oracle查询结果进行连接查询）
• 将查询结果写到PG的bookinfo表中

while True:
  # 通过 fetchmany 获取一批数据
  rows = oracle_cursor.fetchmany()
  if not rows:  #结束查询Oracle
    break
  else:
    # 表中的名称和字段特性的定义需要和后面的数据插入操作一致
    # 注意关键词：TEMPORARY TABLE
    pgsql_cursor.execute("""
      CREATE TEMPORARY TABLE temp_table(
      CustomerName type1,
      Book   type2,
      Amount type3
      """
      );
    #和平时建表时相同，需要提交,每轮操作建立临时表后还需要删除    
    pgsql_conn.commit()  
    
    # 将查询的Oracle中的数据写入临时表temp_table
    # extras的使用会更高效
    extras.execute_values(
        pgsql_cursor, 
        "INSERT INTO temp_table (CustomerName,Book,Amount) VALUES %s", 
        rows)
    # 提交变更
    pgsql_conn.commit()
    
    # PG中多表查询，包括temp_table
    SQL ="""
    SELECT temp_table.*, author.name, publisher.name
    from temp_table
    left join author on author.book = temp_table.book
    left join publisher on publisher.book = temp_table.book
    """
    
    pgsql_cursor.execute(SQL)
    # 循环读取连接查询的结果,并保存到PG的另外一个表中：bookinfo
    for rcd in pgsql_cursor:
      pg_insert_cursor.execute(pg_insert_query, (rcd ))
    
    # 在for循环结束后，提交数据的写入
    pgsql_conn.commit()
    
    # 删除临时表，准备下一次while循环
    pgsql_cursor.execute(drop_table_query )
    # 提交变更，以删除temp_table>>>注意提交对应的execute
    pgsql_conn.commit()  # 然后再进入下一个while循环

以上的表及表中定义仅为示例，实际数据库中会有更为复杂的关联。