データのエクスポート（フェッチ）

COPY … TOによるエクスポート

%%sql
COPY(
    SELECT
        *
    FROM
        duckdb_functions()
    LIMIT 5
) TO 'data/duckdb_functions.csv'
;

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

Count

%cat ./data/duckdb_functions.csv

database_name,database_oid,schema_name,function_name,function_type,description,comment,tags,return_type,parameters,parameter_types,varargs,macro_definition,has_side_effects,internal,function_oid,example,stability
system,0,main,pragma_platform,table,,,{},,[],[],,,,true,8,,
system,0,main,json_execute_serialized_sql,table,,,{},,[col0],[VARCHAR],,,,true,1224,,
system,0,main,pandas_scan,table,,,{},,[col0],[POINTER],,,,true,1826,,
system,0,main,parquet_file_metadata,table,,,{},,[col0],[VARCHAR],,,,true,1820,,
system,0,main,parquet_file_metadata,table,,,{},,[col0],[VARCHAR[]],,,,true,1820,,

See also

• Excel Export

• JSON Export

• Parquet Export

%%sql
CREATE TABLE fx(
    pair VARCHAR,
    ask FLOAT,
    bid FLOAT
)
;
INSERT INTO fx
VALUES(
    'usdjpy',
    150.1,
    150.0
)
;
SELECT
    *
FROM
    fx
;

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

pair	ask	bid
usdjpy	150.10000610351562	150.0

%%sql
COPY fx TO 'data/fx.parquet';

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

Count

EXPORT DATABASEによるエクスポート

%%sql
EXPORT DATABASE './data'

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

Success

%%sql
DROP TABLE fx

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

Success

%%sql
IMPORT DATABASE './data'

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

Count

%%sql
SELECT * FROM duckdb_tables();

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

database_name	database_oid	schema_name	schema_oid	table_name	table_oid	comment	tags	internal	temporary	has_primary_key	estimated_size	column_count	index_count	check_constraint_count	sql
memory	1148	main	1150	fx	2541	None	{}	False	False	False	1	3	0	0	CREATE TABLE fx(pair VARCHAR, ask FLOAT, bid FLOAT);

%%sql
INSERT INTO fx
VALUES ('eurjpy', 160.1, 160.0);

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

Count

%%sql
SELECT * FROM fx

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

pair	ask	bid
usdjpy	150.10000610351562	150.0
eurjpy	160.10000610351562	160.0

Pythonオブジェクトへのフェッチ（エクスポート）

import duckdb

r = duckdb.sql("SELECT database_name, database_oid FROM duckdb_functions() LIMIT 5")

リスト・タプル

r.fetchone()

('system', '0')

r.fetchall()

[('system', '0'), ('system', '0'), ('system', '0'), ('system', '0')]

pandas DataFrame

r.df()

	database_name	database_oid
0	system	0
1	system	0
2	system	0
3	system	0
4	system	0

NumPy ndarray

r.fetchnumpy()

{'database_name': array(['system', 'system', 'system', 'system', 'system'], dtype=object),
 'database_oid': array(['0', '0', '0', '0', '0'], dtype=object)}

Apache Arrow

r.arrow()

pyarrow.Table
database_name: string
database_oid: string
----
database_name: [["system","system","system","system","system"]]
database_oid: [["0","0","0","0","0"]]

Polars

r.pl()

shape: (5, 2)

database_name	database_oid
str	str
"system"	"0"
"system"	"0"
"system"	"0"
"system"	"0"
"system"	"0"