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

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

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

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

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

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

表-1：Customer

表-2：Order

那么，查詢訂單的詳情，用sql實(shí)現(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è)定：

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

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

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

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

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

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

基本的思路步驟如下：

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

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

因?yàn)橛辛伺R時(shí)表，在PG中可以和其他的表進(jìn)行連接查詢

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

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

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

importcx_Oracle#Oracle的python擴(kuò)展包
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       # 準(zhǔn)備多次讀取
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()  


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


#準(zhǔn)備臨時(shí)表刪除用的sql
drop_table_query = "DROP TABLE IF EXISTS temp_table"
#=======================================

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

在PG建臨時(shí)表

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

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

將查詢結(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
      );
    #和平時(shí)建表時(shí)相同，需要提交,每輪操作建立臨時(shí)表后還需要?jiǎng)h除
pgsql_conn.commit()

#將查詢的Oracle中的數(shù)據(jù)寫入臨時(shí)表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的另外一個(gè)表中：bookinfo
for rcd in pgsql_cursor:
pg_insert_cursor.execute(pg_insert_query,(rcd))

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

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

審核編輯：劉清