'Jupyter Notebook에서 Greenplum DB에 로컬변수로 변환해서 query 하기'에 해당되는 글 1건

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

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

Greenplum and PostgreSQL Database 2020. 11. 15. 23:33

지난번 포스팅에서는 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]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(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]:
idtaxbedroombathpricesizelotregion
10368042.0240000279020000seoul
159021.05000077022100seoul
2105032.085000141012000seoul
487022.090000130017500seoul


 




  (방법 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]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(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]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
10368042.0240000279020000seoul
2105032.085000141012000seoul
487022.090000130017500

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]:
idtaxbedroombathpricesizelotregion
159021.05000077022100seoul
5132032.0133000150030000bundang
1565031.565000145012000inchon

 



(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]:
idtaxbedroombathpricesizelotregion
2105032.085000141012000seoul
487022.090000130017500seoul
10368042.0240000279020000seoul

 



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



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

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



728x90
반응형
저작자표시 비영리 변경금지

'Greenplum and PostgreSQL Database' 카테고리의 다른 글

[PostgreSQL, Greenplum] 연속형 데이터의 집계/ 요약 통계량 구하기  (0) 2020.11.28
[PostgreSQL, Greenplum] ipython-sql로 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법  (0) 2020.11.21
[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook에서 DB 접속하는 4가지 방법  (0) 2020.11.15
[PostgreSQL, Greenplum] ipython-sql, pgspecial로 Jupyter Notebook에서 DB접속하고 SQL query 하기  (0) 2020.10.20
[PostgreSQL, Greenplum] 문자열 패턴 매칭 (Pattern Matching)  (0) 2020.07.12
Posted by Rfriend
,

티스토리툴바