python - mysql.connector 不会在 python 中使用多标志执行插入
问题描述
我有一些复杂的 MySQL 插入，它设置一些变量，连接一些表，聚合值，然后尝试插入到表中。INSERT当我尝试使用 DBeaver 执行时，此 SQL命令有效。
当我尝试在没有插入部分的情况下运行此 SQL 命令时，具有相同选项的 mysql.connector 会正确处理它，返回我可以处理的结果集（但我想将结果写回表中）
当我在语句
INSERT INTO worklogs_aggregated SELECT Z.* FROM (...) Z周围添加（部分除外）时，库无法正确执行它，我真的不明白为什么。我没有收到任何错误消息。执行完成，没有任何错误，但表中没有任何内容。如果我复制相同的命令并尝试在 DBeaver 中执行它，它会完美运行。SELECTSET
我的简化 Python 代码与此类似：
import mysql.connector
import config as cfg
def rds_db_connect_for_multi_statement():
token = aws_generate_db_auth_token()
config = {
'host': cfg.db_config['hostname'],
'port': cfg.db_config['port'],
'user': cfg.db_config['username'],
'password': token,
'database': cfg.db_config['database'],
'ssl_ca': cfg.db_config['ca_path'],
'charset': cfg.db_config['charset'],
'raise_on_warnings': True
}
return mysql.connector.connect( **config )
if __name__== "__main__":
connection = None
cursor = None
try:
connection = rds_db_connect_for_multi_statement()
cursor = connection.cursor(buffered=True)
LONG_SQL_COMMAND=... # the SQL command from below
cursor.execute(LONG_SQL_COMMAND, multi=True)
connection.commit()
cursor.close()
connection.close()
except Exception as e:
if(cursor):
cursor.close()
elif(connection):
connection.rollback()
connection.close()
else:
raise(e)
这在没有以下情况下工作INSERT INTO：
SET
@row_number := 0,
@l_datum := '1900-01-01',
@l_member_id := 0,
@l_project_sm_id := 0;
SELECT
datum,
member_id,
project_sm_id,
ts_email,
project_sm_name,
project_ts_project_name,
assigment_assigment_id,
assigment_projekt_id,
assigment_hr_contact_id,
assigment_user_id,
duration_hour
FROM
(
SELECT
@row_number := CASE
WHEN @l_datum = datum
AND @l_member_id = member_id
AND @l_project_sm_id = project_sm_id
THEN @row_number + 1
ELSE 1
END AS project_order,
@l_datum := datum AS datum,
@l_member_id := member_id AS member_id,
@l_project_sm_id := project_sm_id AS project_sm_id,
ts_email AS ts_email,
project_sm_name AS project_sm_name,
project_ts_project_name AS project_ts_project_name,
assigment_assigment_id AS assigment_assigment_id,
assigment_projekt_id AS assigment_projekt_id,
assigment_hr_contact_id AS assigment_hr_contact_id,
assigment_user_id AS assigment_user_id,
duration_hour AS duration_hour
FROM
(
SELECT
wrk.work_dt AS datum,
wrk.member_id AS member_id,
wrk.project_id AS project_sm_id,
Coalesce(
Lower(emap.ts_email),
Lower(mem.email)
) AS ts_email,
prj.name AS project_sm_name,
map.ts_project_name AS project_ts_project_name,
asg.assignment_id AS assigment_assigment_id,
asg.projekt_id AS assigment_projekt_id,
asg.hr_contact_id AS assigment_hr_contact_id,
asg.user_id AS assigment_user_id,
Ceil(
Sum(duration_min) / 60
) AS duration_hour
FROM
sm2ts.worklogs AS wrk
JOIN sm2ts.ref_projects AS prj ON prj.id = wrk.project_id
JOIN sm2ts.project_map AS map ON map.sm_id = wrk.project_id
LEFT JOIN sm2ts.ref_members AS mem ON mem.id = wrk.member_id
LEFT JOIN sm2ts.email_map AS emap ON Lower(emap.sm_email) = Lower(mem.email)
JOIN sm2ts.sf_assignments AS asg ON Lower(asg.email) = Coalesce(
Lower(emap.ts_email),
Lower(mem.email)
)
AND Lower(asg.project_name) = Lower(map.ts_project_name)
WHERE
wrk.work_dt BETWEEN '2020-01-01'
AND '2020-02-06'
AND map.sm_org_id = '544411'
GROUP BY
wrk.member_id,
wrk.work_dt,
wrk.project_id,
map.sm_id,
Coalesce(
Lower(emap.ts_email),
Lower(mem.email)
),
prj.name,
map.sm_name,
map.ts_project_name,
asg.assignment_id,
asg.projekt_id,
asg.hr_contact_id,
asg.user_id
ORDER BY
wrk.member_id,
wrk.work_dt,
wrk.project_id,
map.sm_id,
Coalesce(
Lower(emap.ts_email),
Lower(mem.email)
),
prj.name,
map.sm_name,
map.ts_project_name,
asg.assignment_id,
asg.projekt_id,
asg.hr_contact_id,
asg.user_id
) X
) Y
WHERE
Y.project_order = 1
ORDER BY
datum,
member_id,
project_sm_id;
这不适用于INSERT INTO：
SET
@row_number := 0,
@l_datum := '1900-01-01',
@l_member_id := 0,
@l_project_sm_id := 0;
INSERT INTO worklogs_aggregated
SELECT Z.*
FROM
(
SELECT
datum,
member_id,
project_sm_id,
ts_email,
project_sm_name,
project_ts_project_name,
assigment_assigment_id,
assigment_projekt_id,
assigment_hr_contact_id,
assigment_user_id,
duration_hour
FROM
(
SELECT
@row_number := CASE
WHEN @l_datum = datum
AND @l_member_id = member_id
AND @l_project_sm_id = project_sm_id
THEN @row_number + 1
ELSE 1
END AS project_order,
@l_datum := datum AS datum,
@l_member_id := member_id AS member_id,
@l_project_sm_id := project_sm_id AS project_sm_id,
ts_email AS ts_email,
project_sm_name AS project_sm_name,
project_ts_project_name AS project_ts_project_name,
assigment_assigment_id AS assigment_assigment_id,
assigment_projekt_id AS assigment_projekt_id,
assigment_hr_contact_id AS assigment_hr_contact_id,
assigment_user_id AS assigment_user_id,
duration_hour AS duration_hour
FROM
(
SELECT
wrk.work_dt AS datum,
wrk.member_id AS member_id,
wrk.project_id AS project_sm_id,
COALESCE(
LOWER(emap.ts_email),
LOWER(mem.email)
) AS ts_email,
prj.name AS project_sm_name,
map.ts_project_name AS project_ts_project_name,
asg.ASSIGNMENT_ID AS assigment_assigment_id,
asg.PROJEKT_ID AS assigment_projekt_id,
asg.HR_CONTACT_ID AS assigment_hr_contact_id,
asg.USER_ID AS assigment_user_id,
CEIL(
SUM(duration_min) / 60
) AS duration_hour
FROM
sm2ts.worklogs AS wrk
JOIN sm2ts.ref_projects AS prj ON prj.id = wrk.project_id
JOIN sm2ts.project_map AS map ON map.sm_id = wrk.project_id
LEFT JOIN sm2ts.ref_members AS mem ON mem.id = wrk.member_id
LEFT JOIN sm2ts.email_map AS emap ON LOWER(emap.sm_email) = LOWER(mem.email)
JOIN sm2ts.sf_assignments AS asg ON LOWER(asg.email) = COALESCE(
LOWER(emap.ts_email),
LOWER(mem.email)
)
AND LOWER(asg.PROJECT_NAME) = LOWER(map.ts_project_name)
WHERE
wrk.work_dt BETWEEN '2020-01-01'
AND '2020-02-06'
AND map.sm_org_id = '544411'
GROUP BY
wrk.member_id,
wrk.work_dt,
wrk.project_id,
map.sm_id,
COALESCE(
LOWER(emap.ts_email),
LOWER(mem.email)
),
prj.name,
map.sm_name,
map.ts_project_name,
asg.ASSIGNMENT_ID,
asg.PROJEKT_ID,
asg.HR_CONTACT_ID,
asg.USER_ID
ORDER BY
wrk.member_id,
wrk.work_dt,
wrk.project_id,
map.sm_id,
COALESCE(
LOWER(emap.ts_email),
LOWER(mem.email)
),
prj.name,
map.sm_name,
map.ts_project_name,
asg.ASSIGNMENT_ID,
asg.PROJEKT_ID,
asg.HR_CONTACT_ID,
asg.USER_ID
) X
) Y
WHERE
Y.project_order = 1
ORDER BY
datum,
member_id,
project_sm_id
) Z;
- 从 Python 执行插入时导致问题的原因是什么？
- 为什么我在使用 DBeaver 执行相同的 SQL 命令时没有收到任何错误？
- 如果没有抛出异常，我怎么能得到错误消息？
解决方案
推荐阅读
- laravel - 在 Lumen 中更改环境文件路径
- java - 无状态注释是否会传播到抽象类的扩展？
- python - 努力从 python3 中的论文中用 DEAP 实现差分进化算法
- spring - Feign Client 中的 RequestParam 不支持 Spring Data Pageable
- vb.net - 序列化简单类时跳过的值
- tensorflow - 使用 keras 进行代码时，tensorflow-gpu 卡在 epoch1
- elasticsearch - 什么是弹性搜索 DSL 等效于 WHERE status = 'DONE' AND badgeId IN ('ID1', 'ID2', 'ID3')
- css - 桌面上不显示徽标，仅在平板电脑和移动设备上显示
- c++ - 为什么我的函数模板专业化被 VS2017 而不是 VS2015 拒绝？
- reactjs - 如何在 localforage 中存储图像（例如个人资料图片）？