[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법

지난번 포스팅에서는 Python의 ipython-sql, psycopg2 패키지를 사용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database에 접속할 수 있는 4가지 방법(https://rfriend.tistory.com/577)을 소개하였습니다.

이번 포스팅에서는 Python의 ipython-sql, psycopg2 패키지를 사용하여 PostgreSQL, Greenplum database에 SQL query를 할 때 Jupyter Notebook의 로컬 변수를 SQL query에 대입하여 변수값을 동적으로 대체해가면서 query 할 수 있는 3가지 방법을 소개하겠습니다.

Python의 로컬 변수를 SQL query 문에 사용할 수 있으므로 Python과 PostgreSQL, Greenplum DB를 서로 연동해서 데이터분석과 프로그래밍을 하는 경우 매우 강력하고 유용하게 사용할 수 있습니다.

(방법 1) Variable Substitution:  %sql SELECT :variable_name

(방법 2) Variable Substitution:  %sql SELECT {variable_name}

(방법 3) Variable Substitution:  %sql SELECT $variable_name

(0) 필요 Python 패키지 사전 설치

아래의 SQLAlchemy, psycopg2, ipython-sql, pgspecial, sql_magic 중에서 아직 설치가 안된 패키지가 있다면 아래처럼 명령 프롬프트 창에서 Python의 패키지를 설치해줍니다.

-- (명령 프롬프트 창에서 pip 로 설치) $ pip install --upgrade pip $ pip install sqlalchemy $ pip install psycopg2 $ pip install ipython-sql==0.3.9 $ pip install pgspecial

ipython-sql 패키지로 Jupyter Notebook에서 Greenplum database에 접속한 후에, 예제로 사용할 간단한 houses 테이블을 만들어보겠습니다.

%load_ext sql %sql postgresql://gpadmin:changeme@localhost/demo [Out] 'Connected: gpadmin@demo' %sql select version(); * postgresql://gpadmin:***@localhost/demo 1 rows affected. Out[3]: version PostgreSQL 9.4.24 (Greenplum Database 6.10.1 build commit:efba04ce26ebb29b535a255a5e95d1f5ebfde94e) on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 6.4.0, 64-bit compiled on Aug 13 2020 02:56:29 %%sql DROP TABLE IF EXISTS houses; CREATE TABLE houses (id INT, tax INT, bedroom INT, bath FLOAT, price INT,             size INT, lot INT, region TEXT); INSERT INTO houses VALUES   (1 ,  590 ,       2 ,    1 ,  50000 ,  770 , 22100, 'seoul'),   (2 , 1050 ,       3 ,    2 ,  85000 , 1410 , 12000, 'seoul'),   (3 ,   20 ,       3 ,    1 ,  22500 , 1060 ,  3500, 'busan'),   (4 ,  870 ,       2 ,    2 ,  90000 , 1300 , 17500, 'seoul'),   (5 , 1320 ,       3 ,    2 , 133000 , 1500 , 30000, 'bundang'),   (6 , 1350 ,       2 ,    1 ,  90500 ,  820 , 25700, 'bundang'),   (7 , 2790 ,       3 ,  2.5 , 260000 , 2130 , 25000, 'busan'),   (8 ,  680 ,       2 ,    1 , 142500 , 1170 , 22000, 'busan'),   (9 , 1840 ,       3 ,    2 , 160000 , 1500 , 19000, 'inchon'),  (10 , 3680 ,       4 ,    2 , 240000 , 2790 , 20000, 'seoul'),  (11 , 1660 ,       3 ,    1 ,  87000 , 1030 , 17500, 'inchon'),  (12 , 1620 ,       3 ,    2 , 118600 , 1250 , 20000, 'busan'),  (13 , 3100 ,       3 ,    2 , 140000 , 1760 , 38000, 'bundang'),  (14 , 2070 ,       2 ,    3 , 148000 , 1550 , 14000, 'bundang'),  (15 ,  650 ,       3 ,  1.5 ,  65000 , 1450 , 12000, 'inchon');

(방법 1) Variable Substitution:   %sql SELECT :variable_name

첫번째 방법은 :variable_name 과 같은 named style 을 사용해서 Jupyter Notebook에서 local namespace에 생성한 변수 이름을 SQL query에 넣어서 동적으로 값을 바꾸어 가면서 query 를 해보겠습니다.

(1-1) :variable_name 으로 SQL의 정수(integer) 변수값 대체

id_var = (1, 5, 15) %sql SELECT * FROM houses WHERE id IN :id_var; [Out] * postgresql://gpadmin:***@localhost/demo 3 rows affected. Out[123]: id tax bedroom bath price size lot region 1 590 2 1.0 50000 770 22100 seoul 5 1320 3 2.0 133000 1500 30000 bundang 15 650 3 1.5 65000 1450 12000 inchon

(1-2) :variable_name 으로 SQL의 문자형(character) 변수값 대체

region_var = 'seoul' %sql SELECT * FROM houses WHERE region = :region_var; [Out] * postgresql://gpadmin:***@localhost/demo 4 rows affected. Out[125]: id tax bedroom bath price size lot region 10 3680 4 2.0 240000 2790 20000 seoul 1 590 2 1.0 50000 770 22100 seoul 2 1050 3 2.0 85000 1410 12000 seoul 4 870 2 2.0 90000 1300 17500 seoul

(방법 2) Variable Substitution:  %sql SELECT {variable_name}

(2-1) {variable_name} 으로 SQL의 정수(integer) 변수값 대체

id_var = (1, 5, 15) %sql SELECT * FROM houses WHERE id IN {id_var}; [Out] * postgresql://gpadmin:***@localhost:5432/demo 3 rows affected. Out[126]: id tax bedroom bath price size lot region 1 590 2 1.0 50000 770 22100 seoul 5 1320 3 2.0 133000 1500 30000 bundang 15 650 3 1.5 65000 1450 12000 inchon

(2-2) '{variable_name}' 으로 SQL의 문자형(character) 변수값 대체

region_var = 'seoul' %sql SELECT * FROM houses WHERE region = '{region_var}'; [Out] * postgresql://gpadmin:***@localhost:5432/demo 4 rows affected. Out[127]: id tax bedroom bath price size lot region 1 590 2 1.0 50000 770 22100 seoul 10 3680 4 2.0 240000 2790 20000 seoul 2 1050 3 2.0 85000 1410 12000 seoul 4 870 2 2.0 90000 1300 17500 seoul

(방법 3) Variable Substitution:  %sql SELECT $variable_name

(3-1) $variable_name 으로 SQL의 정수형(integer) 변수값 대체

id_var = (1, 5, 15) %sql SELECT * FROM houses WHERE id IN $id_var; [Out] * postgresql://gpadmin:***@localhost:5432/demo 3 rows affected. Out[128]: id tax bedroom bath price size lot region 1 590 2 1.0 50000 770 22100 seoul 5 1320 3 2.0 133000 1500 30000 bundang 15 650 3 1.5 65000 1450 12000 inchon

(3-2) $variable_name 으로 SQL의 문자형(character) 변수값 대체

localname space에 문자형의 bind parameter 값 입력해줄 때 큰따옴표(" ")로 감싸주고, 그 안에 작은따옴표(' ')로 값 입력해주도록 하세요. 그냥 작은따옴표(' ')만 했더니 칼럼으로 인식을 해서 에러가 나네요.

region_var = "'seoul'" %sql SELECT * FROM houses WHERE region = $region_var; [Out] * postgresql://gpadmin:***@localhost:5432/demo 3 rows affected. Out[171]: id tax bedroom bath price size lot region 2 1050 3 2.0 85000 1410 12000 seoul 4 870 2 2.0 90000 1300 17500 seoul 10 3680 4 2.0 240000 2790 20000 seoul

* Reference: https://pypi.org/project/ipython-sql/

이번 포스팅이 많은 도움이 되었기를 바랍니다.

행복한 데이터 과학자 되세요!