This page documents the rules for converting DuckDB results to Python and Python objects to DuckDB.

Result Conversion: DuckDB Results to Python

A result is read from a relation or, when using the DB-API / con.execute(), from the connection. con.sql(), con.table(), the readers and the other relational methods return relations. Each has its own methods:

Relations are recommended for most conversion, except when you want to do row-based work. A relation has no fetchone(), fetchmany() or fetch_df_chunk() because it is stateless, i.e. it does not hold a result itself. To get specific rows of a result with the relational API you would use limit(n).fetchall(), iter_rows() or to_arrow_reader() instead. Note that with the DB-API, after a partial read with fetchone() or fetchmany(), the df(), fetchnumpy() and Arrow methods of the connection return only the remaining rows.

Examples

Below are some examples. See the Python guides for more. To run the examples, use this table:

import duckdb

con = duckdb.connect()
con.execute("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)")
con.execute("""
    INSERT INTO items VALUES
        ('jeans', 20.0, 1),
        ('hammer', 42.2, 2),
        ('laptop', 2000.0, 1),
        ('chainsaw', 500.0, 10),
        ('iphone', 300.0, 2)
""")

Fetch as Pandas DataFrame:

df = con.execute("SELECT * FROM items").df()
print(df)

       item   value  count
0     jeans    20.0      1
1    hammer    42.2      2
2    laptop  2000.0      1
3  chainsaw   500.0     10
4    iphone   300.0      2

Fetch as dictionary of NumPy arrays:

arr = con.execute("SELECT * FROM items").fetchnumpy()
print(arr)

{'item': array(['jeans', 'hammer', 'laptop', 'chainsaw', 'iphone'], dtype=object), 'value': array([  20. ,   42.2, 2000. ,  500. ,  300. ]), 'count': array([ 1,  2,  1, 10,  2], dtype=int32)}

Fetch as an Arrow table. Converting to Pandas afterwards just for pretty printing:

tbl = con.sql("SELECT * FROM items").to_arrow_table()
print(tbl.to_pandas())

       item    value  count
0     jeans    20.00      1
1    hammer    42.20      2
2    laptop  2000.00      1
3  chainsaw   500.00     10
4    iphone   300.00      2

NumPy

• fetchnumpy() fetches the data as a dictionary of NumPy arrays. A column without NULL values is a plain array. A column with NULL values is a masked array.

Pandas

• df() fetches the data as a Pandas DataFrame
• fetchdf() and, on the connection, fetch_df() are older names of df()
• fetch_df_chunk(vectors_per_chunk) fetches the next portion of the results of con.execute() into a DataFrame. Each chunk has at most the vector size (2048 by default) times vectors_per_chunk (1 by default) rows.

Apache Arrow

• to_arrow_table() fetches the data as an Arrow table
• to_arrow_reader(batch_size) returns an Arrow record batch reader with at most batch_size rows per batch (the default is 131072)
• arrow() is an older name of to_arrow_reader(), on relations only
• A relation is also an Arrow stream through the Arrow PyCapsule Interface. pyarrow.table(rel) returns an Arrow table and polars.DataFrame(rel) a Polars DataFrame, which unlike pl() needs no pyarrow

After con.execute(), to_arrow_table() and to_arrow_reader() first fetch the rows and then convert them, so they make an extra copy of the result, and the reader returns its first batch only after the whole query ran. They warn: UserWarning: Arrow output on a DB-API result makes an extra copy of the rows, use con.sql(query).to_arrow_table() instead. The module functions duckdb.to_arrow_table() and duckdb.to_arrow_reader() read the result of duckdb.execute() and warn the same way. For Arrow output, run the query with con.sql(query).

Deprecated on relations, fetch_arrow_table(), fetch_record_batch() and fetch_arrow_reader() are deprecated aliases of to_arrow_table() and to_arrow_reader(). After con.execute(), fetch_arrow_table(), fetch_record_batch(), arrow() and pl() no longer exist, and neither do the module functions duckdb.fetch_arrow_table(), duckdb.fetch_record_batch() and duckdb.pl().

DuckDB has a single INTERVAL type that stores months, days and microseconds, so it maps onto Arrow's month_day_nano_interval type. On import, every Arrow interval and duration type, including duration[s], duration[ms], duration[us] and duration[ns], is read into that one INTERVAL type. As a result, a duration column that is passed through DuckDB and read back as Arrow is returned as a month_day_nano_interval rather than as its original duration type. This is expected. The resolution and unit of the original Arrow type are not preserved.

Polars

• pl() fetches the data of a relation as a Polars DataFrame, and pl(lazy=True) returns a Polars LazyFrame

Object Conversion: Python Object to DuckDB

