SQLAlchemy: MySQL column DDL with GENERATED ALWAYS AS

Question

I'm trying to replicate this column with sqlalchemy orm (MySQL):

`line_price_taxed` decimal(20,4) GENERATED ALWAYS AS ((`quantity` * `unit_price_taxed`)) STORED,

my attempt so far has been:

line_price_taxed = Column(DECIMAL(20, 4), server_default=text('''GENERATED ALWAYS AS ((`quantity` * `unit_price_taxed`)) STORED'''), index=True)

but I get this error: 1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT GENERATED ALWAYS AS ((`quantity` * `unit_price_taxed`)) STORED

Is there a way to pass raw SQL into the sqlalchemy ORM model?

Answer 1

According to the SQLAlchemy documentation, if you are using a version higher than 1.3.11

You can use Computed For example,

from sqlalchemy import Column, Integer, Computed

line_price_taxed = Column(DECIMAL(20, 4), Computed("quantity * unit_price_taxed"), index=True)

you can find the tutorial from SQLAlchemy Computed (GENERATED ALWAYS AS) Columns

Answer 2

The issue is that sqlalchemy automatically prepends the word DEFAULT into the column definition before your GENERATED ALWAYS AS... if you pass it to the server_default arg.

Nothing stood out to me as immediately obvious in the Column API as a way to do this, but I did come across this example where a custom compiler is used to inject some "SPECIAL DIRECTIVES" into the create column clause if the column's info dict has some particular key. It can be easily modified to achieve your desired column definition, for example:

from sqlalchemy_app import Base, engine
from sqlalchemy import Column, Integer
from sqlalchemy import schema
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.dialects.mysql import DECIMAL
from sqlalchemy.sql import text

class Model(Base):

    id = Column(Integer, primary_key=True)
    quantity = Column(Integer, nullable=False)
    unit_price_taxed = Column(DECIMAL(20, 4), nullable=False)
    line_price_taxed = Column(
        DECIMAL(20, 4),
        info={
            "generated_stmt": "GENERATED ALWAYS AS ((`quantity` * `unit_price_taxed`)) STORED"
        },
        index=True,
    )


@compiles(schema.CreateColumn)
def compile(element, compiler, **kw):
    column = element.element
    if "generated_stmt" not in column.info:
        return compiler.visit_create_column(element, **kw)
    col_name = column.name
    col_type = compiler.type_compiler.process(column.type)
    text = f"{col_name} {col_type} {column.info['generated_stmt']}"
    return text


if __name__ == "__main__":
    Base.metadata.create_all(engine)

Model.line_price_taxed is passed info={"generated_stmt": <stmt text>} and the custom compiler looks for that key to see if it needs to do the special handling. If found it renders the value of that key in line.

Have a look at the linked example as I've left out handling of column defaults, not null and constraints that is included over there.

Here's the engine logs for the above script:

2019-08-22 11:50:51,740 INFO sqlalchemy.engine.base.Engine
CREATE TABLE model (
        id INTEGER NOT NULL AUTO_INCREMENT,
        quantity INTEGER NOT NULL,
        unit_price_taxed DECIMAL(20, 4) NOT NULL,
        line_price_taxed DECIMAL(20, 4) GENERATED ALWAYS AS ((`quantity` * `unit_price_taxed`)) STORED,
        PRIMARY KEY (id)
)


2019-08-22 11:50:51,741 INFO sqlalchemy.engine.base.Engine {}
2019-08-22 11:50:51,796 INFO sqlalchemy.engine.base.Engine COMMIT
2019-08-22 11:50:51,797 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_model_line_price_taxed ON model (line_price_taxed)
2019-08-22 11:50:51,798 INFO sqlalchemy.engine.base.Engine {}
2019-08-22 11:50:51,838 INFO sqlalchemy.engine.base.Engine COMMIT