I have the following code:
SQLQuery="UPDATE Players SET [Player Name] = REPLACE(REPLACE([Player Name], 'Christian', 'Chris') , 'Reece', 'John')"
df = pd.read_sql(SQLQuery, sqlcon)
However, when I run the code I get the following error:
ResourceClosedError: This result object does not return rows. It has been closed automatically.
I ran the same SQL code in SSMS and it works perfectly fine. Why could this be happening?
You should just execute the query, not try to read the result into a dataframe, since there are no results for an UPDATE query.
SQLQuery="UPDATE Players SET [Player Name] = REPLACE(REPLACE([Player Name], 'Christian', 'Chris') , 'Reece', 'John')"
sqlcon.execute(SQLQuery)
pd.read_sql is meant to retrieve data from a sql database, your query is trying to make an update in the database.
For doing a simple update you can use SQLAlchemy execute, From the documentation the syntax is the following:
from sqlalchemy import text
with engine.connect() as connection:
result = connection.execute(text("select username from users"))
for row in result:
print("username:", row['username'])
There are other possible syntax and styles if you use for instance an ORM etc.