Python objects are converted when they are passed as query parameters (execute(), executemany() and sql(..., params=...)), as rows of values(), as constants in expressions and as the value of a duckdb.Value. The rules are the same on all of these paths, except where a section below says otherwise.

This is a mapping of Python object types to DuckDB Logical Types:

• None → NULL
• bool → BOOLEAN
• datetime.timedelta → INTERVAL
• str → VARCHAR
• bytearray → BLOB
• memoryview → BLOB
• decimal.Decimal → DECIMAL / DOUBLE / FLOAT
• uuid.UUID → UUID

The rest of the conversion rules are as follows.

str

A str that UTF-8 cannot encode, such as one that holds a lone surrogate ("\ud800"), raises an InvalidInputException that says it cannot encode str as UTF-8. The connection stays usable.

memoryview

A memoryview converts to a BLOB of the bytes it shows. As a query parameter, it must be contiguous: a strided view such as memoryview(b"abcdef")[::2] raises BufferError: memoryview: underlying buffer is not C-contiguous. values() and expressions accept it and read its bytes in order, here b'ace'.

decimal.Decimal

A Decimal becomes a DECIMAL whose width and scale follow from its digits and its exponent: Decimal("12.5") is a DECIMAL(3,1), Decimal("1E+2") is a DECIMAL(3,0) that holds 100 and Decimal("1E-7") is a DECIMAL(8,7). A value that needs more than 38 digits becomes a DOUBLE, such as Decimal("1E+38") or Decimal(0.1), which holds the 55 decimal digits of the binary float 0.1. NaN and the infinities become FLOAT.

int

Since integers can be of arbitrary size in Python, there is not a one-to-one conversion possible for ints. An int becomes the first of these types that fits it:

• INTEGER
• BIGINT
• UBIGINT
• HUGEINT
• UHUGEINT

So 2**31 is a BIGINT, 2**63 a UBIGINT, 2**64 and -(2**63) - 1 are HUGEINT and 2**127 is a UHUGEINT. An integer that does not fit in 128 bits raises InvalidInputException: Python integer too large for 128-bit integer type. A subclass of int converts by the value it stores.

When using the DuckDB Value class, it's possible to set a target type, which will influence the conversion:

import duckdb
from duckdb.sqltypes import TINYINT

con = duckdb.connect()
print(con.execute("SELECT typeof(?), typeof(?), typeof(?)", [5, 2**64, duckdb.Value(5, TINYINT)]).fetchall())

[('INTEGER', 'HUGEINT', 'TINYINT')]

float

A float is always a DOUBLE.

datetime.datetime

For datetime we will check pandas.isnull if it's available and return NULL if it returns true.

If the datetime has tzinfo, we will use TIMESTAMPTZ, otherwise it becomes TIMESTAMP. Fetching a TIMESTAMPTZ value into Python needs the pytz package, which is not installed with DuckDB; without it, the fetch raises InvalidInputException: Required module 'pytz' failed to import.

datetime.datetime.max and datetime.datetime.min convert to ordinary timestamps, not to infinity. In the other direction, the timestamp infinity is returned as datetime.datetime.max and -infinity as datetime.datetime.min.

datetime.time

If the time has tzinfo, we will use TIMETZ, otherwise it becomes TIME.

datetime.date

date converts to the DATE type. datetime.date.max and datetime.date.min convert to ordinary dates; the date infinity is returned as datetime.date.max and -infinity as datetime.date.min.

bytes

bytes converts to BLOB by default. When it's used to construct a Value object of type BIT, it maps to BIT instead, and its bytes are read as the characters 0 and 1, as in duckdb.Value(b"0101", duckdb.sqltypes.BIT).

list

list becomes a LIST type of the “most permissive” type of its children. For example, [12345, "test"] becomes VARCHAR[], because 12345 can convert to VARCHAR but test can not convert to INTEGER:

print(con.execute("SELECT ?", [[12345, "test"]]).fetchall())

[(['12345', 'test'],)]

A NaN stays NaN inside a list, a struct, a map or a duckdb.Value of a nested type. A list that mixes datetime values with and without tzinfo becomes a TIMESTAMPTZ list as a query parameter and in values(), which read the values without tzinfo as UTC.

dict

The dict object can convert to either STRUCT(...) or MAP(..., ...), depending on its keys and on where it is used:

• A dict whose keys are all strings becomes a STRUCT with one field per key.
• A dict with exactly the two keys key and value, each holding a list and both lists of the same length, becomes a MAP of the two lists zipped together when passed as a query parameter and in values(). In an expression, it is a STRUCT like any other dict with string keys.
• A dict with a key that is not a string becomes a MAP in values() and in expressions, written as a SQL MAP literal. As a query parameter, it becomes a STRUCT whose field names are the keys converted to strings. Keys or values of types that SQL cannot combine raise an error.

