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...?

I don't know anything about SQLAlchemy but I would ask MySQL to log the query so you can compare (diff) it with the one you sent. See dev.mysql.com/doc/refman/8.0/en/log-destinations.html — user9706
– user9706, Commented Feb 14, 2021 at 10:06
Can't add this as comment because 0 rep, but you can add echo=True to create_engine to debug the queries. Also, not sure whether that will make a difference but you can try closing the connection, that should forcefully commit and flush the changes if it doesn't do so by default (perhaps it doesn't for bigger queries?). — Jason Rebelo Neves
– Jason Rebelo Neves, Commented Feb 14, 2021 at 10:13
Thanks, that helps. The echo show me the exact same query, so no formatting issue here.... but update doesn't work. I copied pasted the output of the echo in the sql editor and it works.... no idea whats happening there: 2021-02-14 10:24:57,959 INFO sqlalchemy.engine.base.Engine () 2021-02-14 10:24:57,979 INFO sqlalchemy.engine.base.Engine COMMIT 0 — Vincent Teyssier
– Vincent Teyssier, Commented Feb 14, 2021 at 10:41
I print rs.rowcount, but I also have a few IDs that I identified as being updated on IDE and not in script... and check these after running the query or the scritpt — Vincent Teyssier
– Vincent Teyssier, Commented Feb 15, 2021 at 13:51

Jason Rebelo Neves · Accepted Answer · 2021-02-14 11:16:13Z

7

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.

answered Feb 14, 2021 at 11:16

Jason Rebelo Neves

1,28112 silver badges22 bronze badges

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

5 Comments

Vincent Teyssier Over a year ago

Thanks for the effort. This does not solve the issue unfortunately. In the logs I could see even before the commit message, and simpler update queries are also working (gonna edit the q with these info). I checked that the user I have have all permissions and it does,,,,

Jason Rebelo Neves Over a year ago

Shame. Thought for sure that was the issue. I remember having permission & login issues previously but SQLAlchemy definitely complained about those in a more verbose manner.

Vincent Teyssier Over a year ago

Just a quick update but it seems that I am able to run it when I use the session API instead of connection.... how crazy is that...

Jason Rebelo Neves Over a year ago

Yeah it really just smells like there's some sort of weirdo configuration issue without using the session API at this point...

fenix Over a year ago

THANKS! This works for me on sqlAlchemy and using sqlServer

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

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

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

inserts, updates and deletes stopped working in sqlalchemy

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

inserts, updates and deletes stopped working in sqlalchemy

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related