hi there -

let me illustrate a proper MCVE for the behavior you describe. note also that the issue template requests that you tell us what kind of database you're using; "10.5.5" seems to be a version number and does not tell us what database this is.

Below is an illustration of the table given and your test script; it works without error as automap reflects that the "value1" column has a server default on it, so you will note it's not included in the INSERT. Please run this test case and feel free to modify it, including database in use, to illustrate how you are getting your results. thanks!

from sqlalchemy import create_engine
from sqlalchemy import table
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session


engine = create_engine("sqlite://", echo=True)

with engine.begin() as conn:
    conn.execute(
        """
        CREATE TABLE fact_table (
                id INTEGER PRIMARY KEY,
                first INTEGER,
                value1 DOUBLE DEFAULT 0,
                value2 DOUBLE
        )
"""
    )


session = Session(engine)
base = automap_base()
base.prepare(engine, reflect=True)
table = getattr(base.classes, "fact_table")
kwargs = {"first": 200016, "value2": 1.0}
entry = table(**kwargs)
session.add(entry)
session.commit()

output, after reflection queries are run:

2020-12-03 08:24:36,341 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2020-12-03 08:24:36,341 INFO sqlalchemy.engine.base.Engine INSERT INTO fact_table (first, value2) VALUES (?, ?)
2020-12-03 08:24:36,341 INFO sqlalchemy.engine.base.Engine (200016, 1.0)
2020-12-03 08:24:36,342 INFO sqlalchemy.engine.base.Engine COMMIT

Hello @zzzeek , thank you for your reply. I added information about differences between our codes. Check it now and tell me if I should provide something more.

hey there -

just for future reference, the database you're using is MariaDB as the issue does not exist on MySQL, these are important details that help us to isolate the issue.

for workaround you will need to provide a partial model with this column present:

base = automap_base()

class main_table(base):
    __tablename__ = "main_table"

    id = Column(Integer, primary_key=True)
    value1 = Column(Numeric, server_default="0.000")

base.prepare(engine, reflect=True)
table = main_table
kwargs = {"first": 200016, "value2": 1.0}
entry = table(**kwargs)
session.add(entry)
session.commit()

Mike Bayer has proposed a fix for this issue in the master branch:

Reflect decimal points in MariaDB non-quoted numeric defaults https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2397

Mike Bayer has proposed a fix for this issue in the rel_1_3 branch:

Reflect decimal points in MariaDB non-quoted numeric defaults https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2398