get_adaptive_index_dtypes

pyhelpers.dbms.utils.get_adaptive_index_dtypes(data, index=False, dtype=None, max_len=255, verbose=False)[source]

Generates SQLAlchemy types for MSSQL compatibility, specifically for indices.

This function prevents MSSQL error 42000, which occurs when attempting to use an NVARCHAR(MAX) column as an index key. It maps string-based indices to fixed-length NVARCHAR types.

Parameters:

  • data (pandas.DataFrame | pandas.io.parsers.TextFileReader | list | tuple) – Tabular data to be inspected.

  • index (bool) – Whether the index will be included in the database import. Defaults to False.

  • dtype (dict | None) – Existing dictionary mapping column names to SQLAlchemy types. Defaults to None.

  • max_len (int) – Maximum character length for the index column; defaults to 255. Note: For MSSQL, this should not exceed 450 for Unicode (NVARCHAR) columns.

  • verbose (bool | int) – Whether to print adaptive mapping information. Defaults to False.

Returns:

A dictionary of column/index names mapped to SQLAlchemy types.

Return type:

dict

Examples:

>>> from pyhelpers.dbms.utils import get_adaptive_index_dtypes
>>> from pyhelpers._cache import example_dataframe
>>> df = example_dataframe()
>>> get_adaptive_index_dtypes(df, index=True)
{'City': Unicode(length=255)}