BUG: pd.to_sql fails with an ARRAY of composites

[sergiykhan]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd
from sqlalchemy import (
    create_engine,
    Column,
    Table,
    Text,
    Integer,
    select,
)
from sqlalchemy.orm import (
    declarative_base,
    sessionmaker,
)
from sqlalchemy.engine import (
    URL,
)
from sqlalchemy_utils import (
    CompositeType,
)
from sqlalchemy.dialects.postgresql import (
    ARRAY,
)

engine = create_engine(
    url=URL.create(
        'postgresql',
        username='',
        database='',
    ),
)

Session = sessionmaker(engine)

Base = declarative_base()

table_name = 'test'

failure_mode = 2

if failure_mode == 1:
    # Option #1: Strings of length 1 in the composite type
    # StatementError: (builtins.IndexError) string index out of range
    df = pd.DataFrame(
        {
            'id': [1, 2],
            'comp': [
                [('aa', 't'), ('bb', 'tt')],
                [('cc', 'tt'), ('dd', 'tt')],
            ]
        }
    )
    comp_type = CompositeType(
        'comptype',
        [
            Column('A', Text),
            Column('B', Text),
        ]
    )

elif failure_mode == 2:
    # Option #2: Integers in the composite type
    # StatementError: (builtins.TypeError) 'int' object is not subscriptable
    df = pd.DataFrame(
        {
            'id': [1, 2],
            'comp': [
                [('aa', 3), ('bb', 4)],
                [('cc', 5), ('dd', 6)],
            ]
        }
    )
    comp_type = CompositeType(
        'comptype',
        [
            Column('A', Text),
            Column('B', Integer),
        ]
    )


class Test(Base):
    __table__ = Table(
        table_name,
        Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('comp', ARRAY(comp_type, dimensions=1)),
    )

Base.metadata.drop_all(engine, tables=[Base.metadata.tables[table_name]])
Base.metadata.create_all(engine, tables=[Base.metadata.tables[table_name]])

with Session.begin() as session:
    df.to_sql(
        table_name,
        con=session.connection(),
        index=False,
        if_exists='append',
        dtype={
            'comp': ARRAY(comp_type),
        },
    )

Issue Description

Let me say first that to_sql() appears to work fine with arrays of known Postgres data types, for example ARRAY(Text).

In this case, I am writing an ARRAY(composite_type) into a Postgres table. The corresponding dataframe column contains a list of tuples, which correspond to the Postgres composite type.

First, it appears that for composite types, one must specify the datatype in dtype to avoid the DatatypeMismatch error.

Second, to_sql() fails with different error messages depending on the specifics of the composite type or the actual data. For example, here are the two failure modes that I have discovered:

1. a composite type contains an Integer results in StatementError: (builtins.TypeError) 'int' object is not subscriptable
2. a composite type contains a Text, but one of the values is one-character long results in StatementError: (builtins.IndexError) string index out of range

Note that if the composite type if made of Text fields only and all strings contain more than 1 character, to_sql writes data successfully.

This appears to be an issue with to_sql() that in turn makes SQLAlchemy throw an error.

Expected Behavior

I expect to_sql to successfully write arrays of composites.

Here is an example (for case 2 above) showing that arrays of composites get written successfully in SQLAlchemy ORM.

rec1 = Test()
rec1.id = 1
rec1.comp = [('aa', 3), ('bb', 4)]

rec2 = Test()
rec2.id = 2
rec2.comp = [('cc', 5), ('dd', 6)]

with Session.begin() as session:
    session.add(rec1)
    session.add(rec2)

Installed Versions

INSTALLED VERSIONS

commit : 0f43794
python : 3.9.17.final.0
python-bits : 64
OS : Linux
OS-release : 5.10.102.1-microsoft-standard-WSL2
Version : #1 SMP Wed Mar 2 00:30:59 UTC 2022
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : en_US.UTF-8
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 2.0.3
numpy : 1.24.2
pytz : 2023.3
dateutil : 2.8.2
setuptools : 58.1.0
pip : 23.2.1
Cython : 3.0.0
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : 2.9.6
jinja2 : None
IPython : 8.12.0
pandas_datareader: None
bs4 : None
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.7.1
numba : None
numexpr : None
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : None
snappy : None
sqlalchemy : 2.0.20
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None