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

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

當(dāng)我們的重要數(shù)據(jù)要存入數(shù)據(jù)庫時，一般不會把所有的數(shù)據(jù)關(guān)聯(lián)的特性都定義并放到一個表里，所以會存在多個表，而存在關(guān)聯(lián)的表之間在查詢數(shù)據(jù)時就會產(chǎn)生多表連接（join）查詢。

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

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

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

表-1：Customer

表-2：Order

那么，查詢訂單的詳情，用sql實現(xiàn)的方式如下：

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

剛剛的sql中用到的Left Join連接查詢方式，是以表Customer為主。如果在Order中沒有對應(yīng)客戶的CustomerID，那么對應(yīng)Order部分的數(shù)據(jù)在輸出結(jié)果中為空（NULL）。輸出結(jié)果是：

而如果上面sql用Right Join，那么以O(shè)rder為主，輸出的查詢結(jié)果中Order部分都會輸出，而Customer這部分字段如果沒有對應(yīng)的訂單，則為空（NULL）。

如果使用Inner Join，則是以所有連接表都有對應(yīng)記錄的數(shù)據(jù)會隨查詢輸出。簡單的示意圖如下所示。

簡單的栗子如上所述。結(jié)合題目，我們提供一下這次的情景設(shè)定：

要整合兩個數(shù)據(jù)庫的數(shù)據(jù)，一個是Oracle，一個是Postgresql（PG）

在PG中保存了和貨物相關(guān)的信息

前者只讀，后者可讀寫，應(yīng)用要求將Oracle中的查詢結(jié)果和PG中的和貨物相關(guān)的表的信息進行合并

在PG中和貨物相關(guān)的兩個表分別是書的作者(Author)，書的出版社(Publisher)

合并后的結(jié)果寫入到PG的bookinfo表中

先要說明：兩個獨立的數(shù)據(jù)庫之間是無法使用表之間的連接（join）查詢的。我們直接提供解決方法：就是使用PG的臨時表功能（其他數(shù)據(jù)庫當(dāng)然也有，物理的或者內(nèi)存方式的）——TEMPLATE TABLE

基本的思路步驟如下：

查詢Oracle中的數(shù)據(jù)

根據(jù)（1）的結(jié)果中的字段，在PG中建臨時表

因為有了臨時表，在PG中可以和其他的表進行連接查詢

查詢的結(jié)果再寫入PG中

這里我們使用Python來實現(xiàn)相應(yīng)的功能。示例中的表定義并不合理，大家關(guān)注的應(yīng)該是實現(xiàn)過程。

查詢Oracle的數(shù)據(jù)

importcx_Oracle#Oracle的python擴展包
importpsycopg2#PostgreSQL數(shù)據(jù)庫的適配器
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 數(shù)據(jù)庫
pgsql_conn = psycopg2.connect(database="mydatabase", user="myuser", password="mypassword", host="localhost", port="5432")
pgsql_cursor = pgsql_conn.cursor()  


#準備好PG數(shù)據(jù)中插入數(shù)據(jù)的cursor和sql
#假設(shè)pg的數(shù)據(jù)庫中已經(jīng)建好了需要的表，這個是為例避免在下面的示例代碼中
#的循環(huán)中出現(xiàn)這些需要額外的參數(shù)定義
#=======================================
pg_insert_query="""
"INSERTINTObook_info(
customername,bookname,amount,authorname,publishername)
VALUES(%s,%s,%s,%s,%s)"
"""
#第二個PG數(shù)據(jù)庫的cursor，用于數(shù)據(jù)插入操作，以區(qū)別于連接操作的cursor
pg_insert_cursor = pgsql_conn.cursor()


#準備臨時表刪除用的sql
drop_table_query = "DROP TABLE IF EXISTS temp_table"
#=======================================

下面的操作都是在一個循環(huán)中完成，就不分段了。

在PG建臨時表

將Oracle中查詢的數(shù)據(jù)寫入到PG的臨時表

PG內(nèi)的多表連接查詢（間接的方式與Oracle查詢結(jié)果進行連接查詢）

將查詢結(jié)果寫到PG的bookinfo表中

whileTrue:
  # 通過 fetchmany 獲取一批數(shù)據(jù)
  rows = oracle_cursor.fetchmany()
ifnotrows:#結(jié)束查詢Oracle
    break
else:
#表中的名稱和字段特性的定義需要和后面的數(shù)據(jù)插入操作一致
# 注意關(guān)鍵詞：TEMPORARY TABLE
  pgsql_cursor.execute("""
      CREATE TEMPORARY TABLE temp_table(
      CustomerName type1,
      Book   type2,
      Amount type3
      );
    #和平時建表時相同，需要提交,每輪操作建立臨時表后還需要刪除
pgsql_conn.commit()

#將查詢的Oracle中的數(shù)據(jù)寫入臨時表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)
#循環(huán)讀取連接查詢的結(jié)果,并保存到PG的另外一個表中：bookinfo
for rcd in pgsql_cursor:
pg_insert_cursor.execute(pg_insert_query,(rcd))

#在for循環(huán)結(jié)束后，提交數(shù)據(jù)的寫入
    pgsql_conn.commit()
    
#刪除臨時表，準備下一次while循環(huán)
pgsql_cursor.execute(drop_table_query )
#提交變更，以刪除temp_table>>>注意提交對應(yīng)的execute
pgsql_conn.commit()#然后再進入下一個while循環(huán)

以上的表及表中定義僅為示例，實際數(shù)據(jù)庫中會有更為復(fù)雜的關(guān)聯(lián)。

審核編輯：劉清