How to set an Array column with an empty array as default in SQLAlchemy + Postgres

Question

I want to list the sources of a piece of information. Instead of creating another table with a one to many relation, I tought I'd use the Array type.

To I tried:

app = Flask(__name__)
db = SQLAlchemy(app)

...

class Edge(db.Model):

    sources = db.Column(
        db.ARRAY(db.String),
        default=db.ARRAY(db.String)
    )

But adding an edge gives me this error:

ProgrammingError: (psycopg2.ProgrammingError) can't adapt type 'ARRAY' [SQL: 'INSERT INTO edges (child_id, parent_id, sources) VALUES (%(child_id)s, %(parent_id)s, %(sources)s'] [parameters: {'child_id': 20, 'parent_id': 26, 'sources': ARRAY(String())}]

I can't find a good tutorial on how to use an array column with a default empty array.

Thanks

ted · Accepted Answer · 2018-10-22 09:16:03Z

31

I eventually found the answer in the comments here:

sources = db.Column(
    db.ARRAY(db.String),
    server_default="{}"
)

answered Oct 22, 2018 at 9:16

ted

14.9k10 gold badges70 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LondonRob Over a year ago

I wonder how to do this in the new declarative world of Mapped[list | None]

Kinuax Over a year ago

@LondonRob, leveraging default_factory=list. More details in the proposed answer.

hjpotter92 · Accepted Answer · 2019-12-12 11:56:29Z

2

A python callable can also be set as default value:

sources = db.Column(
    db.ARRAY(db.String),
    default=dict
)

answered Dec 12, 2019 at 11:56

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

2 Comments

snakecharmerb Over a year ago

It can, but for the OP's use-case it should be default=list.

Julian Mehnle Mar 12 at 0:07

Note that the default= argument differs in meaning from the server_default= argument. The former is evaluated only on the Python side, whereas the latter is evaluated inside PostgreSQL.

Kinuax · Accepted Answer · 2024-09-27 19:06:26Z

0

Using the declarative approach:

from sqlalchemy import String
from sqlalchemy.orm import DeclarativeBase, Mapped, MappedAsDataclass, mapped_column

class Edge(MappedAsDataclass, DeclarativeBase):
    sources: Mapped[list] = mapped_column(ARRAY(String), default_factory=list)

edited Sep 27, 2024 at 19:06

answered Jun 22, 2024 at 14:44

Kinuax

5181 gold badge4 silver badges17 bronze badges

3 Comments

Damir Nafikov Over a year ago

dn why, but images: Mapped[list] = mapped_column(ARRAY(String), default_factory=list) gives me error:

Attribute 'images' ... includes dataclasses argument(s): 'default_factory' but class does not specify SQLAlchemy native dataclass configuration

Kinuax Over a year ago

@DamirNafikov, I have updated the answer with proper class definition that should fix the error you got.

Damir Nafikov Over a year ago

got it) i just set server_default="{}"

Collectives™ on Stack Overflow

How to set an Array column with an empty array as default in SQLAlchemy + Postgres

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related