'jupyter notebook에서 PostgreSQL database 접속하기'에 해당되는 글 1건

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

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

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
반응형
Posted by Rfriend
,