SQLAlchemy, adding a column of ARRAY type throw me "can't render element of type <class 'sqlalchemy.dialects.postgresql.base.ARRAY'>"

Question

I'm trying to add a simple string / integer array type column in my sqlAlchemy Base class, but with no luck. Each time it give me an exception that it can't render the array type. I don't understand why. I am missing something?

Here is my class:

class Item_detail(Base):
    __tablename__  = 'item_detail'
    id             = Column(Integer, primary_key=True)
    detail_label   = Column(String)
    images_link    = Column(ARRAY(String()), default=[])

engine = create_engine('sqlite:///bifi.db')

from sqlalchemy.orm import sessionmaker
session = sessionmaker()
session.configure(bind=engine)
Base.metadata.create_all(engine)

But then, sqlalchemy throw me that:

File "/usr/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 79, in _compiler_dispatch
    raise exc.UnsupportedCompilationError(visitor, cls)
sqlalchemy.exc.CompileError: (in table 'item_detail', column 'images_link'): Compiler <sqlalchemy.dialects.sqlite.base.SQLiteTypeCompiler object at 0x10f9aa210> can't render element of type <class 'sqlalchemy.dialects.postgresql.base.ARRAY'>

I don't understand why I can't render it. I tried with different array type with no luck.

Answer 1

SQLAlchemy's ARRAY can be used in PostageSQL back-end currently (SQLAlchemy 1.3.18). The description for ARRAY that is written on "sqlalchemy/sql/sqltypes.py" says:

This type serves as the basis for all ARRAY operations. However, currently only the PostgreSQL backend has support for SQL arrays in SQLAlchemy.

So, if you want to use SQLAlchemy's ARRAY, you should install PostageSQL and psycopg2. Then create engine as following.

DATABASE = 'postgresql'
USER = 'postgres'
PASSWORD = 'your password'
HOST = 'localhost'
PORT = '5432'
DB_NAME = 'postgres'
engine = create_engine(f'postgresql://{USER}:{PASSWORD}@{HOST}:{PORT}/{DB_NAME }')