[Python pandas] DB에 접속해서 데이터 불러오기 (DB connection and SQL query)

이번 포스팅에서는 Python pandas 로 다양한 종류의 DB에 접속해서 SQL query로 Data를 select 해서 Python pandas의 DataFrame 으로 불러오는 방법을 소개하도록 하겠습니다.

 

만약 DB에 Python으로 직접 접속하지 않는 다면 DB에 있는 Data를 이용하기 위해서는 (1) DB에서 csv 파일로 데이터 exporting 하기, (2) csv 파일을 Python pandas 의 pd.csv_read() 로 불러오기의 두 단계의 절차를 거쳐야 합니다. (지난번 포스팅에서 client PC에 있는 text 혹은 csv 파일을 불러오는 방법을 소개했었습니다. )

 

이번 포스팅의 DB connection & SQL Query in Python 방법을 사용하면 csv 파일로 내리고 다시 불러오는 단계가 줄어들기 때문에 좀더 편리하게 DB로 부터 Data 불러오기를 할 수 있을 것입니다.

 

 

 

 

Oracle DB, IBM DB2, PostgreSQL, MariaDB 의 순서대로 소개하겠습니다.

사용자 정의 함수의 query 부분에 사용하고자 하는 SQL query 를 작성해서 실행하면 됩니다.

 

 

1. Oracle DB에 Python으로 접속하여 SQL query 해서 pandas DataFrame 만들기

 

명령 프롬프트 창에서 cx_Oracle 라이브러리를 설치합니다. 

$ pip install cx_Oracle

다음으로, 컴퓨터에서 tnsnames.ora 파일에서 연결 정보(connection information)을 찾습니다. 

아래 예시에서 색깔 칠한 부분에서 Host Name, Port Number, Service Name을 찾을 수 있습니다. 

SYSTEM_OCON = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Host Name)(PORT = Port Number)) (LOAD_BALANCE = YES) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = Service Name) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5)

(* Reference : How to Connect Python to an Oracle Database using cx_Oracle)

이제 준비가 되었으니 Oracle DB에 Python으로 접속해서 Query를 실행시키고, 조회 결과를 Pandas DataFrame으로 가져와서 저장하는 사용자 정의함수를 정의해보겠습니다. 

##-- User defined function for Oracle DB SQL query

 

def query_OracleSQL(query):
     
     import pandas as pd
     import cx_Oracle as co
     from datetime import datetime

     start_tm = datetime.now()

     #  DB Connecion
     dsn_tns = co.makedsn("Host Name", "Port Number", service_name="Service Name")
     conn = co.connect(user="User Name", password="Personal Password", dsn=dnsStr)

     # Get a dataframe
     query_result = pd.read_sql(query, conn)

     # Close connection

     conn.close()

     end_tm = datetime.now()

     print('START: ', str(start_tm))
     print('END: ', str(end_tm))
     print('ELAP: ', str(end_tm - start_tm))

     return query_result

 

 

##-- SQL query
query = """
     SELECT var1, var2, ymd, count(*) as cnt
          FROM myOracleDB
          WHERE ymd BETWEEN
               to_date('2016-11-22T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')
               AND
               to_date('2016-11-22T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS')
GROUP BY var1

ORDER BY cnt

               """

##-- Excute OracleDB SQL in Python

query_OracleSQL(query)

 

 

 

 

2. IBM DB2에 Python으로 접속하여 SQL query 해서 pandas DataFrame 만들기

 

##-- User defined function for IBM DB2 SQL query

def query_DB2(query):

     import sqlalchemy as sa
     import pandas as pd
     from datetime import datetime

     # DB Connetion

     engine = sa.create_engine('ibm_db_sa://xx(id):xx(pw)@xx.xx.xxx.xx(ip):xxxx(port)/xxx(DB)', echo=False)

     conn = engineconnect()
     start_tm = datetime.now()

     # Get a dataframe
     execonn = engine.execute(query)

     query_result = df(execonn.fetchall())
     query_result.columns = execonn.keys()

     # Close connection

     end_tm = datetime.now() 
     print('START: ', str(start_tm))

     print('END: ', str(end_tm))
     print('ELAP: ', str(end_tm - start_tm))

     conn.close()

     return query_result

##-- SQL query
query = """
     SELECT var1, var2, ymd, count(*) as cnt
          FROM IBMDB2_DB
          WHERE ymd = "2016-11-22" 
 """

##-- Excute IBMDB2 SQL in Python

query_DB2(query)

 

 

 

 

3. PostgreSQL에 Python으로 접속하여 SQL query 해서 pandas DataFrame 만들기

 

##-- User defined function for PostgreSQL DB SQL query

def query_postgreSQL(query):

     import pandas as pd
     import psycopg2 as pg
     from datetime import datetime

    

  

    # DB Connection
     conn = pg.connect(host='xx.xxx.xxx.xx',
                                        port='xxxx',
                                        dbname='xxx',
                                        user='xxxx',
                                        password='xxxx')
     start_tm = datetime.now()

     # Get a DataFrame

     query_result = pd.read_sql(query, conn)

 

     # Close connection
     end_tm = datetime.now()

     print('START: ', str(start_tm))
     print('END: ', str(end_tm))
     print('ELAP: ', str(end_tm - start_tm))

     conn.close()

 

     return query_result

##-- SQL query
query = """
     SELECT var1, var2, ymd, count(*) as cnt
          FROM PostgreSQL_DB
          WHERE ymd = "2016-11-22" 
 """

##-- Excute PostgreSQL SQL in Python

query_postgreSQL(query)

 

 

 

4. MariaDB에 Python으로 접속하여 SQL query 해서 pandas DataFrame 만들기

 

##-- User defined function for MariaDB SQL query

def query_MariaDB(query):

     import pandas as pd
     import pymysql
     from datetime import datetime

     # DB Connection
     conn = pymysql.connect(host='xx.xxx.xxx.xxx', port=xxxx,
          user='xxxx'
          password='xxxxx'
          database='xxxxx')

     # start time
     start_tm = datetime.now()

     # Get a DataFrame
     global query_result

     query_result = pd.read_sql(query, conn)

 

     # Close connection
     end_tm = datetime.now()

     print('START TIME : ', str(start_tm))
     print('END TIME : ', str(end_tm))
     print('ELAP time :', str(end_tm - start_tm))
     conn.close()

     return query_result

 

##-- SQL query
query = """
     SELECT var1, var2, ymd, count(*) as cnt
          FROM MariaDB
          WHERE ymd = "2016-11-22" 
 """

##-- Excute PostgreSQL SQL in Python

query_postgreSQL(query)

 

 

많은 도움 되었기를 바랍니다.

 

• For R user : R로 Hive 접속해서 데이터 불러오기 (Hive connection by R)
                  ☞ http://rfriend.tistory.com/232