Jenny's Website

Hi all! Today I gonna let you know how to import and save data from Netezza DB to Python :)

** What is Netezza?
Netezza is a global company in the DW appliance market. In addition, they introduced an all-in-one DW appliance product that integrates server, storage, and database into one dedicated device for the first time.

You can check more information with following link.
[About Netezza][link1]

Here is the Python codes below:

-- netezza to python

import pyodbc
import numpy as np
import os
import pandas as pd
from pandas import DataFrame as df

# pyodbc connection string
conn = pyodbc.connect("DRIVER={NetezzaSQL}; SERVER=00.00.00.000; PORT=0000; DATABASE=DBNAME; UID=MYID; PWD=MYPWD; ")

# Define Cursor
curs = conn.cursor()

# Execute SQL statement and store result in cursor
curs.execute("select * from TABLENAME") 
result = curs.fetchall() # So, select * from TABLENAME, so result means the entire output of the table

# print(result[0]) #table을 출력할 때는 print 함수를 써야한다 

# Create YN code cross table  
# TBU - variable YN will be modified as automatically
table = "TABLENAME" -- Table name to which YMD, YN variables belong
year = "YMD" -- Variable in the format YYYY.MM.DD
var1 = "YN" -- Categorical variable that outputs the result as Y or N


# import data from netezzaDB with SQL query and Python function 
df = pd.read_sql_query("select " + var1+" as var, substr(" + year + ",1,4) as year from "+table, conn)
df

tb1=pd.crosstab(df.VAR, df.YEAR, margins=True).round() -- round(): No decimal place
tb1

tb2=pd.crosstab(df.VAR, df.YEAR, normalize='columns', margins=True).round(2) -- round(2): 소수점 2자리
tb2

-- combine table1, 2 
TB = pd.concat([tb1,tb2], axis=0)
TB

-- save location 
NAME = 'Z:/jennyujin/'+var1+'.xlsx'

-- save as excel file 
TB.to_excel(NAME, sheet_name = var1)