SQL Bridge

Prepare data for database insertion and handle SQL-specific conversions.

---

xpytools.xtool.sql.prepare_dataframe.prepare_dataframe

Python

prepare_dataframe(df: Any) -> Optional['pd.DataFrame']

Clean a DataFrame for safe SQL export or insertion.

• Converts list-like values into PostgreSQL array literals.
• Applies to_primitives() to ensure JSON-safe primitives.
• Replaces NaN / NA / None-like values with None.
• Returns a sanitized copy of the DataFrame.

Parameters:

Name	Type	Description	Default
df	DataFrame | Any	Input DataFrame-like object. Non-DataFrame values are returned unchanged.	required

Returns:

Type	Description
DataFrame | None	Cleaned DataFrame (or None if pandas unavailable or input invalid).

Examples:

Python Console Session

>>> import pandas as pd
>>> df = pd.DataFrame({'tags': [[1,2], [], None], 'val': [1, None, 3]})
>>> prepare_dataframe(df)
   tags  val
0  {1,2}    1
1     {}  None
2  None     3

Source code in xpytools/xtool/sql/prepare_dataframe.py

Python

def prepare_dataframe(df: Any) -> Optional["pd.DataFrame"]:
    """
    Clean a DataFrame for safe SQL export or insertion.

    - Converts list-like values into PostgreSQL array literals.
    - Applies `to_primitives()` to ensure JSON-safe primitives.
    - Replaces NaN / NA / None-like values with None.
    - Returns a sanitized copy of the DataFrame.

    Parameters
    ----------
    df : pandas.DataFrame | Any
        Input DataFrame-like object. Non-DataFrame values are returned unchanged.

    Returns
    -------
    pandas.DataFrame | None
        Cleaned DataFrame (or None if pandas unavailable or input invalid).

    Examples
    --------
    >>> import pandas as pd
    >>> df = pd.DataFrame({'tags': [[1,2], [], None], 'val': [1, None, 3]})
    >>> prepare_dataframe(df)
       tags  val
    0  {1,2}    1
    1     {}  None
    2  None     3
    """
    if not is_df(df):
        return None

    df = df.copy()

    # Normalize to PostgreSQL array literals where applicable
    df = df.map(to_pg_array)

    # Convert nested types, NaNs, dataclasses, Enums, etc. into primitives
    df = df.map(to_primitives)

    # Ensure all NA values are None (for psycopg/sqlalchemy compatibility)
    df = replace_none_like(df, force=True)

    return df

xpytools.xtool.sql.to_pg_array.to_pg_array

Python

to_pg_array(val: Any) -> Any

Convert a Python list-like object into a PostgreSQL array literal.

Parameters:

Name	Type	Description	Default
val	Any	Input value (list, tuple, or scalar).	required

Returns:

Type	Description
Any	If val is list-like, returns PostgreSQL array literal as string, otherwise returns the value unchanged.

Examples:

Python Console Session

>>> to_pg_array([1, 2, 3])
'{1,2,3}'
>>> to_pg_array("hello")
'hello'

Source code in xpytools/xtool/sql/to_pg_array.py

Python

def to_pg_array(val: Any) -> Any:
    """
    Convert a Python list-like object into a PostgreSQL array literal.

    Parameters
    ----------
    val : Any
        Input value (list, tuple, or scalar).

    Returns
    -------
    Any
        If `val` is list-like, returns PostgreSQL array literal as string,
        otherwise returns the value unchanged.

    Examples
    --------
    >>> to_pg_array([1, 2, 3])
    '{1,2,3}'
    >>> to_pg_array("hello")
    'hello'
    """
    if is_none(val):
        return None
    if is_list_like(val) and not isinstance(val, (str, bytes)):
        return "{" + ",".join(str(x) for x in val) + "}"
    return val