SQLAlchemy + Pandas: saving array of strings to Postgres saves them as array of chars

I think you need to convert the string from the csv into an array first using converters argument in read_csv() before calling to_sql.

This example assumes they are stored separated by commas themselves. I used hobbies as the name of my array column. Once the value was converted to a list I did not seem to pass dtype to to_sql but if you still need that then you would use dtype={'hobbies': ARRAY(TEXT)} in this example. I think ARRAY and TEXT are the correct types, not to be confused with array or text. I define my column array type in my sqlalchemy model below.

import sys
from io import StringIO
from sqlalchemy import (
    create_engine,
    Integer,
    String,
)
from sqlalchemy.schema import (
    Column,
)
from sqlalchemy.sql import select
from sqlalchemy.orm import declarative_base
import pandas as pd
from sqlalchemy.dialects.postgresql import ARRAY, TEXT

Base = declarative_base()


username, password, db = sys.argv[1:4]


engine = create_engine(f"postgresql+psycopg2://{username}:{password}@/{db}", echo=False)


class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    name = Column(String(8), index=True)
    hobbies = Column(ARRAY(TEXT))


Base.metadata.create_all(engine)

csv_content = '''"name","hobbies"
1,"User 1","running,jumping"
2,"User 2","sitting,sleeping"
'''

with engine.begin() as conn:
    def convert_to_array(v):
        return [s.strip() for s in v.split(',') if s.strip()]

    content = StringIO(csv_content)
    df = pd.read_csv(content, converters={'hobbies': convert_to_array})
    df.to_sql('users', schema="public", con=conn, if_exists='append', index_label='id')


with engine.begin() as conn:
    for user in conn.execute(select(User)).all():
        print(user.id, user.name, user.hobbies)
        print ("|".join(user.hobbies))
        print (type(user.hobbies))