R, Python 분석과 프로그래밍의 친구 (by R Friend)

'ipython-sql'에 해당되는 글 4건

2020.11.21 [PostgreSQL, Greenplum] ipython-sql로 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법
2020.11.15 [PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법
2020.11.15 [PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook에서 DB 접속하는 4가지 방법
2020.10.20 [PostgreSQL, Greenplum] ipython-sql, pgspecial로 Jupyter Notebook에서 DB접속하고 SQL query 하기

[PostgreSQL, Greenplum] ipython-sql로 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법

Greenplum and PostgreSQL Database 2020. 11. 21. 14:52

지난번 포스팅에서는 Python의 ipython-sql 패키지를 이용하여 Jupyter Notebook 의 로컬변수(variable in local namespace)로 PostgreSQL, Greenplum database에 동적으로 SQL query를 하는 3가지 방법을 소개하였습니다.(https://rfriend.tistory.com/578)

이번 포스팅에서는 Python의 ipython-sql 패키지를 이용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database에 SQL query한 결과를 pandas DataFrame으로 가져오는 (즉, 객체로 할당하는) 3가지 방법을 소개하겠습니다.

(1) ResultSet = %sql SELECT ... (single-line query)

(2) %%sql ResultSet << SELECT ... (multi-line queries)

(3) %%sql SELECT ... 를 먼저 실행한 후에,

ResultSet = _ (select 결과를 사후에 할당)

==> (공통) sql.run.ResultSet을 pandas DataFrame으로 변환: df = ResultSet.DataFrame()

(0) Python 패키지 사전 설치 및 PostgreSQL, Greenplum database 접속

명령 프롬프트 창에서 pip로 PostgreSQL, Greenplum database에 접속하여 SQL query를 할 수 있는 Python 패키지를 설치해주세요.

-- (명령 프롬프트 창에서)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

ipython-sql 패키지를 이용하여 Jupyter Notebook에서 Greenplum database에 접속해보겠습니다.

%load_ext sql

# %sql postgresql://username:passowrd@host:port/database

%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 1 rows affected.

예제로 사용할 간단한 houses 테이블을 만들고 데이터를 넣어보겠습니다.

%%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) ResultSet = %sql SELECT ... (single-line query)

SQL query가 1줄 짜리인 경우에 일반적인 IPython assignment 방식(result = %sql SELECT ...)으로 간단하게 사용할 수 있습니다.

SQL query 결과를 할당받으면 'sql.run.ResultSet' 데이터 형태입니다. 이를 pandas DataFrame으로 변환하려면 pandas의 DataFrame() 메소드를 사용하면 됩니다. ResultSet.DataFrame() 처럼 해서 일단 pandas DataFrame으로 변환을 해주고 나면, pandas의 데이터전처리 및 시각화 함수를 사용할 수 있습니다.

# for 1 line SQL query
house_sub_1 = %sql SELECT * FROM houses WHERE price <= 80000;

[Out]

 * postgresql://gpadmin:***@localhost:5432/demo
3 rows affected.

house_sub_1

[Out]

id	tax	bedroom	bath	price	size	lot	region
3	20	3	1.0	22500	1060	3500	busan
1	590	2	1.0	50000	770	22100	seoul
15	650	3	1.5	65000	1450	12000	inchon

type(house_sub_1)

[Out] sql.run.ResultSet

# converting 'sql.run.ResultSet into pandas DataFrame

df_house_sub_1 = house_sub_1.DataFrame()
type(df_house_sub_1)

[Out] pandas.core.frame.DataFrame

# plot using pandas function

import matplotlib.pyplot as plt
df_house_sub_1['price'].plot(kind='bar')
plt.show()

(2) %%sql ResultSet << SELECT ... (multi-line queries)

2줄 이상의 SQL quries 결과를 할당하려면 %%sql result_name 그리고 이어서 '<<' 연산자(<< operator)를 사용합니다.

(참고로, %sql 은 1줄 짜리 SQL query를 쓸 때, %%sql 은 2줄 이상의 SQL query를 쓸 때 사용함)

'sql.run.ResultSet' 데이터 형태로 할당받은 결과를 DataFrame() 메소드를 사용해서 pandas DataFrame으로 변환하는 것은 위의 (1)번과 동일합니다.

%%sql house_sub_2 << SELECT *
FROM houses
WHERE price <= 80000;

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected. Returning data to local variable house_sub_2

house_sub_2

[Out]

id	tax	bedroom	bath	price	size	lot	region
1	590	2	1.0	50000	770	22100	seoul
3	20	3	1.0	22500	1060	3500	busan
15	650	3	1.5	65000	1450	12000	inchon

type(house_sub_2)

[Out] sql.run.ResultSet

# Converting 'sql.run.ResultSet' into pandas DataFrame

df_house_sub_2 = house_sub_2.DataFrame()
type(df_house_sub_2)

[Out] pandas.core.frame.DataFrame

(3) %%sql SELECT ... 를 먼저 실행한 후에,

ResultSet = _ (select 결과를 사후에 할당)

경우에 따라서는 먼저 %sql 로 1줄 짜리 SQL query를 먼저 하거나, 혹은 %%sql 로 2줄 이상의 SQL query 를 Jupyter Notebook에서 먼저 실행한 후에, 이의 결과를 확인하고 나서 나중에 그 결과를 Python의 pandas DataFrame으로 가져오고 싶을 때도 있을 것입니다.

이럴 경우에 직전에 실행한 %sql SELECT ... 또는 %%sql SELECT ... 결과를 result = _ 를 사용해서 'sql.run.ResultSet'으로 할당받을 수 있습니다.

'sql.run.ResultSet' 데이터 형태로 할당받은 결과를 DataFrame() 메소드를 사용해서 pandas DataFrame으로 변환하는 것은 위의 (1)번과 동일합니다.

%sql SELECT * FROM houses WHERE price <= 80000;

[Out]

* postgresql://gpadmin:***@localhost:5432/demo 3 rows affected.

Out[22]:

id	tax	bedroom	bath	price	size	lot	region
1	590	2	1.0	50000	770	22100	seoul
3	20	3	1.0	22500	1060	3500	busan
15	650	3	1.5	65000	1450	12000	inchon

# Capturing the quries results and assigning to ResultSet

house_sub_3 = _

house_sub_3

[Out]

id	tax	bedroom	bath	price	size	lot	region
1	590	2	1.0	50000	770	22100	seoul
3	20	3	1.0	22500	1060	3500	busan
15	650	3	1.5	65000	1450	12000	inchon

type(house_sub_3)

[Out] sql.run.ResultSet

df_house_sub_3 = house_sub_3.DataFrame()
type(df_house_sub_3)

[Out] pandas.core.frame.DataFrame

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

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

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

728x90

저작자표시 비영리 변경금지

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

[PostgreSQL, Greenplum] 상관관계 분석 (Correlation Coefficients using SQL, MADlib) (0)	2020.11.29
[PostgreSQL, Greenplum] 연속형 데이터의 집계/ 요약 통계량 구하기 (0)	2020.11.28
[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법 (0)	2020.11.15
[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

Posted by Rfriend

[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]:

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/

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

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

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

[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook에서 DB 접속하는 4가지 방법

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

지난번 포스팅에서는 Python의 ipython-sql, pgspecial 패키지를 사용하여 Jupyter Notebook 에서 PostgreSQL, Greenplum database 에 접속(access)하고 SQL query, meta-commands 를 하는 방법(https://rfriend.tistory.com/572)을 간략하게 소개하였습니다.

이번 포스팅에서는 psycopg2 와 ipython-sql 패키지를 사용하여 Jupyter Notebook에서 PostgreSQL, Greenplum database 에 접속하는 4가지 방법을 추가로 소개하겠습니다.

특히, 사용자이름(username), 비밀번호(password), 호스트(host), 포트(port), 데이터베이스(database) 등과 같이 보안이 요구되고 다른 사용자에게 노출이나 공유가 되면 곤란한 정보들(DB Credentials)을 Jupyter Notebook에서 표기/노출하지 않고 별도의 파일로 보관하면서, 이를 불러와서 DB access 할 수 있는 방법들에 주안점을 두고 소개하겠습니다.

-- Jupyter Notebook 의 Cell 안에서 DB credentials 직접 입력 (* 외부 노출되므로 권장하지 않음)

(1) %sql postgresql://Username:Password@Host:Port/Database

(2) %sql $connection_string

-- 별도의 폴더에 별도의 파일로 DB credentials 관리하고, 이를 불러와서 Jupyter Notebook에 입력

(* 보안유지 되므로 권장함)

(3) config.py 별도 파일 & %sql $connection_string

(4) db_credentions 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path

(0) Python 패키지 사전 설치

먼저, 명령 프롬프트 창에서 아래의 PostgreSQL, Greenplum database에 접속하고 SQL query 를 하기 위해 필요한 Python 패키지들을 pip로 설치해줍니다.

-- (명령 프롬프트 창에서 설치)

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

-- Jupyter Notebook 의 Cell 안에서 DB credentials 직접 입력

(* 편리하기는 하지만, DB 접속 정보가 외부에 노출되므로 권장하지 않음. )

(1) %sql postgresql://Username:Password@Host:Port/Database

가장 편리한 방법은 SQLAlchemy 표준 URL (database-driver://Username:Password@Host:Port/Database) 에 따라 PostgreSQL, Greenplum database에 connection engine을 생성하여 접속하는 방법입니다.

하지만, 이 방법은 Jupyter Notebook에 DB 접속정보가 고스란히 노출되기 때문에 만약 다른 조직, 팀원 간에 협업을 하고 notebook 파일을 공유해야할 일이 생길 경우 보안 방침에 위배가 되므로 권장하는 방법은 아닙니다.

%load_ext sql

# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@demo'

(2) %sql $connection_string

%sql $connection_string 문으로 Jupyter Notebook에서 동적으로 DB credentials 를 Python string format 으로 입력받아서 PostgreSQL, Greenplum database에 접속할 수 있습니다.

다만, 아래처럼 DB credentials 를 Jupyter notebook 의 Cell 안에서 직접 입력하면 DB 접속 정보가 외부로 노출되는 문제가 있습니다.

%load_ext sql

# DB credentials

username = "gpadmin"
password = "changeme"
host = "localhost"
port = "5432"
database = "demo"

# connection strings using Python string format
connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)

# dynamic access dredentials
%sql $connection_string

-- 별도의 폴더에 별도의 파일로 DB credentials 관리하고, 이를 불러와서 Jupyter Notebook에 입력

(* 보안유지 되므로 권장함)

(3) config.py 별도 파일 & %sql $connection_string

세번째 방법은 DB Credentials 정보를 별도의 파일에 분리해서 만들어놓고, 이를 불러와서 DB connect 하는 방법입니다. 아래에 예를 들어보면, (폴더, 파일 이름은 각자 알아서 정해주면 됨)

(a) HOME directory 밑에 DB credentials 파일을 넣어둘 'db_cred' 라는 이름의 폴더 만들고,

(b) 'db_cred' 폴더 안에 'gpdb_credentials.py', '__init__.py' 라는 이름의 2개의 Python 파일을 생성함.

'gpdb_credentials.py' 파일에는 Dictionary (Key : Value 짝) 형태로 Username, Password, Host, Port, Database 정보를 입력해줌. 여러개의 Database 별로 credentials 정보를 각각 다른 이름의 Dictionary 로 하나의 파일 안에 생성해놓을 수 있음.

'__init__.py' 파일은 내용은 비어있으며, 해당 폴더의 Python 파일을 패키지로 만들기 위해 생성해줌.

(c) Jupyter Notebook 을 작업하는 Directory 에서도 HOME directory 밑의 'db_cred' 폴더에 접근해서 'gpdb_credentials.py' 파일에 접근할 수 있도록 sys.path.append(cred_path) 로 Python 의 Path 에 추가해줌. (sys.path.append(cred_path))

(d) 작업을 하는 Jupyter Notebook 에서 'from gpdb_credentials import demo_db' 문으로 gpdb_credentials.py 파일에서 'demo_db' Dictionary 를 불러옴.

(e) 'demo_db' Dictionary 에서 DB connection에 필요한 정보를 파싱해옴. (dict['key'] 인덱싱)

(f) %sql $ 문 뒤에 (e)에서 파싱해서 만든 connection_string을 입력해서 DB connect 함.

%load_ext sql

# put a folder and DB credential files at HOME directory

import os
homedir = os.getenv('HOME')
cred_path = os.path.join(homedir, 'db_cred')

# add a 'cred_path' for interpreter to search
import sys
sys.path.append(cred_path)

# import DB credentials from 'gpdb_credentials.py' dictionary file.

from gpdb_credentials import demo_db

# parsing DB credentials and connect to Greenplum using %sql $connection_string

username = demo_db['Username']
password = demo_db['Password']
host = demo_db['Host']
port = demo_db['Port']
database = demo_db['Database']

connection_string = "postgresql://{user}:{password}@{host}:{port}/{db}".format(
    user=username,
    password=password,
    host=host,
    port=port,
    db=database)

%sql $connection_string

(4) db_credentials 별도 파일 & %config SqlMagic.dsn_filename = db_cred_path

명령 프롬프트 창에서 아래처럼 0.3.9 버전의 ipython-sql을 설치해줍니다. (최신 버전은 0.4.0 이지만 Python 3.x. 버전의 ipython-sql 0.4.0 버전에 DSN connections 를 하는데 있어 config 를 반환하지 않는 bug가 있습니다. config bug fix 되기 전까지는 0.3.9 버전으로 사용하기 바랍니다.)

-- 명령 프롬프트 창에서 ipython-sql 0.3.9 버전 설치
pip install ipython-sql==0.3.9

(a) PostgreSQL, Greenplum database 접속 정보(connection info.)를 별도의 configuration file 에 저장하여 HOME directory 밑에 보관합니다. 이때 2개 이상의 복수의 DB credentials 정보를 [DB alias] 로 구분해서 하나의 configuration file에 저장해서 사용할 수 있습니다.

파일 이름을 ".odbc.ini", ".dsn.ini" 처럼 "."으로 시작하면 '숨김 파일(hidden file)'이 되어 평상시에는 탐색기, Finder에서는 볼 수가 없으므로 DB 접속정보를 관리하는데 좀더 보안에 유리합니다.

(참고로, Windows OS에서 숨김파일을 보려면, Windows 탐색기에서 [구성] > [폴더 및 검색 옵션] > [폴더 옵션] 대화상자에서 [보기] 탭을 클릭 > [고급 설정]에서 "숨김 파일 밒 폴더 표시"를 선택하면 됩니다.

Mac OS 에서는 Finder에서 "Shift + Command + ." 동시에 눌러주면 숨김 파일이 표시됩니다.)

(b) Jupyter Notebook에서 ipython-sql 로 DSN connections 을 할 수 있습니다.

%config SqlMagic.dsn_filename = "$homedir/.odbc.ini"

(c) DB connect 된 이후에 제일 처음으로 %sql 로 SQL query 할 때 DB credentions 의 DB alias 를 [ ] 안에 넣어서 명시를 해주고(예: %sql [demo_db] SELECT version();), 그 다음부터 %sql 문으로 SQL query 할 때는 DB alias 를 안써주고 바로 SQL query 를 하면 됩니다.

(d) Jupyter Notebook의 중간 Cell 에서 사용(connect)하려는 DB를 바꾸고 싶으면 %sql [DB_alias2] SELECT .... 처럼 [DB_alias] 부분에 다른 DB alias 이름을 명시해주고 SQL query 를 하면, 그 이후 Cell 부터는 새로운 DB 를 connect 해서 query를 할 수 있습니다.

(예: %sql [dev_db] SELECT COUNT(*) FROM tbl;)

%load_ext sql

import os
homedir = os.getenv('HOME')

# parse and configure gpdb credentials and access to GPDB
%config SqlMagic.dsn_filename = "$homedir/.odbc.ini"

# put [alias_name] after %sql in the first line
%sql [demo_db] SELECT version();

[Out] * postgresql://gpadmin:***@localhost:5432/demo

1 rows affected.

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

다음 포스팅에서는 ipython-sql 로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법(https://rfriend.tistory.com/578)을 소개하겠습니다.

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

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

728x90

저작자표시 비영리 변경금지

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

[PostgreSQL, Greenplum] ipython-sql로 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법 (0)	2020.11.21
[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법 (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
[PostgreSQL, Greenplum] 문자열 처리 연산자 및 함수 (character type operators and functions) (0)	2020.07.04

Posted by Rfriend

[PostgreSQL, Greenplum] ipython-sql, pgspecial로 Jupyter Notebook에서 DB접속하고 SQL query 하기

Greenplum and PostgreSQL Database 2020. 10. 20. 23:52

Python의 SQLAlchemy (모든 DB), psycopg2 (PostgreSQL, Greenplum) 패키지를 이용하여 Spyder 나 Pycharm 과 같은 IDE에서 PostgreSQL, Greenplum database에 접속(access) 하고 SQL query 를 할 수 있습니다.

그렇다면 Jupyter Notebook 에서도 DB access, SQL query를 할 수 있으면 편하겠지요?

이번 포스팅에서는 Python의 ipython-sql 과 pgspecial 패키지를 이용하여 PostgreSQL, Greenplum database 에 Jupyter Notebook으로 접속(access DB)하여 SQL query 를 하는 방법을 소개하겠습니다.

(1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기

(2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)

(3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands query 하기 (\l, \dn, \dt)

(0) 사전 설치가 필요한 Python 패키지 리스트

명령 프롬프트 창에서 아래의 5개 패키지에 대해서 pip 로 설치해주시기 바랍니다.

sqlalchemy, psycopg2는 PostgreSQL, Greenplum DB 접속(access, connection)를 위해서 필요한 Python 패키지 입니다.

ipython-sql, sql_magic은 IPython으로 Jupyter Notebook에서 DB access, SQL query 를 하기 위해 필요한 Python 패키지입니다.

pgspecial은 Jupyter Notebook에서 PostgreSQL, Greenplum DB에 meta-commands (역슬래쉬 \ 로 시작하는, psql 에서 사용하는 \l, \dn, \dt 명령문) 를 위해 필요한 Python 패키지입니다.

ipython-sql 의 경우 2020.12월 현재 0.4.0 버전 (python 3.x) 이 최신인데요, %config로 DB access 하는 명령문의 bug가 아직 fix가 안되어 있어서, 아래처럼 ipython-sql==0.3.9 로 한단계 낮은 버전으로 설치해주세요.

-- 명령 프롬프트 창에서 pip 로 python 패키지 설치

$ pip install --upgrade pip

$ pip install sqlalchemy

$ pip install psycopg2

$ pip install ipython-sql==0.3.9

$ pip install pgspecial

$ pip install sql_magic

만약 psycopg2 모듈을 pip 로 설치하다가 에러가 나면 아래처럼 wheel package 를 이용해서 psycopg2-binary 로 설치해보세요.

Error: pg_config executable not found.

pg_config is required to build psycopg2 from source. Please add the directory

containing pg_config to the $PATH or specify the full executable path with the

option:

python setup.py build_ext --pg-config /path/to/pg_config build ..

or with the pg_config option in 'setup.cfg'.

$ pip install psycopg2-binary

(1) ipython-sql 로 PostgreSQL, Greenplum DB 접속(access)하기

%load_ext sql 로 IPython의 sql 을 로딩하여 %sql 또는 %%sql magic 명령문을 사용할 수 있습니다.

PostgreSQL, Greenplum database에 접속할 때는 SQLAlchemy 의 표준 URL connect strings 를 사용합니다.

[ SQLAlchemy 의 표준 Database URL]

dialect+driver://username:password@hoat:port/database

아래의 db credentials 로 Greenplum database에 접속할 때의 예입니다.

- driver: postgresql

- username: gpadmin

- password: changeme

- host: localhost

- port: 5432

- database: demo

%load_ext sql

# postgresql://Username:Password@Host:Port/Database
%sql postgresql://gpadmin:changeme@localhost:5432/demo

[Out] 'Connected: gpadmin@testdb'

* 위의 %sql SQLAlchemy 표준 URL 방법 외에 Jupyter Notebook에서 PostgreSQL, Greenplum DB에 접속하는 다른 3가지 추가 방법은 https://rfriend.tistory.com/577 를 참고하세요.

(2) ipython-sql 로 PostgreSQL, Greenplum DB에 SQL query 하기 (%sql, %%sql)

Jupyter Notebook의 Cell 안에 1줄 SQL query일 경우는 %sql 로 시작하고, 2줄 이상 SQL query 일 경우에는 %%sql 로 시작합니다.

(2-1) %sql : 1줄의 SQL query

1줄짜리 SELECT 문으로 PostgreSQL의 버전을 확인해보겠습니다.

%sql SELECT version();

[Out]

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

(2-1) %%sql : 2줄 이상의 SQL query

아래 예제는 pg_catalog.pg_tables 테이블에서 스키마 이름이 pg_catalog, information_schema 가 아닌 테이블을 조회하여 1개만 반환해보는 query 입니다.

%%sql
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'pg_catalog' AND
schemaname != 'information_schema'
LIMIT 1;

(3) pgspecial 로 PostgreSQL, Greenplum DB에 meta-commands (\l, \dn, \dt)

meta-commands 는 psql 에서 역슬래쉬(\)와 함께 사용해서 데이터베이스, 스키마, 테이블 등을 조회할 때 사용하는 명령어를 말합니다. pgspecial 패키지는 Jupyter Notebook에서 meta-commands 를 사용할 수 있게 해줍니다.

(3-1) database 조회 : %sql \l (역슬래쉬 + L)

%sql \l

[Out]

Name

Owner

Encoding

Collate

Ctype

Access privileges

demo

gpadmin

UTF8

en_US.utf8

=Tc/gpadmin

gpadmin=CTc/gpadmin

dsuser=CTc/gpadmin

gpperfmon	gpadmin	UTF8	en_US.utf8	en_US.utf8	None
postgres	gpadmin	UTF8	en_US.utf8	en_US.utf8	None
template0	gpadmin	UTF8	en_US.utf8	en_US.utf8	=c/gpadmin gpadmin=CTc/gpadmin
template1	gpadmin	UTF8	en_US.utf8	en_US.utf8	=c/gpadmin gpadmin=CTc/gpadmin

(3-2) Schema 조회 : %sql \dn

%sql \dn

[Out]

Name	Owner
gp_toolkit	gpadmin
madlib	dsuser14
public	gpadmin

(3-3) Table 조회 : %sql \dt

아래 예제는 public 스키마에서 "ab" 로 시작하는 모든 테이블(public.ab*)을 조회한 것입니다.

%sql \dt public.ab*

[Out]

Schema	Name	Type	Owner
public	abalone	table	gpadmin
public	abalone_corr	table	gpadmin
public	abalone_corr_summary	table	gpadmin
public	abalone_correlations	table	gpadmin

이상으로 ipython-sql, pgspecial 패키지를 사용해서 PostgreSQL, Greenplum database에 접속하고 SQL query, meta-commands 하는 방법에 대한 가장 기본적이고 개략적인 소개를 마치겠습니다.

* 다음번 포스팅에서는 SQLAlchemy, psycopg2, ipython-sql 로 Jupyter Notebook 에서 PostgreSQL, Greenplum database에 접속하는 4가지 방법(https://rfriend.tistory.com/577)에 대한 소소한 팁을 추가로 소개하겠습니다.

* ipython-sql 로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법은 https://rfriend.tistory.com/578 를 참고하세요.

* ipython-sql로 PostgreSQL, Greenplum database에 접속하여 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법은 https://rfriend.tistory.com/579 를 참고하세요.

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

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

728x90

저작자표시 비영리 변경금지

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

[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook 의 로컬변수로 동적으로 SQL query 하는 3가지 방법 (0)	2020.11.15
[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook에서 DB 접속하는 4가지 방법 (0)	2020.11.15
[PostgreSQL, Greenplum] 문자열 패턴 매칭 (Pattern Matching) (0)	2020.07.12
[PostgreSQL, Greenplum] 문자열 처리 연산자 및 함수 (character type operators and functions) (0)	2020.07.04
[PostgreSQL, Greenplum] 산술연산자, 비교연산자, 논리연산자, 기타연산자 (Operators) (0)	2020.06.28

Posted by Rfriend

이전 1 다음

R, Python 분석과 프로그래밍의 친구 (by R Friend)

'ipython-sql'에 해당되는 글 4건

[PostgreSQL, Greenplum] ipython-sql로 Jupyter Notebook에서 SQL query한 결과를 pandas DataFrame으로 가져오는 3가지 방법

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

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

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

[PostgreSQL, Greenplum] ipython-sql 로 Jupyter Notebook에서 DB 접속하는 4가지 방법

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

[PostgreSQL, Greenplum] ipython-sql, pgspecial로 Jupyter Notebook에서 DB접속하고 SQL query 하기

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

카테고리

태그목록

티스토리툴바