'PostgreSQL DB connect in Python'에 해당되는 글 1건

  1. 2019.07.02 [Python] Python으로 Postgresql, GPDB, DB2, Presto DB connect 하는 방법 (2)

이번 포스팅에서는 Windows10 OS 에서

(1) Python으로 Postgresql, Greenplum DB connect 하고 Query 결과 가져오는 방법

(2) Python으로 MySQL DB connect 하고 Query 결과 가져오는 방법

(3) Python으로 IBM DB2 DB connect 하고 Query 결과 가져오는 방법

(4) Python으로 Presto, Hive DB connect 하고 Query 결과 가져오는 방법

을 소개하겠습니다.

 

 (1) Python으로 Postgresql, Greenplum DB connect 하고 Query 결과 가져오는 방법

먼저, 명령 프롬프트 창에서 psycopg2 라이브러리를 설치해줍니다.

 $ pip install psycopg2

Spyder 등의 Python IDE에서 PostgreSQL, Greenplum DB에 접속하고 query를 실행하여 결과를 pandas DataFrame으로 받아와서 저장하는 사용자 정의함수를 정의합니다.

( * Reference : PostgreSQL Python: Connect to PostgreSQL Database Server )

 

[ UDF of connecting to Postgresql, GPDB & Getting query result as a DataFrame ]

def postgresql_query(query): 

    import psycopg2 as pg
    import pandas as pd

    # Postgresql, Greenplum DB Connect
    connection_string = "postgresql://{user}:{password}@{host}/{db}".\
        format(user='gpadmin',  # put your info
                 password='changeme', 
                 host='localhost', 
                 db='gpadmin')

    conn = pg.connect(connection_string)

    cursor = conn.cursor()

    #conn.autocommit = True

    # execute a query and get it as a pandas' DataFrame
    cursor.execute(query)
    col_names = [desc[0] for desc in cur.description]
    result = pd.DataFrame(cur.fetchall(), columns=col_names)

   cursor.close()

   conn.close()


    return result


 

아래는 Query를 실행해서 결과를 가져오는 간단한 예시입니다.

query = """
    SELECT * FROM mytable WHERE grp == 'A' LIMIT 100;

    """

postgresql_query(query)grp_A = postgresql_query(query)

 

 

 (2) Python으로 MySQL DB connect 하고 Query 결과 가져오는 방법

먼저, 명령 프롬프트 창에서 mysql 라이브러리를 설치해줍니다.

$ pip install mysql


다음으로 MySQL DB에 접속하고 query를 실행시켜서 결과를 DataFrame으로 가져오는 사용자 정의함수를 정의합니다.

( * Reference : Connecting to MySQL Using Connector/Python )


def mysql_query(query):

    import mysql.connector

    import pandas as pd


    cnx = mysql.connector.connect(user='userid',

                                             password='changeme',

                                             host='12.34.567.890',

                                             database='mydb')

 

    cursor = cnx.cursor()

 

     # execute a query and get it as a pandas' DataFrame
     cursor.execute(query)
     col_names = [desc[0] for desc in cur.description]
     result = pd.DataFrame(cur.fetchall(), columns=col_names)

 

    cursor.close()

    cnx.close()

 

    return result

 


위에서 정의한 사용자 정의함수를 사용하여 MySQL DB에 접속하고, Query로 조회한 결과를 result 라는 이름의 DataFrame으로 저장하는 예시입니다.

 

query = """

    SELECT * FROM mydb WHERE age >= 20 ORDER BY age;

    """

 

result = mysql_query(query)

 

 

 (3) Python으로 IBM DB2 DB connect 하고 Query 결과 가져오는 방법

먼저, 명령 프롬프트 창에서 ibm_db_dbi 라이브러리를 설치해줍니다.

$ pip install ibm_db_dbi


다음으로 DB2에 접속해서 Query를 실행하고, 결과를 pandas DataFrame으로 가져오는 사용자 정의함수를 정의합니다.

( * Reference : Connecting to an IBM database server in Python)


def db2_query(query):
    

    import ibm_db_dbi as db

    import pandas as pd

    conn = db.connect('DATABASE=mydb;' 
                             'HOSTNAME=12.34.567.890;' 
                             'PORT=50000;' 
                             'PROTOCOL=TCPIP;' 
                             'UID = secret;' 
                             'PWD= changeme;', '', ' ')

     cursor = conn.cursor()
     cursor.execute(query)
     col_names = [desc[0] for desc in cursor.description]

     result = pd.DataFrame(cursor.fetchall(), columns=col_names)

     cursor.close()
     conn.close()
 
     return result

 

 

Python에서 Query를 실행시켜서 결과를 pandas DataFrame을 가져오는 예시는 아래와 같습니다.

query = """

    SELECT school_nm, count(*) as student_cnt

    FROM school

    WHERE school_nm LIKE 'seoul%';

    """

 

school = db2_query(query)

 

 

 (4) Python으로 Presto, Hive DB connect 하고 Query 결과 가져오는 방법

먼저 명령 프롬프트 창에서 pyhive 라이브러리를 설치해줍니다.

$ pip install pyhive


Presto 혹은 Hive에 접속하고 Query를 실행해서 결과를 pandas DataFrame으로 가져오는 사용자 정의함수를 정의합니다.

( * Reference : PyHive is a collection of Python DB-API and SQLAlchemy interfaces for Presto and Hive  )

 


def presto_query(query):

     from pyhive import presto
     import pandas as pd

     cursor = presto.connect('12.34.567.890').cursor()
    

     # execute a query and get a result as a DataFrame

     cursor.execute(query)
     col_names = [ desc[0] for desc in cursor.description ]
     result = pd.DataFrame(cursor.fetchall(), columns=col_names)

     cursor.close()
 
     return result

 


Python에서 위의 사용자 정의 함수를 사용하여 query를 실행시키고 결과를 DataFrame으로 가져오는 예제입니다.

 

query = """

    WITH

        t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),

        t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)

    SELECT t1.*, t2.* FROM t1 JOIN t2 ON t1.a = t2.a;

    """

result = presto_query(query)

 

 

혹시 pip install 하는 단계에서 'error: Microsoft Visual C++ 14.0 is required. Get it with "Microsoft Visual C++ Build Tools": https://visualstudio.microsoft.com/downloads/' 와 같은 에러가 나면 안내에 나와있는 사이트에 가서 Microsoft Visual C++ 을 다운받아 설치하시기 바랍니다.

 

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

Posted by R Friend R_Friend