Hacktoberfest 2026：维护者为十月标记出来的 issue，仍然开放、适合新手。 浏览 Hacktoberfest issue

pandas.to_sql fails when writing more than 256 cells to a SQL Warehouse with method="multi"

未关闭
  • 哪里不对？
#24 3 条评论 3 个 reaction 已指派 0 人 在 GitHub 查看

还没有人认领这个 Issue。

评估

难度
4/5
预计耗时
3-5 天
新手友好度
25/100
Issue 类型
缺陷
描述清晰度
基本清楚
活跃度
停滞
技术栈
pandas, python, sql, sqlalchemy
领域
databases

调研方向

使用列出的版本复现 pandas.DataFrame.to_sql 与 SQLAlchemy method="multi" 的示例，然后跟踪 databricks-sqlalchemy 和 databricks-sql-connector 的参数处理。比较命名参数和内联参数的行为。当多行写入不再因 256 个参数的限制而失败，并且现有的逐行写入 workaround 不再需要时，即视为完成。

由索引模型根据 Issue 内容生成。

描述

This is a duplicate of https://github.com/databricks/databricks-sql-python/issues/300 . I am reposting it in this repo, as it still occurs with current versions of databricks-sqlalchemy~=1.0 (1.0.5) and databricks-sql-connector (4.0.2) together with the DBR 15.4 LTS ML versions of pandas (1.5.3) and sqlalchemy (1.4.39).

Problem

When using the multi-row insert mode of sqlalchemy, currently each value gets its own named parameter in the generated query:

INSERT INTO default.test_table (numeric_col, string_col) 
VALUES 
   (:numeric_col_m0, :string_col_m0), 
   (:numeric_col_m1, :string_col_m1), 
   (:numeric_col_m2, :string_col_m2), 
   (:numeric_col_m3, :string_col_m3),
   [...]
   (:numeric_col_m998, :string_col_m998), 
   (:numeric_col_m999, :string_col_m999)

with parameters:

{
'numeric_col_m0': 0, 
'string_col_m0': 'AAA', 
'numeric_col_m1': 1,
'string_col_m1': 'AAA', 
'numeric_col_m2',
[...], 
'numeric_col_m999': 999, 
'string_col_m999': 'AAA'
}

Consequently, even very small tables exceed the limit of 256 named parameters:

sqlalchemy.exc.OperationalError: (databricks.sql.exc.RequestError) Error during request to server: BAD_REQUEST: Parameterized query has too many parameters: 2000 parameters were given but the limit is 256.. BAD_REQUEST: Parameterized query has too many parameters: 2000 parameters were given but the limit is 256.

Example

import pandas as pd 
from sqlalchemy import create_engine

sqlalchemy_connection_string = f"databricks://token:{token}@{host}?http_path={http_path}?catalog={catalog}"
engine = create_engine(sqlalchemy_connection_string)
test_data = pd.DataFrame({"numeric_col": range(1_000), "string_col": ["AAA"] * 1_000})
test_data.to_sql("test_table", engine, if_exists="replace", index=False, method="multi")

Workarounds

Inline Parameters

I tried to avoid this issue by using legacy inline parameters. However, I get a different error then:

sqlalchemy_connection_string = f"databricks://token:{token}@{host}?http_path={http_path}?catalog={catalog}"
engine = create_engine(sqlalchemy_connection_string, connect_args={"use_inline_params": "silent"})
test_data = pd.DataFrame({"numeric_col": range(1_000), "string_col": ["AAA"] * 1_000})
test_data.to_sql("test_table", engine, if_exists="replace", index=False, method="multi")

DatabaseError: (databricks.sql.exc.ServerOperationError) [UNBOUND_SQL_PARAMETER] Found the unbound parameter: numeric_col_m0. Please, fix `args` and provide a mapping of the parameter to either a SQL literal or collection constructor functions such as `map()`, `array()`, `struct()`. SQLSTATE: 42P02; line 1 pos 57

Writing row-by-row

The only workaround at the moment seems to be to insert the data row-by-row by not setting method in the to_sql() method.
However, this is prohibitively slow even for medium-sized data frames:

Image

主要语言
Python
星标
24
派生
20
30 天内没有已合并 PR

环境准备

  • 没有 Dockerfile 或 Docker Compose 文件
  • 没有 Pull Request 模板
  • 阅读贡献指南

从这里开始

  1. 先读完整个 Issue，再读项目的贡献指南。
  2. 在 Issue 下留言说明你要接手 —— 这能避免两个人做同样的事。
  3. Fork 仓库，在一个分支上完成修改。
  4. 提交 Pull Request，并在描述里引用这个 Issue 编号。

databricks/databricks-sqlalchemy 的其他 Issue

查看 databricks/databricks-sqlalchemy 的全部 Issue

相似的 Issue

更多 Python Issue

把新 issue 发到你的邮箱

精选适合新手参与的 GitHub issue 摘要。