SQLAlchemy execute query but does not update despite raw query working fine in a SQL IDE

Question

I have a query that works fine in a SQL editor:

UPDATE users mu 
JOIN (SELECT 
        min.user_id as user_id, 
        (max.total_followers - min.total_followers) as progression
    FROM(select user_id, measurement_date, total_followers
    from followers_totals ft 
        where measurement_date = (select max(measurement_date) from followers_totals as f where f.user_id = ft.user_id
        and date_format(ft.measurement_date, '%%Y-%%m-%%d') >= date_format(date_sub(CURDATE(), interval 7 day), '%%Y-%%m-%%d'))) max
    JOIN (select user_id, measurement_date, total_followers
        from followers_totals ft 
        where measurement_date = (select min(measurement_date) from followers_totals as f where f.user_id = ft.user_id
        and date_format(ft.measurement_date, '%%Y-%%m-%%d') >= date_format(date_sub(CURDATE(), interval 7 day), '%%Y-%%m-%%d'))) min
    ON max.user_id = min.user_id
    WHERE min.user_id = '123456' and max.user_id = '123456') progression 
ON progression.user_id = mu.user_id
SET mu.followers_count_progress_7D = progression.progression
WHERE progression.user_id is not null;

I try to execute the same query from SQLAlchemy using the execute function:

import sqlalchemy
from sqlalchemy import create_engine, Table, MetaData, exc

eng = create_engine('mysql://xxxxxxxxxxxxxxxxxxxxxxxxxxxx')
con = eng.connect()    

try:
    query = """UPDATE users mu 
                JOIN (SELECT 
                        min.user_id as user_id, 
                        (max.total_followers - min.total_followers) as progression
                    FROM(select user_id, measurement_date, total_followers
                    from followers_totals ft 
                        where measurement_date = (select max(measurement_date) from followers_totals as f where f.user_id = ft.user_id
                        and date_format(ft.measurement_date, '%%Y-%%m-%%d') >= date_format(date_sub(CURDATE(), interval 7 day), '%%Y-%%m-%%d'))) max
                    JOIN (select user_id, measurement_date, total_followers
                        from followers_totals ft 
                        where measurement_date = (select min(measurement_date) from followers_totals as f where f.user_id = ft.user_id
                        and date_format(ft.measurement_date, '%%Y-%%m-%%d') >= date_format(date_sub(CURDATE(), interval 7 day), '%%Y-%%m-%%d'))) min
                    ON max.user_id = min.user_id
                    WHERE min.user_id = '123456' and max.user_id = '123456') progression 
                ON progression.user_id = mu.user_id
                SET mu.followers_count_progress_7D = progression.progression
                WHERE progression.user_id is not null;"""
    rs = con.execute(query)
    print(rs)
except exc.SQLAlchemyError as e:
    print (e)

No exception is returned and print(rs) result in a return proxy as expected. However the db does not get updated with SQLAlchemy while it is updated with a SQL editor. Is there some part of my query that is not supported by SQL Alchemy?

I initially thought it would be the escape of the % in the date format, but different tests show that simpler queries run as expected using this escape writing.

EDIT: after using echo=True in the engine creation as suggested above I can see that the query formatting is preserve, the commit is done. I copied pasted the ouput of the echo to a sql editor and the query works well, but with sqlalchemy it does not update at all.

EDIT2: tried adding autocommit=True with same result.... Logs are showing:

2021-02-14 11:21:21,387 INFO sqlalchemy.engine.base.Engine ()
2021-02-14 11:21:21,389 INFO sqlalchemy.engine.base.Engine UPDATE users mu 
                JOIN (
                    SELECT min.user_id as user_id, 
                        (max.total_followers - min.total_followers) as progression 
                    FROM(
                        select user_id, measurement_date, total_followers 
                 ....
                ON progression.user_id = mu.user_id 
                SET mu.followers_count_progress_7D = progression.progression 
                WHERE progression.user_id is not null;
2021-02-14 11:21:21,389 INFO sqlalchemy.engine.base.Engine ()
2021-02-14 11:21:21,393 INFO sqlalchemy.engine.base.Engine COMMIT
0

The user used to connect has all permissions:

GRANT ALL ON *.* TO 'user1'@'%';

Simpler update queries run on SQLAlchemy are actually working.

EDIT 3: Interestingly it seems that this only happens for certain ids, but not all. How can something ID dependant work remotely but not locally...?

Answer 1

inserts, updates and deletes stopped working in sqlalchemy

You updated sqlalchemy 1.x to 2.x and now .execute("...") has no effect for inserts, updates, and deletes. It's because sqlalchemy 2.x forced named transactions and your transaction is getting rolled back. Try this instead:

from sqlalchemy import create_engine, sql, text, exc

#passing echo=True to create_engine displays connection and commit status
engine = create_engine('postgresql://postgres:your_configs', echo=True)

def myengine_execute(sql):
    try:
        with engine.connect() as conn:
            conn.execute(text(sql))
            conn.commit()
    except exc.SQLAlchemyError as e:
        print(e)

result = myengine_execute("update mytable set thing = 9 where index = 9001")

Prints:

INFO sqlalchemy.engine.Engine select pg_catalog.version()
INFO sqlalchemy.engine.Engine [raw sql] {}
INFO sqlalchemy.engine.Engine select current_schema()
INFO sqlalchemy.engine.Engine [raw sql] {}
INFO sqlalchemy.engine.Engine show standard_conforming_strings
INFO sqlalchemy.engine.Engine [raw sql] {}
INFO sqlalchemy.engine.Engine BEGIN (implicit)
INFO sqlalchemy.engine.Engine update mytable set thing = 9 where index = 9001
INFO sqlalchemy.engine.Engine [generated in 0.00019s] {}
INFO sqlalchemy.engine.Engine COMMIT

Answer 2

Since the debug printing didn't seem to give enough info to solve the issue, I'm going to assume that it is indeed an issue with actually committing changes to the DB, so, like other people have mentioned in various other questions (such as: setting autocommit to 1 in mysql), you should try explicitly using autocommit=True.

You can test this either with a with statement such as:

with engine.connect().execution_options(autocommit=True) as conn:
    conn.execute(query)

or just appending .execution_options(autocommit=True) to your existing code:

conn.execute(query).execution_options(autocommit=True)

Note though that execution_option's autocommit parameter will be deprecated with SQLAlchemy 1.4 and that the replacement will be to set transaction isolation levels as shown here.

Just to reiterate, it seems like MySQL sets the autocommit value to 0 internally, meaning that it uses a transaction which needs to be .commit()ed to propagate it to the DB. Hope that actually solves the issue as I'm not set up to test this on my machine at the moment.