Cannot use Postgres's ARRAY with SQLAlchemy

Question

I cannot add a 2D integer array to Postgre using this code with SQLAlchemy:

    def create_polygon(x, y, w, h):
        polygon = []
        polygon.append([1, 2])
        return polygon


    engine = create_engine('postgresql://usr:pwd@localhost:5432/db', echo=True)
    meta = MetaData()

    table = Table(
        'table', meta,
        Column('id', Integer, primary_key=True), 
        Column('polygon', ARRAY(Integer, dimensions=2))
    )
    meta.create_all(engine)

    conn = engine.connect()

    for line in lines:            
        insert = zone_by_cam.insert(
            polygon = create_polygon(*line.params)
        )

        conn.execute(insert)

I got this error message:

Additional arguments should be named <dialectname>_<argument>, got 'polygon'

I then changed the name of polygon to postgresql_polygon (which is nowhere to be found in the docs), and have this one instead:

Argument 'postgresql_polygon' is not accepted by dialect 'postgresql' on behalf of <class 'sqlalchemy.sql.dml.Insert'>

How can I fix this? Thanks!

Ilja Everilä · Accepted Answer · 2019-08-13 06:57:13Z

Table.insert() does not accept values to insert as keyword arguments, and passes any keyword arguments it does not handle to the dialect specific implementation of Insert. The error then is telling you that the implementation expects those arguments to follow the format <dialectname>_<argument>, but you gave it polygon. postgresql_polygon is not in the Postgresql dialect documentation, since no such argument exists. The initial fix is simple, pass values as a dict in the keyword argument values, or use Insert.values():

for line in lines:            
    insert = zone_by_cam.insert().values(
        polygon=create_polygon(*line.params)
    )

    conn.execute(insert)

Instead of separate inserts you could also pass all your "polygons" in a single executemany operation:

insert = zone_by_cam.insert().values([
    {'polygon': create_polygon(*line.params)} for line in lines
])

conn.execute(insert)

Collectives™ on Stack Overflow

Cannot use Postgres's ARRAY with SQLAlchemy

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related