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 ('{}')?

Answer 1

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.