Jupyter Notebooks

• Jupyter（Notebook）からSQLクエリを実行

• クエリと結果の両方を記録・共有できる

• Pythonコードと併用できる

Note

Google Colabに対応

Notebookのサンプル: https://colab.research.google.com/drive/1eOA2FYHqEfZWLYssbUxdIpSL3PFxWVjk?usp=sharing

ライブラリのインストール

• jupysql: NotebookのセルにSQLを記述

• duckdb-engine: SQLAlchemyへの接続

pip install jupysql duckdb-engine

duckdb-engineを使った接続

拡張機能のロード

import duckdb

%load_ext sql
%sql duckdb:///:memory:

Connecting to 'duckdb:///:memory:'

セルマジックを使ったSQLクエリ

%%sql
SELECT
    database_name,
    database_oid
FROM
    duckdb_functions()
ORDER BY
    ALL
    DESC
LIMIT 5
;

Running query in 'duckdb:///:memory:'

database_name	database_oid
temp	1830
temp	1830
temp	1830
temp	1830
temp	1830

Pythonオブジェクト

%%sql
result << SELECT
    database_name,
    database_oid
FROM
    duckdb_functions()
ORDER BY
    ALL
    DESC
LIMIT 5
;

Running query in 'duckdb:///:memory:'

result

database_name	database_oid
temp	1830
temp	1830
temp	1830
temp	1830
temp	1830

%%sql
SELECT
    schema_name,
    function_name
FROM
    duckdb_functions()
ORDER BY
    ALL
    DESC
LIMIT 5
;

Running query in 'duckdb:///:memory:'

schema_name	function_name
pg_catalog	shobj_description
pg_catalog	shobj_description
pg_catalog	shobj_description
pg_catalog	pg_typeof
pg_catalog	pg_typeof

# _は直前の実行結果を保持する変数
_

schema_name	function_name
pg_catalog	shobj_description
pg_catalog	shobj_description
pg_catalog	shobj_description
pg_catalog	pg_typeof
pg_catalog	pg_typeof