BUG: to_sql does not populate index column with a value when using the mssql+pyodbc engine

[hb2638]

BUG: to_sql does not populate index column with a value when using the mssql+pyodbc engine

[Siddharth-Latthe-07]

@hb2638 The root cause is that the index column is not correctly specified in the SQL table creation and data insertion process. The to_sql method needs to include the index in the INSERT statement and ensure it is populated with the correct values.
You can try by ensuring that the to_sql method includes the index column in the SQL table creation and data insertion process.

import math
import uuid
import pandas as pd
import sqlalchemy.engine

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")
df.index.name = 'index'  # Ensure the index has a name

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"not_set_{str(uuid.uuid4())}", conn, index=True)
        conn.rollback()

plz let me know, if the above handling works
Thanks