# de04 資料庫連接操作: 作業

這個作業主要是要讓學員學習如何連接到關聯型資料庫並進行一些讀取與寫入的操作。同時為讓學員去思考, 將數據寫入到資料庫與寫入到BlobStorage服務的差別, 以及將數據從資料庫讀取與從BlobStorage服務來讀取在日常的數據ETL的設計上該如何去構思。

## 計算某一個Task所花費的時間

在許多的運算過程,為了了解可能的性能的瓶頸或優化的方向, 有時會在程式裡埋蔵一些幫助profiling的程式碼。如果要了解某一段區塊所執行的時間, 以下是一小段範例:

import time # 載入 time 模組

start_time = time.time()
print(f'Task execution [START]: {start_time}')



# your script --> start
t = 4

time.sleep(t) # 我們用sleep()來模擬程式運算的執行, 參數t是秒數

# your script --> end


end_time = time.time()
print(f'Task execution [END]: {end_time}')

# calcuate Task execution duriation
elapsed_time = end_time - start_time
elapsed = time.strftime("%H:%M:%S", time.gmtime(elapsed_time))
print(f"Total eclapsed duration: {elapsed}")

Task execution [START]: 1665970675.3717315
Task execution [END]: 1665970679.3721182
Total eclapsed duration: 00:00:04

以下的作業會使用到以下的範例資料。

**範例資料庫**: DVD rental database

說明: DVD出租數據庫代表DVD出租商店的業務流程。 DVD出租數據庫具有許多資料表，包括：
* 15 tables
* 1 trigger
* 7 views
* 8 functions
* 1 domain
* 13 sequences

**DVD Rental ER Model**


使用SQLAlchemy連接的範例:

from sqlalchemy import create_engine

# create engine to connect Postgresql
pg_engine_source = create_engine("postgresql+psycopg2://dxlab:wistron888@10.34.124.114/dvdrental")

## 作業#01

請使用DBAPI來連結到這個範例資料庫, 並請計算出15個資料表每一個資料表的Record Count的筆數。

#task01
import psycopg2

# all tables
tables = ['category', 'film_category', 'film', 'language', 'film_actor', 'inventory', 
          'rental', 'payment', 'staff', 'actor', 'customer', 'address', 'city', 'country', 'store']

results = {
    'category':0, 'film_category':0, 'film':0, 'language':0, 'film_actor':0, 'inventory':0, 
          'rental':0, 'payment':0, 'staff':0, 'actor':0, 'customer':0, 'address':0, 'city':0, 'country':0, 'store':0
}

db_conn = psycopg2.connect(host='active.deacademydev.service.paas.wistron.com', dbname='dvdrental', user='dxlab', password='wistron888', port='15067')
print('Connect [postgres] database successfully!')

# write your code below

# Open a cursor to perform database operations
cur = db_conn.cursor()

for table in tables:
    # 執行你的SQL
    
    
    
db_conn.close()
# print out the final result
print(results)

## 作業#02

請使用DBAPI來連結到這個範例資料庫, 並且執行一個Left-join結果的Query然後把結果存成一個CSV檔(`task2.csv`)。
CSV的檔案必需要包含欄位名稱(column in 1st row), 欄位之間以","來分隔。並且把這個檔案上傳到本地(與Python檔同目錄)。

**CSV導出參考**: https://gist.github.com/madan712/f27ac3b703a541abbcd63871a4a56636

SELECT
    film.film_id,
    title,
    inventory_id
FROM
    film
LEFT JOIN inventory 
    ON inventory.film_id = film.film_id
ORDER BY title;


**SQL語法參考**: https://www.postgresqltutorial.com/postgresql-left-join/

#task02
import psycopg2

stmt = """
SELECT
    film.film_id,
    title,
    inventory_id
FROM
    film
LEFT JOIN inventory 
    ON inventory.film_id = film.film_id
ORDER BY title;
"""

db_conn = psycopg2.connect(host='active.deacademydev.service.paas.wistron.com', dbname='dvdrental', user='dxlab', password='wistron888', port='15067')
print('Connect [postgres] database successfully!')

# write your code below




    
db_conn.close()