BUG: to_sql does gives incorrect column name for index when callable passed in to method

[hb2638]

BUG: to_sql does gives incorrect column name for index when callable passed in to method

[Siddharth-Latthe-07]

@hb2638 The Observed Behavior from your code,
The output from SQLAlchemy shows that the SQL table is created with a column named index, but the insert_callback function receives ('index', '') as the index column name in the list of columns. This discrepancy causes issues when trying to insert data into the table.
Expected output:-
The insert_callback function should receive the correct column name for the index column, which should be index, not ('index', '').
To correct this behavior, you can preprocess the keys list to replace ('index', '') with index before constructing the SQL insert statement.
Try out this code and let me know, if it works,

import math
import typing
import uuid

import pandas as pd
import sqlalchemy
import sqlalchemy.engine

def insert_callback(table: pd.io.sql.SQLTable, conn: sqlalchemy.engine.base.Connection, keys: list[str], data_iter: typing.Iterable[tuple]) -> None:
    data = [list(d) for d in data_iter]
    print(f"{data=}")
    print(f"{keys=}")
    print(f"{[c.name for c in table.table.columns]=}")
    
    # Preprocess keys to replace ('index', '') with index
    processed_keys = ["index" if k == "('index', '')" else k for k in keys]
    
    sql = f"""
INSERT INTO [{table.name}]({", ".join(f"[{k}]" for k in processed_keys)})
VALUES ({", ".join(f":{i}" for i, _ in enumerate(processed_keys))})
""".strip()
    
    for values in data:
        conn.execute(sqlalchemy.text(sql), {str(i): v for i, v in enumerate(values)})

input_data = {
    1: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
    2: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
    3: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
    4: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
    5: {('A', 'X'): math.nan, ('A', 'Y'): math.nan, ('B', 'X'): math.nan, ('B', 'Y'): math.nan},
}

df = pd.DataFrame.from_dict(input_data, "index")
conn_url = sqlalchemy.engine.URL.create("mssql+pyodbc", query={"odbc_connect": "DRIVER={ODBC Driver 18 for SQL Server};SERVER=.;Trusted_Connection=yes;TrustServerCertificate=yes"})
engine = sqlalchemy.create_engine(conn_url, echo=True)

with engine.connect() as conn:
    with conn.begin():
        df.to_sql(f"insert_callback_{str(uuid.uuid4())}", conn, index=True, method=insert_callback)
        conn.rollback()