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

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

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

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

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

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

表-1：Customer

表-2：Order

那么，查詢(xún)訂單的詳情，用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連接查詢(xún)方式，是以表Customer為主。如果在Order中沒(méi)有對(duì)應(yīng)客戶(hù)的CustomerID，那么對(duì)應(yīng)Order部分的數(shù)據(jù)在輸出結(jié)果中為空（NULL）。輸出結(jié)果是：

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

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

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

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

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

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

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

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

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

基本的思路步驟如下：

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

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

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

查詢(xún)的結(jié)果再寫(xiě)入PG中

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

查詢(xún)Oracle的數(shù)據(jù)

importcx_Oracle#Oracle的python擴(kuò)展包
importpsycopg2#PostgreSQL數(shù)據(jù)庫(kù)的適配器
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ù)庫(kù)
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ù)庫(kù)中已經(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ù)庫(kù)的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中查詢(xún)的數(shù)據(jù)寫(xiě)入到PG的臨時(shí)表

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

將查詢(xún)結(jié)果寫(xiě)到PG的bookinfo表中

whileTrue:
  # 通過(guò) fetchmany 獲取一批數(shù)據(jù)
  rows = oracle_cursor.fetchmany()
ifnotrows:#結(jié)束查詢(xún)Oracle
    break
else:
#表中的名稱(chēng)和字段特性的定義需要和后面的數(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()

#將查詢(xún)的Oracle中的數(shù)據(jù)寫(xiě)入臨時(shí)表temp_table
#extras的使用會(huì)更高效
  extras.execute_values(
        pgsql_cursor, 
"INSERTINTOtemp_table(CustomerName,Book,Amount) VALUES %s", 
rows)
    # 提交變更
    pgsql_conn.commit()
    
#PG中多表查詢(xún)，包括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)讀取連接查詢(xún)的結(jié)果,并保存到PG的另外一個(gè)表中：bookinfo
for rcd in pgsql_cursor:
pg_insert_cursor.execute(pg_insert_query,(rcd))

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

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

審核編輯：劉清