How can I turn a literal into an expression in SQLAlchemy for MYSQL?

Question

I'm using sqlalchemy to create a table in mysql. I would like to have a JSON column, with a default value, but mysql doesn't allow literals to be the default value, only expressions.

If I create the column like this:

mycolumn = sqlalchemy.Column(
        MutableDict.as_mutable(mysql.JSON),
        server_default='{}'
    )

The line for the column creation in the SQL query will look like this:

mycolumn JSON DEFAULT '{}'

But this doesn't work, I need this:

mycolumn JSON DEFAULT ('{}')

I also tried using json_object():

mycolumn = sqlalchemy.Column(
        MutableDict.as_mutable(mysql.JSON),
        server_default=func.json_object('{}', type_=JSON)
    )

but this creates:

mycolumn JSON DEFAULT json_object()

and again, this doesn't work.

How can I put the default value into an expression , like mycolumn JSON DEFAULT ('{}')?

Either default seems to work wit MariaDB - is the behaviour in MySQL different? — snakecharmerb
– snakecharmerb, Commented May 8, 2024 at 13:53
that's strange, I get mycolumn JSON NOT NULL DEFAULT ('{}') @snakecharmerb — python_user
– python_user, Commented May 8, 2024 at 15:05
@snakecharmerb thanks for clarifying, yes I can see the same in mariadb, in mysql for some reason I get mycolumn json NOT NULL DEFAULT (_utf8mb4'{}') — python_user
– python_user, Commented May 9, 2024 at 6:57

Bill Karwin · Accepted Answer · 2024-05-08 16:24:56Z

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html says:

The BLOB, TEXT, GEOMETRY, and JSON data types can be assigned a default value only if the value is written as an expression, even if the expression value is a literal.

This means you cannot have a bare literal (without the parentheses) as a default for a JSON column. You must make it an expression, i.e. put it inside parentheses as you have found works. It's okay for the expression within the parentheses to be a simple literal.

I got this to work for a model in SQLAlchemy this way:

from sqlalchemy import Table, Column, Integer, String, JSON, text

user_table = Table(
        "user_account",
        meta,
        Column("id", Integer, primary_key=True),
        Column("properties", JSON, server_default=text("('{}')"))
    )

https://docs.sqlalchemy.org/en/20/core/metadata.html#sqlalchemy.schema.Column.params.server_default says:

A text() expression will be rendered as-is, without quotes.

Collectives™ on Stack Overflow

How can I turn a literal into an expression in SQLAlchemy for MYSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related