How to execute UPDATE query with SQLAlchemy SQL-builder in python?

Question

If have a sqlalchemy database and want to run an update query. I thought I could just use the same method that I also use for SELECT commands.

Here's my code:

disk_engine = create_engine('sqlite:///test.db')
chunksize = 20000
j = 0
index_start = 1

for df in pd.read_csv('test.csv', chunksize=chunksize, 
                         iterator=True, encoding='utf-8'):
    df.index += index_start
    j+=1
    df.to_sql('data', disk_engine, if_exists='append')
    index_start = df.index[-1] + 1

...

##NOW I WANT TO UPDATE A ROW
ts = pd.read_sql_query('UPDATE data SET score = ROUND(score,1)', disk_engine);

But this throws an error:

result.pyc in _non_result(self, default) 918 if self._metadata is None: 919 raise exc.ResourceClosedError( --> 920 "This result object does not return rows. " 921 "It has been closed automatically.", 922 )

ResourceClosedError: This result object does not return rows. It has been closed automatically.

How can I update the database correctly?

Possible duplicate of SQLAlchemy: prevent automatic closing

shivsn
– shivsn

2016-07-01 19:12:21 +00:00
Commented Jul 1, 2016 at 19:12 — shivsn
– shivsn, Commented Jul 1, 2016 at 19:12
How does this answer my question?

user2212461
– user2212461

2016-07-01 19:20:49 +00:00
Commented Jul 1, 2016 at 19:20 — user2212461
– user2212461, Commented Jul 1, 2016 at 19:20

Eric Leschinski · Accepted Answer · 2023-11-28 03:03:01Z

5

If the exact string UPDATE data SET score = ROUND(score,1) is what you want to be executed, you should be able to execute that from a connection to disk_engine. For example:

sqlalchemy 1.x you can get away with just this:

conn = disk_engine.connect()
result = conn.execute('UPDATE data SET score = ROUND(score,1)')

sqlalchemy 2.x, transactions are forced upon inserts, updates, deletes:

from sqlalchemy import text 
with engine.connect() as conn:
    conn.execute(text('UPDATE data SET score = ROUND(score,1))')  
    conn.commit()

edited Nov 28, 2023 at 3:03

Eric Leschinski

155k96 gold badges423 silver badges337 bronze badges

answered Jul 1, 2016 at 19:33

Eric La Fevers

1212 bronze badges

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

Collectives™ on Stack Overflow

How to execute UPDATE query with SQLAlchemy SQL-builder in python?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related