Skip to main content

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.