跨数据库插入非存在记录报错：MariaDB语法错误排查与方案求助

我来帮你拆解下代码里的问题，以及给出更高效、安全的实现方案：

你的代码存在的核心问题 #

1. SQL参数拼接引发的语法错误（和安全风险） #

你手动用'%s'拼接SQL字符串，当字段值本身包含单引号（比如你报错里的'in'）时，会把SQL语句的引号结构搞乱，直接触发语法错误。而且这种写法存在严重的SQL注入风险，绝对不能在生产环境用。

2. 低效的内存级数据对比 #

你把远程表和本地表的所有数据都拉到Python内存里做列表比对，数据量小的时候还能凑合用，一旦数据上万条，内存占用会飙升，运行速度也会慢到离谱。数据库本身就是为这类数据比对和筛选设计的，把逻辑交给数据库才是正确的思路。

3. 冗余的代码逻辑 #

你把查询结果转成列表的循环完全是多余的，lst = cHandler.fetchall()直接就能拿到结果列表，不需要再循环append。

修正后的可行方案 #

方案1：修复参数传递（解决语法错误） #

Frappe的db.sql支持安全的参数绑定，不需要手动加单引号，直接传参数就行，既能解决引号问题，又能避免注入：

# 拉取远程数据库数据
cHandler = myDB.cursor()
cHandler.execute('select UserId,C1,LogDate from DeviceLogs_12_2019')
remote_records = cHandler.fetchall()

# 逐条插入（安全参数绑定）
for record in remote_records:
    user_id, c1_val, log_date = record
    frappe.db.sql("""
        INSERT INTO biometric(UserId, C1, LogDate)
        SELECT %(user_id)s, %(c1)s, %(log_date)s
        WHERE NOT EXISTS (
            SELECT 1 FROM biometric 
            WHERE UserID = %(user_id)s AND LogDate = %(log_date)s
        )
    """, {
        "user_id": user_id,
        "c1": c1_val,
        "log_date": log_date
    })

方案2：更高效的批量/跨库处理（强烈推荐） #

如果你的远程SQL Server和本地MariaDB能直接建立连接（比如用SQL Server的链接服务器功能），直接写一条SQL就能完成同步，完全不需要Python中转，速度快到飞起：

-- 假设远程数据库通过链接服务器名为RemoteDB访问
INSERT INTO biometric(UserId, C1, LogDate)
SELECT UserId, C1, LogDate
FROM RemoteDB.dbo.DeviceLogs_12_2019
WHERE NOT EXISTS (
    SELECT 1 FROM biometric
    WHERE biometric.UserID = RemoteDB.dbo.DeviceLogs_12_2019.UserId
      AND biometric.LogDate = RemoteDB.dbo.DeviceLogs_12_2019.LogDate
)

如果没法直接跨库，也可以在Python里批量处理，减少和数据库的交互次数：

cHandler = myDB.cursor()
cHandler.execute('select UserId,C1,LogDate from DeviceLogs_12_2019')
remote_records = cHandler.fetchall()

# 准备批量插入的SQL模板
insert_sql = """
    INSERT INTO biometric(UserId, C1, LogDate)
    SELECT %s, %s, %s
    WHERE NOT EXISTS (
        SELECT 1 FROM biometric 
        WHERE UserID = %s AND LogDate = %s
    )
"""

# 构造参数列表：每个记录对应(UserId,C1,LogDate,UserId,LogDate)
params = [(r[0], r[1], r[2], r[0], r[2]) for r in remote_records]

# 批量执行，大幅减少数据库调用次数
frappe.db.cursor().executemany(insert_sql, params)
frappe.db.commit()

额外优化建议 #

• 给biometric表的UserId和LogDate加个联合索引，这样NOT EXISTS的查询速度会提升N倍，尤其是数据量大的时候：

  CREATE INDEX idx_biometric_user_logdate ON biometric(UserId, LogDate);
  

• 别每次都拉取远程表的全部数据！可以加个过滤条件，只同步本地没有的新数据，比如：

  # 先查本地最新的日志日期
  latest_log_date = frappe.db.sql("SELECT MAX(LogDate) FROM biometric")[0][0] or '1900-01-01'
  # 只拉取远程表中比本地最新日期晚的数据
  cHandler.execute('select UserId,C1,LogDate from DeviceLogs_12_2019 WHERE LogDate > %s', (latest_log_date,))
  

内容的提问来源于stack exchange，提问作者Aditi