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](http://stackoverflow.com/questions/14375666/sqlalchemy-prevent-automatic-closing) — shivsn, Jul 01 '16 at 19:12

score 2 · Answer 1 · answered Jul 01 '16 at 19:33

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:

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

Though I can't tell if/how the update statement is supposed to change based on your example code, so this might not be what you are looking for.

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

1 Answers1