I want to set the default value of my SQLAlchemy postgres JSON column to an empty dictionary.
from sqlalchemy.dialects.postgresql import JSON
info = Column(JSON, default='{}')
info = Column(JSON, default={})
Neither of these work.
4 Answers
Using default=lambda: {} works. Credit goes to univerio in the comments.
8 Comments
Anthony
More clean and concise solution, use it instead.
Konrad
This no longer seems to work, have you been using the same solution?
AzFlin
@Konrad Yes I have. Maybe a newer version of SQLAlchemy broke this
ricekab
I use
default=dict which is functionally equivalent to the lambda. This still works for me using SQLAlchemy 1.3.x
eli
@ricekab that won't set the
server_default though |
The easiest way I've found to define a JSON not null (if applies) column with a default {} value is (tested with SQLAlchemy 1.3.x):
info = db.Column(JSON, nullable=False, server_default='{}')
1 Comment
AlTheOne
I did it without use
default, used only server_default='{}'Just if someone struggles like me with default and server_default in combination with a JSONB-column, what seems to work is the following:
from sqlalchemy import text
info = Column(JSONB, default=text("'{}'::jsonb"), server_default=text("'{}'::jsonb"))
The methods above (default=dict or default=lambda: {}) did not work for me. Especially in combination with alembic. I always got the error:
sqlalchemy.exc.ArgumentError: Argument 'arg' is expected to be one of type '<class 'str'>' or '<class 'sqlalchemy.sql.elements.ClauseElement'>' or '<class 'sqlalchemy.sql.elements.TextClause'>', got '<class 'type'>' (or function instead of type for the lambda).
3 Comments
Anthony
Specifying
default in alembic migration scripts is just meaningless - alembic never performs INSERTs or UPDATEs, and default is used only when there is no value for column at INSERT or UPDATE. See: docs.sqlalchemy.org/en/14/core/defaults.html. As a rule of thumb, you should use only DDL-producing SQLA-constructs (like tables, columns) in your migration scripts.
taffit
Thank you for the clarification. The answer was mainly for using it in a Python module, with SQLAlchemy as ORM where I experienced the problems (< v1.4). Btw, I'm also updating and inserting data using alembic, e. g. initial data or if data needs to be migrated, too. I've adjusted my alembic scripts so that e. g. the initial seeding data is coming from CSV-files or that I can upgrade/downgrade different "layers" of a database, from DDL, views or functions that are updated or just the data, where appropriate (as it is mostly bound to the tables and views of a certain revision).
AFOC
FYI either
default or server_default here works, and I didn't need to add ::jsonb either. For anyone who has a non-empty default, here's an example: privileges = db.Column(JSONB, nullable=False, default=text(f"'{json.dumps(default_privileges)}'")) - where default_privileges is any dict defined for this purposeBetter to use just dict constructor:
info = Column(JSON, default=dict)
Also, work perfectly for other types:
created = Column(DateTime, default=datetime.utcnow)
3 Comments
Anthony
It is strange. Could you please share a gist with your model and an use case? Also take a look: docs.sqlalchemy.org/en/14/core/…
Nam G VU
It's been a long time ago. I will return with some sample code later @tosh
Nam G VU
In the mean time, we have other who also cannot have default=dict working stackoverflow.com/a/60114072/248616
dictinstance is being used across all instances of your model. Trydefault=lambda: {}.