Question 113206 in Mysql Question posted in
The official MySQL documentation can be found here.

SQLAlchemy / mysql – How to insert data without calculated (computed) columns

I have a calculated (computed) column in a table and would like to insert rows with the calculated column not specified with SQLAlchemy.
However, the SQL query for insert generated by SQLAlchemy includes the calculated column. Is there a way not to specify a column in this case?

Please refer to the following.

class Foo(Base):
    __tablename__ = "foo"
    user_id = Column(INTEGER, ForeignKey("users.id"), nullable=False)
    calculated = Column(INTEGER, nullable=False, default=0, server_default=FetchedValue())

data = Foo(user_id=1) # even with no 'calculated' column specified, the sql query generated by SQLAlchemy includes 'calculated' 
session.add(data)
await session.commit()
Tags:

3

Answers


  1. Chosen as BEST ANSWER
    0

    It looks like mysql accepts "DEFAULT" for a generated column.

    For INSERT, REPLACE, and UPDATE, if a generated column is inserted into, replaced, or updated explicitly, the only permitted value is DEFAULT.

    https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

    The following code seems to work.

    from sqlalchemy import text

data = Foo(
  user_id=1,
  calculated=text("DEFAULT")
)
session.add(data)
await session.commit()

  2. 0

    You can use excluded_columns attribute on your Foo class, please try the below and see if that works

    class Foo(Base):
    __tablename__ = "foo"
    __excluded_columns__ = ['calculated']
    user_id = Column(INTEGER, ForeignKey("users.id"), nullable=False)
    calculated = Column(INTEGER, nullable=False, default=0, server_default=FetchedValue())
    Login or Signup to reply.
  3. 0

    The calculated column is present in the generated INSERT statement because you have default=0 in the column definition. If you remove default=0 it will not be passed, however as the column is not nullable the value must be computed immediately on the server.

    This script shows how it would work with a trigger doing the computation on the server side.

    import sqlalchemy as sa
from sqlalchemy import orm

Base = orm.declarative_base()


class Foo(Base):
    __tablename__ = 't74053080'
    id = sa.Column(sa.Integer, primary_key=True)
    name = sa.Column(sa.String(32))
    calculated = sa.Column(
        sa.INTEGER, nullable=False, server_default=sa.FetchedValue()
    )


add_trigger = sa.DDL(
    """
    CREATE TRIGGER tr BEFORE INSERT
    ON t74053080
    FOR EACH ROW SET NEW.calculated = RAND() * 1000
    """
)
sa.event.listen(Foo.__table__, 'after_create', add_trigger)


engine = sa.create_engine('mysql+pymysql:///test', echo=True, future=True)
Base.metadata.drop_all(engine, checkfirst=True)
Base.metadata.create_all(engine)
Session = orm.sessionmaker(engine, future=True)

with Session.begin() as s:
    data = Foo(name='A')
    s.add(data)

with Session() as s:
    foo = s.scalars(sa.select(Foo)).first()
    print(foo.name, foo.calculated)

    Generating this INSERT statement:

    INSERT INTO t74053080 (name) VALUES (%(name)s)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search