The DuckDBPyType class, duckdb.sqltypes.DuckDBPyType, represents one of the data types. Use DuckDBPyType to describe SQL types when declaring function parameters, return types or casts. Python types such as int are accepted too: int describes BIGINT. Converting a data value such as 5 follows different rules; see Python Object Conversion.

Converting from Other Types

Wherever a DuckDBPyType is expected, any of the options listed below can be given instead.

Python Built-Ins

The table below shows the mapping of Python Built-in types to DuckDB type.

NumPy Scalar Types

The table below shows the mapping of NumPy scalar types to DuckDB type. A NumPy dtype object does not convert: DuckDBPyType(numpy.dtype("int32")) raises a NotImplementedException.

Type Names

A string is read as a SQL type name, such as "INTEGER", "INT[]", "STRUCT(a INT)" or "MAP(INT, VARCHAR)":

import duckdb
from duckdb.sqltypes import DuckDBPyType

print(DuckDBPyType("MAP(INT, VARCHAR)"))

MAP(INTEGER, VARCHAR)

A type created with CREATE TYPE exists only in its database, so name the connection: DuckDBPyType("mood", connection=con) or con.type("mood"). Without a connection, the name is looked up on the default connection.

con = duckdb.connect()
con.execute("CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy')")
print(con.type("mood"))

ENUM('sad', 'ok', 'happy')

Nested Types

list[child_type]

list type objects map to a LIST type of the child type, which can itself be nested.

import duckdb.sqltypes

print(duckdb.sqltypes.DuckDBPyType(list[int]))

BIGINT[]

dict[key_type, value_type]

dict type objects map to a MAP type of the key type and the value type.

import duckdb.sqltypes

print(duckdb.sqltypes.DuckDBPyType(dict[str, int]))

MAP(VARCHAR, BIGINT)

{'a': field_one, 'b': field_two, ..., 'n': field_n}

dict objects map to a STRUCT composed of the keys and values of the dict.

import duckdb.sqltypes

print(duckdb.sqltypes.DuckDBPyType({'a': str, 'b': int}))

STRUCT(a VARCHAR, b BIGINT)

Union[type_1, ... type_n]

typing.Union objects, and unions written as type_1 | type_2, map to a UNION type of the provided types.

import duckdb.sqltypes
from typing import Union

print(duckdb.sqltypes.DuckDBPyType(Union[int, str, bool, bytearray]))
print(duckdb.sqltypes.DuckDBPyType(int | str))

UNION(u1 BIGINT, u2 VARCHAR, u3 BOOLEAN, u4 BLOB)
UNION(u1 BIGINT, u2 VARCHAR)

Creation Functions

For the built-in types, you can use the constants defined in duckdb.sqltypes:

For the complex types there are methods available on the DuckDBPyConnection object or the duckdb module. Anywhere a DuckDBPyType is accepted, we will also accept one of the type objects that can implicitly convert to a DuckDBPyType.

from duckdb.sqltypes import INTEGER, VARCHAR

print(duckdb.list_type(INTEGER))
print(duckdb.array_type(INTEGER, 3))
print(duckdb.struct_type({"a": INTEGER, "b": VARCHAR}))
print(duckdb.map_type(VARCHAR, INTEGER))
print(duckdb.decimal_type(10, 2))

INTEGER[]
INTEGER[3]
STRUCT(a INTEGER, b VARCHAR)
MAP(VARCHAR, INTEGER)
DECIMAL(10,2)

list_type

Parameters:

• type: DuckDBPyType

array_type

Parameters:

• type: DuckDBPyType
• size: int

struct_type | row_type

Parameters:

• fields: Union[list[DuckDBPyType], dict[str, DuckDBPyType]]

A list names the fields v1, v2 and so on. An empty dict or list gives an empty STRUCT.

map_type

Parameters:

• key: DuckDBPyType
• value: DuckDBPyType

decimal_type

Parameters:

• width: int
• scale: int

A DECIMAL has a width of 1 to 38 and a scale of 0 to the width.

union_type

Parameters:

• members: Union[list[DuckDBPyType], dict[str, DuckDBPyType]]

string_type

Parameters:

• collation: str, by default ""

enum_type

enum_type is not implemented and raises a NotImplementedException. Create an ENUM type with SQL and get it with con.type(), as shown under Type Names.

Inspecting Types

A DuckDBPyType has an id, such as 'struct'. A nested type, an ENUM or a DECIMAL also has children, a list of pairs of a name and a type or a value. The field of a STRUCT type is also available by attribute or by key:

point = duckdb.struct_type({"x": INTEGER, "y": VARCHAR})
print(point.id)
print(point.children)
print(point.x, point["y"])

struct
[('x', INTEGER), ('y', VARCHAR)]
INTEGER VARCHAR