Hi Mike and thanks for taking the time to look into that.
The query itself works fine. As mentioned it updates perfectly in mysql CLI
or using a sql client like DBeaver.
I followed your suggestion and tried using mysql.connector and the query
works fine.
In the meantime I also tried to use the *session API instead of the
connection API of SQLAlchemy and it works.*... funny.
I still wonder what the issue is with the connection API. I tried to force
autocommit=True but same issue. And the commit message is anyway already
showing in the logs.
On Sunday, 14 February 2021 at 23:22:35 UTC+8 Mike Bayer wrote:
> looks like you are matching on timestamps. is there a timezone or date
> truncation issue occurring ? check the timestamps etc. and make sure they
> match up. Create small test scripts using SQLAlchemy with echo='debug'
> to see that the rows you expect are matching. your SQL query does not
> seem to use any Python values nor anything really specific to SQLAlchemy so
> try writing a simple mysqlclient script also.
>
>
>
>
>
> On Sun, Feb 14, 2021, at 8:36 AM, Vincent T wrote:
>
> Mysql v8.0.23 on ubuntu 20.04
> SQLAlchemy 1.3.22
> Python 3.8.5
>
> I have an SQL query as shown below that updates row in database.
> When run by command line or using DBeaver SQL editor, I get 583k lines
> updated.
> When using SQLAlchemy I get only 450k lines updated. i.e. some rows get
> updated but not all.
>
> I checked the user permissions, the charset and collation, everything is
> ok.
> I managed to narrow it down to a few IDs where it fails with SQL alchemy
> but works in IDE or CLI. However the underlying data has absolutely nothing
> different.
>
> I've also tried to copy the query from the echo log and it works fine in
> IDE or CLI despite failing in SQLAlchemy python script.
>
> Please see below output logs, script with query:
>
> import time
> import sqlalchemy
> from datetime import *
> from sqlalchemy import create_engine, exc
> from dateutil.parser import parse
>
> connect_args = {'init_command':"SET
> @@collation_connection='utf8mb4_0900_ai_ci'"}
> # instantiate db connection
> eng =
> create_engine('mysql+mysqldb://xxxxxxx@localhost:3306/mydb?charset=utf8mb4',
> connect_args=connect_args, echo=True)
>
>
>
> try:
> query = """UPDATE magic_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 14 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 14 day), '%%Y-%%m-%%d'))
> ) min
> ON max.user_id = min.user_id
> WHERE min.user_id = '43691' and max.user_id = '43691')
> progression
> ON progression.user_id = mu.user_id
> SET mu.followers_count_progress_14D = progression.progression
> WHERE progression.user_id is not null;"""
>
> with eng.connect() as con:
> rs = con.execute(query)
> print(rs.rowcount)
> con.close()
> except exc.SQLAlchemyError as e:
> print (e)
>
>
> 2021-02-14 13:27:59,593 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
> LIKE 'sql_mode'
> 2021-02-14 13:27:59,593 INFO sqlalchemy.engine.base.Engine ()
> 2021-02-14 13:27:59,595 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES
> LIKE 'lower_case_table_names'
> 2021-02-14 13:27:59,595 INFO sqlalchemy.engine.base.Engine ()
> 2021-02-14 13:27:59,598 INFO sqlalchemy.engine.base.Engine SELECT
> DATABASE()
> 2021-02-14 13:27:59,598 INFO sqlalchemy.engine.base.Engine ()
> 2021-02-14 13:27:59,599 INFO sqlalchemy.engine.base.Engine show collation
> where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
> 2021-02-14 13:27:59,599 INFO sqlalchemy.engine.base.Engine ()
> 2021-02-14 13:27:59,601 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test plain returns' AS CHAR(60)) AS anon_1
> 2021-02-14 13:27:59,601 INFO sqlalchemy.engine.base.Engine ()
> 2021-02-14 13:27:59,602 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test unicode returns' AS CHAR(60)) AS anon_1
> 2021-02-14 13:27:59,602 INFO sqlalchemy.engine.base.Engine ()
> 2021-02-14 13:27:59,602 INFO sqlalchemy.engine.base.Engine SELECT
> CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE
> utf8mb4_bin AS anon_1
> 2021-02-14 13:27:59,602 INFO sqlalchemy.engine.base.Engine ()
> 2021-02-14 13:27:59,603 INFO sqlalchemy.engine.base.Engine UPDATE
> magic_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 14 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 14 day), "%%Y-%%m-%%d"))) min ON
> max.user_id = min.user_id WHERE min.user_id = 43691 and max.user_id =
> 43691) progression ON progression.user_id = mu.user_id SET
> mu.followers_count_progress_14D = progression.progression WHERE
> progression.user_id is not null;
> 2021-02-14 13:27:59,603 INFO sqlalchemy.engine.base.Engine ()
> 2021-02-14 13:27:59,617 INFO sqlalchemy.engine.base.Engine COMMIT
> 0
>
> Any help or idea about how to debug this would be very much appreciated.
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/2c7c4629-36dc-47d5-b5e0-fedc5e5385e4n%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/2c7c4629-36dc-47d5-b5e0-fedc5e5385e4n%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/7242af11-3455-418b-b035-00be43036c6cn%40googlegroups.com.
