Python操作MariaDB执行跨库INSERT SELECT语句报语法错误咨询

• 连接上下文不支持跨库访问：大部分Python数据库驱动建立连接时默认绑定单个数据库，若连接账号无跨库访问权限、或数据库本身未开启跨库访问能力，直接写库名.表名的跨库限定语法会被判定为语法错误。
• 标识符未正确转义：当库名、表名和数据库保留字重名时，如果没有用对应数据库的转义符包裹标识符（MySQL/MariaDB用反引号、PostgreSQL用双引号、SQL Server用方括号），SQL解析阶段就会抛出语法错误。
• 数据库本身不支持单条SQL跨库：比如PostgreSQL、默认配置的SQLite都不支持直接在单条SQL中通过库名.表名的形式访问其他库的表，这种写法本身不符合对应数据库的语法规范。
• SQL字符串拼接错误：代码中动态拼接SQL时如果漏写空格、多了非法转义字符，比如把insert into db1.t1 select拼成insert into db1.t1select，也会触发语法错误。

• 先在数据库原生客户端中验证SQL合法性：把你写的跨库SQL直接复制到对应数据库的命令行/可视化客户端中执行，如果客户端层面就报语法错，先修正SQL本身的语法问题，再放到Python代码中调用。
• 根据你使用的数据库类型适配跨库逻辑：
  • MySQL/MariaDB：本身原生支持跨库访问，首先确认连接使用的账号对源库有读权限、对目标库有写权限；所有库名、表名如果和保留字冲突，统一用反引号包裹，正确写法参考：

    INSERT INTO `target_database`.`target_table`
    SELECT * FROM `source_database`.`source_table`;
    

    Python中调用时不需要在连接参数里强制指定单个数据库，示例：

    import pymysql
    # 连接时不指定具体db，只要账号权限足够即可
    conn = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="your_migrate_account",
        password="your_password"
    )
    cur = conn.cursor()
    cur.execute("""
        INSERT INTO `target_database`.`target_table`
        SELECT * FROM `source_database`.`source_table`
    """)
    conn.commit()
    cur.close()
    conn.close()
    

  • SQLite：不支持直接跨文件访问表，需要先在当前连接上挂载源数据库，再执行迁移：

    import sqlite3
    conn = sqlite3.connect("target_database.db")
    cur = conn.cursor()
    # 挂载源数据库文件，设置别名
    cur.execute("ATTACH DATABASE 'source_database.db' AS source_db")
    cur.execute("""
        INSERT INTO main.target_table
        SELECT * FROM source_db.source_table
    """)
    conn.commit()
    

  • PostgreSQL：不支持直接写库名跨库查询，需要先部署postgres_fdw扩展，将源库的表映射为当前库的外部表后，再执行插入操作，不能直接使用anotherdatabase.table的写法。
• 大表迁移不建议直接用单条insert into ... select *的写法：单条SQL会触发长事务、锁表，甚至因为数据量过大导致内存溢出，建议分批拉取源表数据，用executemany()批量写入目标表，单批大小控制在1000-5000条即可。
• 禁止用字符串格式化的方式拼接SQL参数，避免语法错误和SQL注入风险，如果需要指定迁移的库、表名，先做白名单校验后再拼接标识符部分。

内容的提问来源于stack exchange，提问作者Vladimir Reyes La O