年后的小編在寫一些代碼，沒有及時更新我們的公眾號，干脆，先把其中涉及到數據庫的的一些操作，尤其是不同數據庫，多個表的“連接”查詢的操作過程小結一下供各位參考。畢竟今天這個數據時代，大量數據的處理都離不開數據庫這個工具。

說到數據庫，它不僅是我們大量數據有序存儲的地方，更是一種提供各種數據的匯總計算、組合統計的高效工具。換句話說，就是數據庫存儲數據不是主要目的，更重要的是怎么使用這些數據。

當我們的重要數據要存入數據庫時，一般不會把所有的數據關聯的特性都定義并放到一個表里，所以會存在多個表，而存在關聯的表之間在查詢數據時就會產生多表連接（join）查詢。

舉個書店和客戶用數據庫的簡單栗子。比如客戶的信息和客戶的訂單。

（1）從數據保存的角度來看，完全沒必要在每個訂單中把客戶的詳細信息再跟著書本訂單進行重復保存（1是影響存儲空間——在我們國家有些村的地址寫下了的話，可以成一本書了，2是如果客戶信息有調整時，不希望把所有的訂單信息也要跟著改變），所以出現了兩個表；

（2）從信息輸出的角度而言，當我們要了解一個訂單時，卻希望同時知道客戶和貨物書本的詳情。這個時候就需要同時用到兩個表的數據，對于數據庫的操作就要進行多表連接查詢。

表-1：Customer

表-2：Order

那么，查詢訂單的詳情，用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）。輸出結果是：

而如果上面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的數據

importcx_Oracle#Oracle的python擴展包
importpsycopg2#PostgreSQL數據庫的適配器
frompsycopg2 import extras


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


oracle_query="""
selectA.CustomerName,B.Goods,B.Amount
fromCustomerALeftjoinOrderB
whereA.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="""
"INSERTINTObook_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表中

whileTrue:
  # 通過 fetchmany 獲取一批數據
  rows = oracle_cursor.fetchmany()
ifnotrows:#結束查詢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, 
"INSERTINTOtemp_table(CustomerName,Book,Amount) VALUES %s", 
rows)
    # 提交變更
    pgsql_conn.commit()
    
#PG中多表查詢，包括temp_table
SQL="""
SELECTtemp_table.*,author.name,publisher.name
fromtemp_table
leftjoinauthoronauthor.book=temp_table.book
leftjoinpublisheronpublisher.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循環

以上的表及表中定義僅為示例，實際數據庫中會有更為復雜的關聯。

審核編輯：劉清