我有一个在SQL编辑器中运行良好的查询:
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;
我尝试使用execute函数从SQLAlchemy执行相同的查询:
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)
不会返回任何异常,并且打印(rs)会按预期生成返回代理。 但是,在使用SQL编辑器更新数据库时,它不会使用SQLAlchemy进行更新。 我的查询中是否有SQL Alchemy不支持的部分
我最初认为这将是日期格式中%的转义,但不同的测试表明,使用这种转义编写可以像预期的那样运行更简单的查询
编辑:按照上面的建议在引擎创建中使用echo=True后,我可以看到查询格式已保留,提交已完成。我将echo的输出复制粘贴到一个sql编辑器中,查询运行良好,但是使用sqlalchemy它根本不会更新
EDIT2:尝试添加autocommit=True,结果相同。。。。 日志显示:
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
用于连接的用户具有所有权限:
GRANT ALL ON *.* TO 'user1'@'%';
在SQLAlchemy上运行的更简单的更新查询实际上正在工作
编辑3: 有趣的是,这似乎只发生在某些ID上,而不是所有ID上。依赖ID的东西如何远程工作而不是本地工作
由于调试打印似乎没有提供足够的信息来解决这个问题,我将假设这确实是一个向DB提交更改的问题,因此,就像其他人在各种其他问题(例如:setting autocommit to 1 in mysql)中提到的那样,您应该尝试显式地使用
autocommit=True
您可以使用
with
语句来测试这一点,例如:或者只是将
.execution_options(autocommit=True)
附加到现有代码中:请注意,
execution_option
的autocommit
参数将在SQLAlchemy 1.4中被弃用,替换将设置事务隔离级别,如here所示只是重申一下,MySQL似乎在内部将autocommit值设置为0,这意味着它使用一个需要
.commit()
处理的事务将其传播到DB。希望这确实解决了这个问题,因为我目前还没有在我的机器上测试这个相关问题 更多 >
编程相关推荐