Loading data into MotherDuck with Python

Copying a table from a local DuckDB database into MotherDuck

You can use CREATE TABLE AS SELECT to load CSV, Parquet, and JSON files into MotherDuck from either local, Amazon S3, or https sources as shown in the following examples.

# load from local machine into table mytable of the current/active used database
con.sql("CREATE TABLE mytable AS SELECT * FROM '~/filepath.csv'");
# load from an S3 bucket into table mytable of the current/active database
con.sql("CREATE TABLE mytable AS SELECT * FROM 's3://bucket/path/*.parquet'")

If the source data matches the table’s schema exactly you can also use INSERT INTO ... SELECT to append data, as shown in the following example.

# append to table mytable in the currently selected database from S3
con.sql("INSERT INTO mytable SELECT * FROM ‘s3://bucket/path/*.parquet’")

tip

Use INSERT INTO ... SELECT to load data from files as shown above. Do not use single-row INSERT INTO ... VALUES statements in a loop — this is significantly slower because each statement incurs separate network overhead. See Loading data best practices for more detail.

Copying an entire local DuckDB database to MotherDuck

MotherDuck supports copying your opened DuckDB database into a MotherDuck database. The following example copies a local DuckDB database named localdb into a MotherDuck-hosted database named clouddb.

 # open the local db
local_con = duckdb.connect("localdb.ddb") 
# connect to MotherDuck
local_con.sql("ATTACH 'md:'")
# The from indicates the file to upload. An empty path indicates the current database 
local_con.sql("CREATE DATABASE clouddb FROM CURRENT_DATABASE()") 

A local DuckDB database can also be copied by its file path:

local_con = duckdb.connect("md:")
local_con.sql("CREATE DATABASE clouddb FROM 'localdb.ddb'")

See Loading Data into MotherDuck for more detail.