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

[paulstaab]

This is a duplicate of databricks/databricks-sql-python#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:

[luppidd]

Still observing this issue in the most recent version of the package when used with pandas. Is this being looked at or would it be worthwhile to try to hack a solution together for my specfic use case?

[diskun00]

One solution is to specify parameter chunksize. Then you won't don't reach the limation.

[beefupinetree]

I had to write my own to_sql() function to solve this issue. I was inspired by the reply I received on my own open issue databricks/databricks-sql-python#450 which recommended that I write my own INSERT queries. So I did. I've been using it for a few months now and it's working well. I packaged and released it on PyPi here for everyone else to use. I included all the background information on this issue, and how this solution works in the GitHub repo here as well.

The gist of it is that this new to_sql_dbx() function compiles the SQL query before it's sent to the Databricks SQL warehouse. Which replaces the parameters (:value1, :value2) with their actual values in the query. This bypasses the 255 parameter limit set on the server side.

You can try it out yourself by installing the package:
python -m pip install pandas-tosql-dbx-fix

And using the following code:

import pandas_tosql_dbx_fix as pdx

# Use your own values for the following variables
server = "YOUR_DATABRICKS_SERVER_HOSTNAME"
hpath = "YOUR_DATABRICKS_HTTP_PATH"
catalog = "YOUR_CATALOG_NAME"
schema = "YOUR_SCHEMA_NAME"
# token = "YOUR_DATABRICKS_TOKEN" # if needed

table_name = "to_sql_table"
test_table_rows = 1000

df = pdx.create_test_dataframe(test_table_rows)

# You can also connect to Databricks using a token with the pdx.connect_to_dbx_pat() function,
# or by creating your own SQLAlchemy engine.
db_con = pdx.connect_to_dbx_oauth(
        server, hpath, catalog, schema
    )

# The function takes the same arguments as the to_sql() method in Pandas
pdx.to_sql_dbx(
            df,
            db_con,
            f"{catalog}.{schema}.{table_name}",
            if_exists="append",
        )