Python does not update SQL Server table

Question

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?

# you must call commit() to persist your data if you don't set autocommit to True conn.commit() — LinPy
– LinPy, Commented Aug 20, 2019 at 6:28
no, it is Stored procedure in SQL it commits after finishing the SP automatically — asmgx
– asmgx, Commented Aug 20, 2019 at 6:29
Before closing connection call conn.commit(), In python script. — Manjunath Rao
– Manjunath Rao, Commented Aug 20, 2019 at 6:31

asmgx · Accepted Answer · 2019-08-20 22:29:45Z

4

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()

answered Aug 20, 2019 at 22:29

asmgx

8,14819 gold badges102 silver badges184 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nimbusgb Nov 27, 2024 at 16:24

5 years and 3 month later ....... Just had the same issue. NONE of the multitude of python SQL code snippets or tutorials mention commit!

Collectives™ on Stack Overflow

Python does not update SQL Server table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related