CODEWITHSUNDEEP
pythonpostgresqlsqlalchemy
Minh Nghĩa
Minh Nghĩa

Reputation: 1059

Cannot use Postgres's ARRAY with SQLAlchemy

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!

Upvotes: 0

Views: 425

Answers (1)

Ilja Everil&#228;
Ilja Everil&#228;

Reputation: 53017

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)

Upvotes: 1

Related Questions