CODEWITHSUNDEEP
pythonsql-server
asmgx
asmgx

Reputation: 8004

Python does not update SQL Server table

I am trying to update SQL Server table through Python. But unfortunately it does not update.

I get message successful but no data was updated.

If I call the same SQL script from within SQL Server, it updates correctly.

Let me show you my script: this is my Python code:

PredString = '99'

conn = pymssql.connect(server="MyServer", database="MyDB", port="1433", user="****", password="******")
dfUpdate = pd.read_sql("EXEC UpdatePredictions '" + PredString + "'", conn)
conn.close()

print(dfUpdate)

This is the SQL Server stored procedure:

alter procedure UpdatePredictions 
    (@PredString varchar(max)) 
as
begin
    update MyTable 
    set PredMths = @PredString 

    select 'Updated.'
end

When I run Python code I get "Updated" but actually no record was updated

But when I call from SQL Server:

 EXEC UpdatePredictions '99'

I get message "Updated" and records are actually updated

What am I doing wrong here? How can I get Python to update the table?

Upvotes: 1

Views: 1307

Answers (1)

asmgx
asmgx

Reputation: 8004

Thanks to the guys who commented the answer.

As no one have made it as an answer, I will so I can mark it, so other people can find the answer easily in the future.

the problem was that Python connection wasn't committing update statement.

therefore I have to add this line after sending the update 

conn.commit()

Upvotes: 4

Related Questions