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

[hb2638]

Pandas version checks

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import math
import typing
import uuid

import pandas
import sqlalchemy
import sqlalchemy.engine


def insert_callback(table: pandas.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]=}")
    sql = f"""
INSERT INTO [{table.name}]({", ".join(f"[{k}]" for k in keys)})
VALUES ({", ".join(f":{i}" for i, _ in enumerate(keys))})
""".strip()
    for values in data:
        conn.execute(sqlalchemy.text(sql), {str(i): v for i, v in enumerate(values)})

input = {
    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 = pandas.DataFrame.from_dict(input, "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()

Issue Description

When calling pandas.DataFrame.to_sql with index set to True, it gives the the wrong name for the SQL column used for the index.

E.x.: In the code I provided, it creates a column called index as the index but it passed ('index', '') as the index column name in the list of columns.

Below is the output from sql alchemy

2024-06-26 16:02:23,136 INFO sqlalchemy.engine.Engine SELECT CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR)
2024-06-26 16:02:23,136 INFO sqlalchemy.engine.Engine [raw sql] ()
2024-06-26 16:02:23,137 INFO sqlalchemy.engine.Engine SELECT schema_name()
2024-06-26 16:02:23,137 INFO sqlalchemy.engine.Engine [generated in 0.00014s] ()
2024-06-26 16:02:23,138 INFO sqlalchemy.engine.Engine SELECT CAST('test max support' AS NVARCHAR(max))
2024-06-26 16:02:23,138 INFO sqlalchemy.engine.Engine [generated in 0.00013s] ()
2024-06-26 16:02:23,138 INFO sqlalchemy.engine.Engine SELECT 1 FROM fn_listextendedproperty(default, default, default, default, default, default, default)
2024-06-26 16:02:23,138 INFO sqlalchemy.engine.Engine [generated in 0.00011s] ()
2024-06-26 16:02:23,139 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2024-06-26 16:02:23,151 INFO sqlalchemy.engine.Engine SELECT [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] 
FROM [INFORMATION_SCHEMA].[TABLES] 
WHERE ([INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max)) OR [INFORMATION_SCHEMA].[TABLES].[TABLE_TYPE] = CAST(? AS NVARCHAR(max))) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_NAME] = CAST(? AS NVARCHAR(max)) AND [INFORMATION_SCHEMA].[TABLES].[TABLE_SCHEMA] = CAST(? AS NVARCHAR(max))
2024-06-26 16:02:23,151 INFO sqlalchemy.engine.Engine [generated in 0.00032s] ('BASE TABLE', 'VIEW', 'insert_callback_5ff921a3-15b6-474b-af16-db1d7e243f06', 'dbo')
2024-06-26 16:02:23,153 INFO sqlalchemy.engine.Engine 
CREATE TABLE [insert_callback_5ff921a3-15b6-474b-af16-db1d7e243f06] (
	[index] BIGINT NULL, 
	[('A', 'X')] FLOAT(53) NULL, 
	[('A', 'Y')] FLOAT(53) NULL, 
	[('B', 'X')] FLOAT(53) NULL, 
	[('B', 'Y')] FLOAT(53) NULL
)


2024-06-26 16:02:23,153 INFO sqlalchemy.engine.Engine [no key 0.00014s] ()
2024-06-26 16:02:23,155 INFO sqlalchemy.engine.Engine CREATE INDEX [ix_insert_callback_5ff921a3-15b6-474b-af16-db1d7e243f06_index] ON [insert_callback_5ff921a3-15b6-474b-af16-db1d7e243f06] ([index])
2024-06-26 16:02:23,155 INFO sqlalchemy.engine.Engine [no key 0.00012s] ()
data=[[1, None, None, None, None], [2, None, None, None, None], [3, None, None, None, None], [4, None, None, None, None], [5, None, None, None, None]]
keys=["('index', '')", "('A', 'X')", "('A', 'Y')", "('B', 'X')", "('B', 'Y')"]
[c.name for c in table.table.columns]=['index', "('A', 'X')", "('A', 'Y')", "('B', 'X')", "('B', 'Y')"]
2024-06-26 16:02:23,158 INFO sqlalchemy.engine.Engine INSERT INTO [insert_callback_5ff921a3-15b6-474b-af16-db1d7e243f06]([('index', '')], [('A', 'X')], [('A', 'Y')], [('B', 'X')], [('B', 'Y')])
VALUES (?, ?, ?, ?, ?)
2024-06-26 16:02:23,158 INFO sqlalchemy.engine.Engine [generated in 0.00016s] (1, None, None, None, None)
2024-06-26 16:02:23,160 INFO sqlalchemy.engine.Engine ROLLBACK

Expected Behavior

The sql column name of the index should be in the list of keys/columns provided to the call back.

Installed Versions

import pandas as pd
pd.show_versions()
INSTALLED VERSIONS

commit : d9cdd2e
python : 3.12.1.final.0
python-bits : 64
OS : Windows
OS-release : 10
Version : 10.0.19044
machine : AMD64
processor : Intel64 Family 6 Model 165 Stepping 2, GenuineIntel
byteorder : little
LC_ALL : None
LANG : None
LOCALE : English_United States.1252
pandas : 2.2.2
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0.post0
setuptools : 70.1.0
pip : 24.1
Cython : None
pytest : None
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : 3.2.0
lxml.etree : 5.2.2
html5lib : 1.1
pymysql : None
psycopg2 : None
jinja2 : 3.1.4
IPython : None
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
bottleneck : None
dataframe-api-compat : None
fastparquet : 2024.5.0
fsspec : 2024.6.0
gcsfs : None
matplotlib : 3.8.4
numba : None
numexpr : None
odfpy : None
openpyxl : 3.1.3
pandas_gbq : None
pyarrow : 16.1.0
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.11.4
sqlalchemy : 2.0.30
tables : None
tabulate : None
xarray : None
xlrd : None
zstandard : None
tzdata : 2024.1
qtpy : None
pyqt5 : None

[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()