Conversion¶

orm_loader.conversion interprets raw values for every file load, whatever the file format.

flowchart LR A[Arrow column] --> B[Policy picks the column's handler] B --> C[clean] C --> D[detect missing] D --> E[parse] E --> F[check] F --> G[values and invalid mask]

ConversionPolicy¶

ConversionPolicy is a frozen settings object passed to load_file(policy=...). It holds the settings shared by every column and one handler for each kind of column type.

Field	Meaning
`nulls`	`NullPolicy`: what counts as a missing value
`errors`	`ErrorPolicy`: what happens to unreadable and missing required values
`text`, `integer`, `floating`, `numeric`, `boolean`, `date`, `datetime`	The handler for that kind of column, each with its own settings
`handlers`	Handlers for further column types, or replacements, by SQLAlchemy type class
`columns`	Handlers for single columns by `(table_name, column_name)`

policy = ConversionPolicy(
    nulls=NullPolicy(text_markers=frozenset({"NULL"})),
    errors=ErrorPolicy(on_error="reject_row", max_rejects=0.01),
    date=DateHandler(formats=("dd/mm/yyyy",)),
    columns={("concept", "concept_code"): TextHandler(strip=False)},
)

The settings of one load, shared by every column.

The handler for a column is, in order, the entry in columns and the handler for the most specific class of the column type (the stored type of a TypeDecorator) among handlers and the built-in fields. A column type with no handler is an error.

Attributes:

Name	Type	Description
`nulls`	`NullPolicy`	What counts as a missing value.
`errors`	`ErrorPolicy`	What happens to unreadable and missing required values.
`text`	`TextHandler`	Handler for string and text columns.
`enum`	`EnumHandler`	Handler for enumerated columns.
`integer`	`IntegerHandler`	Handler for integer columns.
`floating`	`FloatHandler`	Handler for floating point columns.
`numeric`	`NumericHandler`	Handler for fixed precision columns.
`boolean`	`BooleanHandler`	Handler for boolean columns.
`date`	`DateHandler`	Handler for date columns.
`datetime`	`DateTimeHandler`	Handler for timestamp columns.
`handlers`	`Mapping[type, ColumnHandler]`	Handlers for further column types, or replacements for the built-in ones, by SQLAlchemy type class.
`columns`	`Mapping[tuple[str, str], ColumnHandler]`	Handlers for single columns by `(table_name, column_name)`.

`for_column(sa_type, table_name=None, column_name=None)` ¶

Return the handler that interprets a column.

Parameters:

Name	Type	Description	Default
`sa_type`	`TypeEngine[Any]`	Column type.	required
`table_name`	`str \| None`	Table the column belongs to.	`None`
`column_name`	`str \| None`	The column.	`None`

Returns:

Type	Description
`ColumnHandler`	The handler for the column.

Raises:

Type	Description
`PolicyError`	No handler matches the column type. Passthrough is chosen explicitly with `columns`.

NullPolicy¶

Text columns keep every nonempty value verbatim. Only empty and whitespace-only text is null.
Words such as NULL, None, N/A and NaN stay as text in text columns. Version 2 turned them into NULL, which dropped concepts with those names. To treat words as missing again, list them in text_markers, for example NullPolicy(text_markers=frozenset({"NULL", "None", "N/A", "NaN", "nan", "null", "NA", "n/a"})). Set operations add or remove words from a set you keep.
text_markers are exact, case-sensitive words that are null in every text column of the load. Matching ignores surrounding whitespace and applies the same way to every file format. To treat a word as missing in some columns only, subclass TextHandler, override missing() and register it in ConversionPolicy.columns.
empty_is_null=False keeps empty text fields as empty strings. Typed columns still treat empty fields as missing.
Typed columns accept a valid value, a missing word (null, none, na, n/a, nan and similar) or a real null, and fail otherwise.

What counts as a missing value, shared by every column handler.

Text columns keep every nonempty value verbatim apart from the markers. Typed columns treat empty text and the typed missing words as missing.

Attributes:

Name	Type	Description
`empty_is_null`	`bool`	Empty and whitespace-only text becomes null in text columns.
`text_markers`	`frozenset[str]`	Exact, case-sensitive text values treated as missing in text columns. Matching ignores surrounding whitespace.
`typed_missing_words`	`frozenset[str]`	Lowercase words treated as missing in non-text columns.

`text_missing(trimmed)` ¶

Return a mask of trimmed text values that are missing in a text column.

Parameters:

Name	Type	Description	Default
`trimmed`	`Array`	String array without surrounding whitespace.	required

Returns:

Type	Description
`Array`	Boolean array that is true where the value is missing.

`typed_missing(trimmed)` ¶

Return a mask of trimmed text values that are missing in a typed column.

ErrorPolicy¶

on_error is raise (default) or reject_row. max_rejects stops a reject_row load before the merge. A count stops the load as soon as it is passed, and a fraction is checked once all rows have been read, against the total number of rows read. A load that rejected rows logs a warning with their number and the rejects file.

What happens to a row with an unreadable value or a missing required value.

Attributes:

Name	Type	Description
`on_error`	`Literal['raise', 'reject_row']`	`"raise"` aborts on the first batch containing such a row. `"reject_row"` drops those rows and writes them to a rejects file.
`max_rejects`	`float \| None`	With `reject_row`, abort once rejected rows exceed this count, or this fraction of rows read when it is a float between 0 and 1.

Handlers¶

The handler for a column is the first match of: the entry in ConversionPolicy.columns and the handler for the most specific class of the column type. A TypeDecorator is handled as the type it stores. A column type with no handler is an error that names the table, column and type, raised before any row is processed. Loading such a column unchanged is an explicit choice: columns={("table", "column"): PassthroughHandler()}.

Handler	Column types	Accepted text	Settings
`TextHandler`	`String`, `Text`	Any nonempty text; longer than the column is an error	`strip`
`EnumHandler`	`Enum`	One of the labels the column stores	`strip`
`IntegerHandler`	`Integer`, `SmallInteger`, `BigInteger`	Optional sign and digits, with an optional all-zero fraction (`1.0`); a value outside the 16, 32 or 64 bits of the column type is an error	none
`FloatHandler`	`Float`	Decimal or exponent form; `Infinity` and values that overflow to infinity are errors	none
`NumericHandler`	`Numeric`	Plain and exponent decimal forms such as `1.5`, `.05` and `1.5E3`, converted to an Arrow decimal of the declared precision and scale without rounding; a precision without a scale has scale 0. A value that does not fit the declared digits is an error. The declaration must have a precision of at most 38 digits, see below	none
`BooleanHandler`	`Boolean`	`true`, `t`, `yes`, `y`, `1`, `on` and their opposites	`true_words`, `false_words`
`DateHandler`	`Date`	One of `formats`, validated against the calendar; year 0 is an error	`formats`
`DateTimeHandler`	`DateTime`	ISO date and time, or a date in one of `formats`; see time zones below	`formats`

Dates default to yyyymmdd, dd-mon-yyyy and yyyy-mm-dd. Ambiguous forms such as 02/01/2020 are never guessed; declare DateHandler(formats=("dd/mm/yyyy",)) or ("mm/dd/yyyy",). A timestamp column reads date-only values with its own formats.

Time zones¶

The column type decides, so no value is converted by a convention the schema did not ask for.

A DateTime(timezone=True) column requires an offset (Z or +hh:mm) and stores UTC. A value without an offset and a date-only value are errors.
A DateTime column without a time zone rejects a value with an offset.

Typed input¶

Input that already has a type, such as a Parquet column, is cast to the column type and checked like parsed text. A value that does not come back unchanged when cast back to its input type is an error, so an integer 2 in a Boolean column, a timestamp with a time in a Date column and a decimal that loses digits are all rejected. Float NaN is missing.

In a Numeric column an integer is read exactly and a float is read as its shortest decimal text, so a Parquet float column loads the same values as a CSV of the same numbers. Infinity is an error.

Numeric columns¶

The loader converts only what an Arrow decimal can hold, which is a precision of at most 38 digits. That is also the widest decimal DuckDB stores, and SQLite stores Numeric as a float. A Numeric column with a wider precision or no precision raises a PolicyError before any row is read. To load it anyway, leave the value to the database with columns={("table", "column"): PassthroughHandler()}: the database then parses the text and rounds a value that exceeds the scale, and a value it cannot parse aborts the load.

Adding a column type¶

Subclass ConvertingHandler, implement arrow_type and parse, and pass the handler in ConversionPolicy.handlers for the type class, or in ConversionPolicy.columns for a single column. clean, missing and check can be overridden.

class HexHandler(ConvertingHandler):
    def arrow_type(self, sa_type):
        return pa.int64()

    def parse(self, cleaned, sa_type):
        ...  # return Interpretation(values, invalid)

policy = ConversionPolicy(handlers={MyHexType: HexHandler()})

ConvertingHandler¶

Bases: ColumnHandler

Converts a column in four stages: clean, detect missing, parse, check.

Text input follows the stages. Input that already has a type, such as a Parquet column, is cast directly with floating NaN read as missing. A value that does not survive the cast back to its input type is lossy and unreadable, and check runs on the cast values. Subclasses supply arrow_type and parse and may override clean, missing and check.

Attributes:

Name	Type	Description
`distinct`	`bool`	Run the stages once per distinct value and gather the results back, for types whose parse step is expensive per value.

`arrow_type(sa_type)` `abstractmethod` ¶

Return the Arrow type of the converted values.

`check(values, sa_type)` ¶

Return a mask of parsed values the column cannot hold, or None when it holds any.

`clean(raw, trimmed)` ¶

Return the text that is parsed, the trimmed text by default.

`missing(trimmed, nulls)` ¶

Return a mask of values that count as missing, by the typed rules by default.

`parse(cleaned, sa_type)` `abstractmethod` ¶

Parse cleaned text in which missing values are null.

Parameters:

Name	Type	Description	Default
`cleaned`	`Array`	String array with missing values set to null.	required
`sa_type`	`TypeEngine[Any]`	Column type the values are converted to.	required

Returns:

Type	Description
`Interpretation`	Parsed values and a mask of values that did not fit.

ColumnHandler¶

Bases: ABC

Interprets the raw values of the columns of one kind of column type.

A handler is a frozen settings object. It is selected for a column by ConversionPolicy.for_column and holds no state about the column.

`expected(sa_type)` ¶

Return the name reported in an error for an unreadable value.

`interpret(arr, sa_type, nulls)` `abstractmethod` ¶

Interpret a raw column without raising on unreadable values.

Parameters:

Name	Type	Description	Default
`arr`	`Array \| ChunkedArray`	Raw column.	required
`sa_type`	`TypeEngine[Any]`	Column type the values are converted to.	required
`nulls`	`NullPolicy`	What counts as a missing value.	required

Returns:

Type	Description
`Interpretation`	Converted values with nulls where a value is missing, and a mask that is true where a value was unreadable.

`validate(sa_type)` ¶

Raise ValueError when the handler cannot read the column type as declared.

Conversion¶

ConversionPolicy¶

for_column(sa_type, table_name=None, column_name=None) ¶

NullPolicy¶

text_missing(trimmed) ¶

typed_missing(trimmed) ¶

ErrorPolicy¶

Handlers¶

Time zones¶

Typed input¶

Numeric columns¶

Adding a column type¶

ConvertingHandler¶

arrow_type(sa_type) abstractmethod ¶

check(values, sa_type) ¶

clean(raw, trimmed) ¶

missing(trimmed, nulls) ¶

parse(cleaned, sa_type) abstractmethod ¶

ColumnHandler¶

expected(sa_type) ¶

interpret(arr, sa_type, nulls) abstractmethod ¶

validate(sa_type) ¶

`for_column(sa_type, table_name=None, column_name=None)` ¶

`text_missing(trimmed)` ¶

`typed_missing(trimmed)` ¶

`arrow_type(sa_type)` `abstractmethod` ¶

`check(values, sa_type)` ¶

`clean(raw, trimmed)` ¶

`missing(trimmed, nulls)` ¶

`parse(cleaned, sa_type)` `abstractmethod` ¶

`expected(sa_type)` ¶

`interpret(arr, sa_type, nulls)` `abstractmethod` ¶

`validate(sa_type)` ¶