sqlalchemy DML statement cannot have any enabled triggers

Question

I'm using SQLAlchemy and MSSQL 2019 with triggers (insert/update) can't remove .

engine_new = create_engine('mssql+pymssql://***:***@***/***', implicit_returning=False)
Session_new = sessionmaker(bind=engine_new)
session_new = Session_new()
metadata_new = MetaData()
metadata_new.reflect(bind=engine_new)

partsTable = metadata_new.tables['parts']

def addPart(dataArt, manId)
# rowInsertStmt = insert(partsTable).values(
rowInsertStmt = partsTable.insert().values(
                part_kod = dataArt.get('kod'),                
                part_description = dataArt.get('title'),
                part_kod_supplier = dataArt.get('supplier'),
                part_manufacturer = manId
            )

        session_new.execute(rowInsertStmt)

        session_new.commit()

dataArt - list with parts descriptions
manId - is of manufactures

Error message:

The target table 'parts' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.DB-Lib error message 20018

Now, I think that SQLalchemy can't work with MSSQL triggers. Because no working solution was found on the web for my situation, but in official documents this problem is not highlighted.

Any advice, please? It possible use SQLalchemy without OUTPUT? Drop SQLalchemy and use direct SQL requests?

Does this answer your question? Dealing with triggers in SQLAlchemy when inserting into table — Thom A, Commented Apr 14 at 8:46
see also docs.sqlalchemy.org/en/20/core/… and docs.sqlalchemy.org/en/20/dialects/mssql.html#insert-behavior — siggemannen, Commented Apr 14 at 8:49
@ThomA I saw that and used it in the first line. But without result( — Stanislav, Commented Apr 14 at 8:49
Used what, @Stanislav ? 'implicit_returning':False? That, according to both the answer and documentation, goes in the definition of your class, not the "first line". — Thom A, Commented Apr 14 at 8:51
@ThomA Yes, I user it but without result. ` class Parts(Base): tablename = 'parts' table_args = {'implicit_returning':False} part_id = Column(Integer, primary_key=True, autoincrement=True) part_hash = Column(String(50), unique=True) ` — Stanislav, Commented Apr 14 at 9:16

Stanislav · Accepted Answer · 2025-04-14 12:23:24Z

Thanks for @ThomA!

The solution is using method add with Session like:

with Session(autoflush=False, bind=engine_new) as db:

                new_part = Parts(                    
                    part_kod=dataArt.get('kod'),
                    part_description=dataArt.get('title'),
                    part_kod_supplier=dataArt.get('supplier'),
                    part_manufacturer=manId
                )

                db.add(new_part)
                db.commit()

While using

rowInsertStmt = insert(partsTable).values(...)
#or this rowInsertStmt = partsTable.insert().values(...)
session_new.execute(rowInsertStmt)
session_new.commit()

You will see an error in the topic.

I use the same solution with 'insert' for processing 'manufactures' and it works well, but with one sql trigger.

Collectives™ on Stack Overflow

sqlalchemy DML statement cannot have any enabled triggers

1 Answer 1

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Linked

Related