[Python] Python SQLAlchemy PostgreSQL psycopg3 driver - WRONG ARRAY TEXT[] insertion -...

class VerificationRowsHeaders(Base):
__tablename__ = 'VerificationRowsHeaders'
__table_args__ = (
ForeignKeyConstraint(['file_id'], ['schema.Upload.file_id'], ondelete='CASCADE', onupdate='CASCADE', name='fk_Upload_file_id'),
PrimaryKeyConstraint('file_id', name='VerificationRowsHeaders_pkey'),
{'schema': 'schema'}
)

file_id: Mapped[str] = mapped_column(String(36), primary_key=True)
created_at: Mapped[datetime.datetime] = mapped_column(DateTime(True))
headers: Mapped[Optional

• ] = mapped_column(ARRAY(Text()))
  updated_at: Mapped[Optional[datetime.datetime]] = mapped_column(DateTime(True))
  
  
  connection_str = f"postgresql+psycopg_async://{os.getenv("DATABASE_USER")}:{os.getenv("DATABASE_PASSWORD")}@{os.getenv("DATABASE_HOST")}/{os.getenv("DATABASE_NAME")}"
  self._engine = create_async_engine(
  connection_str,
  pool_size=sqlalchemy_options.pool_size,
  max_overflow=sqlalchemy_options.max_overflow,
  pool_timeout=sqlalchemy_options.pool_timeout
  )
  self._sessionmaker = async_sessionmaker(bind=self._engine, autocommit=False, expire_on_commit=False, class_=AsyncSession)
  
  async def insert_verification_rows_headers(self, data: verification_rows_headers_sqla_model) -> Union[int, None]:
  try:
  async with self._db_session() as session:
  async with session.begin():
  session.add(data)
  return 1
  except:
  self.logger.error(f"[{self.__class__.__name__}.{inspect.currentframe().f_code.co_name}] - {return_current_datetime()} {traceback.format_exc()}")
  return None
  
  
  
  await self.database_service.insert_verification_rows_headers(
  data=verification_rows_headers_sqla_model(
  file_id="123456",
  headers=["toto", "tata", "titi"],
  created_at=now,
  updated_at=now
  )
  )
  
  
  The resulting inserted TEXT[] become {toto;tata;titi} instead of {toto,tata,titi}. After digging a bit, with other ORM like Prisma or Manual SQL it works perfectly. This is 100% related to sqlalchemy driver or the way I pass the list.
  
  https://www.postgresql.org/docs/current/arrays.html#ARRAYS-IO <- don't help at all, ; should be used for box types
  
  SELECT typdelim FROM pg_type WHERE typname = 'text'; = ,
  SELECT typdelim FROM pg_type WHERE typname = '_text'; = ,
  
  
  from sqlalchemy.dialects.postgresql import array as postgresql_array
  
  
  encoding ["toto", "tata", "titi"] with postgresql_array don't resolve the problem...
  
  Absolutely not understand where the problem is... Can't manage to insert a correct array with sqlaclhemy.
  
  Continue reading...