CODEWITHSUNDEEP
pythonsql-server-2008sqlalchemyflask-sqlalchemy
machazthegamer
machazthegamer

Reputation: 613

Dealing with triggers in SQLAlchemy when inserting into table

I have used flask-SQLAlchemy to map one of the tables called Proposal in a Database but I ran into a problem when inserting into the database from the app. Here is the error:

sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42000', "[42000]

[Microsoft][ODBC SQL Server Driver][SQL Server]The target table 'Proposal' of
the DML statement cannot have any enabled triggers if the statement contain an
OUTPUT clause without INTO clause. (334) (SQLExecDirectW); [42000] [Microsoft]
[ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared

The database is of MSSQL type.

The sql statement generated by SQLAlchemy is

SQL: 'INSERT INTO [Proposal] (proposal_fee, contract1, contract,
proposal_disb, source, sid, value_per_hr, team_hrs, reason_for_loss,
incharge, [Country_id], [manner], [Manager_id], preparer_id,
task_id, date_received, proposal_deadline, industry_id,
proposal_currency_id, contract_currency_id, service_description_id,
business_unit_id, status_id, associated_cost) OUTPUT inserted.[ID] VALUE
(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)']
[parameters: ('', '', '', '', '', 'None', '', '', None, 'Adv,
Office', 'Select Country', None, 'ABCD', 'some name', '1007000', '', '', '', '', 'usd', None, None, '', '')]

I know that when I disable the triggers the insert will work but I need a solution from within sqlalchemy that can navigate around this problem?

Is there any way to do it?

Upvotes: 4

Views: 5954

Answers (2)

TabsNotSpaces
TabsNotSpaces

Reputation: 1357

If you are mapping over your table class, this will not work. For SqlAlchemy 1.2.7, implicit_returning can be turned off by setting the arg inline=True for insert() (documentation - check the "input" arg in the insert section).

Here's my implementation, which also utilizes the sessionmaker to run the query:

from sqlalchemy.orm import mapper, sessionmaker
from sqlalchemy import MetaData, create_engine, Table

class User(object):
    pass

def CreateUser(event):
    #Retrieve event
    email = event["email"]
    firstName = event["firstName"]
    lastName = event["lastName"]

    #Create connection
    conn_str = "mssql+pymssql://<username>:<password>@<rds_host>:1433/<db_name>"
    engine = create_engine(conn_str)
    metadata = MetaData(engine, reflect=True)

    #Create session
    Session = sessionmaker()
    Session.configure(bind=engine)
    session = Session()

    #Retrieve DB table
    users = Table("tblUser", metadata, autoload=True)
    mapper(User, users)

    #Insert record
    isInline = True
    insert = users.insert(None, isInline).values(FirstName=firstName, LastName=lastName, Email=email)
    session.execute(insert)
    session.commit()

Upvotes: 4

machazthegamer
machazthegamer

Reputation: 613

I realized that I have to remove that output clause and have found the recommended solution to use :

__table_args__ = {'implicit_returning':False}

when creating the class for the Proposal table.

Upvotes: 6

Related Questions