This page contains examples for data ingestion to Python using DuckDB. First, import the DuckDB package:

import duckdb

Then, proceed with any of the following sections.

CSV Files

CSV files can be read using the read_csv function, called either from within Python or directly from within SQL. By default, the read_csv function attempts to auto-detect the CSV settings by sampling from the provided file.

Read from a file using fully auto-detected settings:

duckdb.read_csv("example.csv")

Read multiple CSV files from a folder:

duckdb.read_csv("folder/*.csv")

Specify options on how the CSV is formatted internally:

duckdb.read_csv("example.csv", header = False, sep = ",")

Override types of the first two columns:

duckdb.read_csv("example.csv", dtype = ["int", "varchar"])

Directly read a CSV file from within SQL:

duckdb.sql("SELECT * FROM 'example.csv'")

Call read_csv from within SQL:

duckdb.sql("SELECT * FROM read_csv('example.csv')")

Add a filename column that holds the file each row was read from:

duckdb.read_csv("example.csv", filename=True)

Read a file-like object, such as an io.BytesIO or an open file; this needs the fsspec package:

import io

duckdb.read_csv(io.BytesIO(b"a,b\n1,2\n"))

See the CSV Import page for more information. To write a relation to a CSV file, use write_csv. If you pass overwrite=False then it refuses to replace a file that exists.

Parquet Files

Parquet files can be read using the read_parquet function, called either from within Python or directly from within SQL.

Read from a single Parquet file:

duckdb.read_parquet("example.parquet")

Read multiple Parquet files from a folder:

duckdb.read_parquet("folder/*.parquet")

Read a Parquet file over https:

duckdb.read_parquet("https://some.url/some_file.parquet")

Read a list of Parquet files:

duckdb.read_parquet(["file1.parquet", "file2.parquet", "file3.parquet"])

Directly read a Parquet file from within SQL:

duckdb.sql("SELECT * FROM 'example.parquet'")

Call read_parquet from within SQL:

duckdb.sql("SELECT * FROM read_parquet('example.parquet')")

See the Parquet Loading page for more information. To write a relation to a Parquet file, use write_parquet; with overwrite=False, it refuses to replace a file that exists.

JSON Files

JSON files can be read using the read_json function, called either from within Python or directly from within SQL. By default, the read_json function will automatically detect if a file contains newline-delimited JSON or regular JSON, and will detect the schema of the objects stored within the JSON file.

Read from a single JSON file:

duckdb.read_json("example.json")

Read multiple JSON files from a folder:

duckdb.read_json("folder/*.json")

Directly read a JSON file from within SQL:

duckdb.sql("SELECT * FROM 'example.json'")

Call read_json from within SQL:

duckdb.sql("SELECT * FROM read_json('example.json')")

Directly Accessing DataFrames and Arrow Objects

DuckDB can query a Pandas DataFrame by the name of its variable, as if it were a table:

import duckdb
import pandas as pd

test_df = pd.DataFrame.from_dict({"i": [1, 2, 3, 4], "j": ["one", "two", "three", "four"]})
print(duckdb.sql("SELECT * FROM test_df").fetchall())

[(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four')]

The same works for Pandas Series, Polars DataFrames, LazyFrames and Series, NumPy arrays, a dict of one-dimensional NumPy arrays, relations and Arrow objects.

The masked elements in a NumPy masked array are NULL. In a dict of arrays, names that differ only in case get a suffix, so the keys a and A become the columns a and A_1.

Use register() to give a DataFrame or Arrow object a SQL name. This also works for objects stored in a dictionary or class attribute.

import duckdb
import pandas as pd

my_dictionary = {}
my_dictionary["test_df"] = pd.DataFrame.from_dict({"i": [1, 2, 3, 4], "j": ["one", "two", "three", "four"]})
duckdb.register("test_df_view", my_dictionary["test_df"])
print(duckdb.sql("SELECT * FROM test_df_view").fetchall())

[(1, 'one'), (2, 'two'), (3, 'three'), (4, 'four')]

You can also create a table in DuckDB from the contents of the DataFrame (or the view):

# create a new table from the contents of a DataFrame
duckdb.execute("CREATE TABLE test_df_table AS SELECT * FROM test_df")
# insert into an existing table from the contents of the registered view
duckdb.execute("INSERT INTO test_df_table SELECT * FROM test_df_view")

A relation's create method also makes a table. A name with a catalog, such as other_db.main.test_df_table, creates the table in that attached database.

Only variables that are visible to Python code at the location of the sql() or execute() call can be queried by name. To also look in the functions that called it, set python_scan_all_frames to true. Accessing these variables is made possible by replacement scans. To disable replacement scans entirely, use:

SET python_enable_replacements = false;

The precedence of objects with the same name is as follows:

• Objects explicitly registered via register()
• Native DuckDB tables and views
• Replacement scans

DuckDB supports querying multiple types of Apache Arrow objects including tables, record batches, arrays and chunked arrays, datasets, RecordBatchReaders and scanners. See the Python guides for more examples.

• The filters of a query are pushed into Arrow datasets and Polars LazyFrames. Tables, record batches, readers, scanners and Polars DataFrames are read whole, and the filter applies to what they return.
• A RecordBatchReader can be read once. A second scan raises InvalidInputException: This RecordBatchReader has already been consumed and cannot be scanned again. To scan its data more than once, read it into a table first, for example with read_all().
• Arrow tables and Polars DataFrames are scanned on all threads, even when they hold a single chunk. A query that depends on the order of rows without an ORDER BY, such as one using first(), can return different rows between runs.

Custom Arrow Objects

DuckDB can also consume any object that implements the Arrow PyCapsule interface through an __arrow_c_stream__ method. Such objects can be queried directly through replacement scans and passed to duckdb.from_arrow():

import duckdb
import pyarrow as pa

class MyArrowStream:
    def __init__(self, table):
        self._table = table

    def __arrow_c_stream__(self, requested_schema=None):
        return self._table.__arrow_c_stream__(requested_schema)

obj = MyArrowStream(pa.table({"i": [1, 2, 3]}))
print(duckdb.sql("SELECT sum(i) AS s FROM obj").fetchall())
print(duckdb.from_arrow(obj).fetchall())

[(6,)]
[(1,), (2,), (3,)]

Pandas DataFrames – object Columns

pandas.DataFrame columns of an object dtype require some special care, since this stores values of arbitrary type. To convert these columns to DuckDB, we first go through an analyze phase before converting the values. In this analyze phase a sample of all the rows of the column are analyzed to determine the target type. This sample size is by default set to 1000. If the type picked during the analyze step is incorrect, this will result in Invalid Input Error: Failed to cast value, in which case you will need to increase the sample size. The sample size can be changed by setting the pandas_analyze_sample config option.

# example setting the sample size to 100k
duckdb.execute("SET GLOBAL pandas_analyze_sample = 100_000")

Appending Rows

Use the appender to insert rows produced by Python. The with block writes the rows to the table when it finishes without an exception:

duckdb.execute("CREATE TABLE people (id INTEGER, name VARCHAR)")
with duckdb.appender("people") as appender:
    appender.append_row(1, "Mark")