A dict in the key and value form:

my_map_dict = {
    "key": [
        1, 2, 3
    ],
    "value": [
        "one", "two", "three"
    ]
}

duckdb.values(my_map_dict).show()

becomes a MAP(INTEGER, VARCHAR):

┌─────────────────────────┐
│          col0           │
│  map(integer, varchar)  │
├─────────────────────────┤
│ {1=one, 2=two, 3=three} │
└─────────────────────────┘

A dict with string keys becomes a STRUCT:

my_struct_dict = {
    "name": "duck",
    "count": 2,
    "tags": [1, 2, 3],
    "active": True
}

duckdb.values(my_struct_dict).show()

┌───────────────────────────────────────────────────────────────────────┐
│                                 col0                                  │
│ struct("name" varchar, count integer, tags integer[], active boolean) │
├───────────────────────────────────────────────────────────────────────┤
│ {'name': duck, 'count': 2, 'tags': [1, 2, 3], 'active': true}         │
└───────────────────────────────────────────────────────────────────────┘

As a query parameter, a dict with other keys becomes a STRUCT with string field names:

my_mixed_dict = {1: "one", "2": 2, "three": [1, 2, 3], False: True}
print(con.execute("SELECT typeof(?)", [my_mixed_dict]).fetchone()[0])

STRUCT("1" VARCHAR, "2" INTEGER, three INTEGER[], "False" BOOLEAN)

A dict returned by a function converts to the function's return type. For a MAP return type, a dict in the key and value form gives the entries of the map, so the return type has to describe those entries:

duckdb_conn = duckdb.connect()

def get_map() -> dict[str, list[str] | list[int]]:
    return {
        "key": [
            1, 2, 3
        ],
        "value": [
            "one", "two", "three"
        ]
    }

duckdb_conn.create_function("get_map", get_map, return_type=dict[int, str])

duckdb_conn.sql("SELECT get_map()").show()

┌─────────────────────────┐
│        get_map()        │
│  map(bigint, varchar)   │
├─────────────────────────┤
│ {1=one, 2=two, 3=three} │
└─────────────────────────┘

Without return_type, the annotation dict[str, list[str] | list[int]] is the return type, so each value, such as "one", has to convert to a UNION(u1 VARCHAR[], u2 BIGINT[]), and the query fails:

duckdb_conn.create_function("get_map_error", get_map)

duckdb_conn.sql("SELECT get_map_error()").show()

_duckdb.ConversionException: Conversion Error: Could not convert value of type VARCHAR to UNION(u1 VARCHAR[], u2 BIGINT[])

A function annotated to return a dict returns a MAP, due to automatic conversion. To return a STRUCT, the return_type has to be provided:

from duckdb import list_type, struct_type
from duckdb.sqltypes import BOOLEAN, INTEGER, VARCHAR

def get_struct() -> dict[str, str | int | list[int] | bool]:
    return my_struct_dict

duckdb_conn.create_function("get_struct_as_map", get_struct)

duckdb_conn.sql("SELECT get_struct_as_map()").show()

duckdb_conn.create_function("get_struct", get_struct, return_type=struct_type({
    "name": VARCHAR,
    "count": INTEGER,
    "tags": list_type(INTEGER),
    "active": BOOLEAN
}))

duckdb_conn.sql("SELECT get_struct()").show()

┌─────────────────────────────────────────────────────────────────────┐
│                         get_struct_as_map()                         │
│ map(varchar, union(u1 varchar, u2 bigint, u3 bigint[], u4 boolean)) │
├─────────────────────────────────────────────────────────────────────┤
│ {name=duck, count=2, tags=[1, 2, 3], active=true}                   │
└─────────────────────────────────────────────────────────────────────┘

┌───────────────────────────────────────────────────────────────────────┐
│                             get_struct()                              │
│ struct("name" varchar, count integer, tags integer[], active boolean) │
├───────────────────────────────────────────────────────────────────────┤
│ {'name': duck, 'count': 2, 'tags': [1, 2, 3], 'active': true}         │
└───────────────────────────────────────────────────────────────────────┘

A STRUCT type, as a return type or as the type of a duckdb.Value, takes each field from the dict key of the same name, ignoring case. A dict whose keys do not match the fields raises InvalidInputException: We could not convert the object ... to the desired target type.

tuple

tuple converts to LIST by default, when it's used to construct a Value object of type STRUCT it will convert to STRUCT instead.

NumPy Objects

numpy.ndarray and numpy.datetime64 are converted by calling tolist() and converting the result of that.

A NumPy integer scalar such as numpy.int64(5) is read by its value in values() and in expressions. As a query parameter, it raises NotImplementedException: Unable to transform python value of type ...; pass its Python value, numpy.int64(5).item(), instead.