Siddharth-Latthe-07
commented
4 months ago @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()
Pandas version checks
[X] I have checked that this issue has not already been reported.
[X] I have confirmed this bug exists on the latest version of pandas.
[X] I have confirmed this bug exists on the main branch of pandas.
Reproducible Example
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
Expected Behavior
The sql column name of the index should be in the list of keys/columns provided to the call back.
Installed Versions