Why am I getting an error when using the UPDATE function with SQLAlchemy?-CodePudding

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?

CodePudding user response：

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.

CodePudding user response：

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)