MSSQLDatabaseException: (544, b"Cannot insert explicit value for identity column in table &#039-CodePudding

I am using the Jupyter Notebook and writing the code in Python. I was trying to access a database table and store the content in a dataframe as:

engine_query="mssql pymssql://" username ":" password "@" server "/" database
sql_engine = sqlalchemy.create_engine(engine_query).connect()
query = "SELECT * FROM RM_ST_FFA_Control_table"
mail_details = pd.read_sql(query, sql_engine)

Here, RM_ST_FFA_Control_table has 5 columns as 'P_Key','SR_NO','Details','Value','Last_Update_Date'(It is in datatime format) and the values stored as:

| P_Key | SR_NO | Details | Value | Last_Update_Date
--------------------------------------------------- 
| 1 | 1 | server | 10.129.29.153 | 2021-11-18
| 2 | 2 | user | snt.marketdata | 2021-11-18

By mistake, I have truncated this table as:

with sql_engine.connect() as con:con.execution_options(autocommit=True).execute("TRUNCATE TABLE RM_ST_FFA_Control_table;")

I want to restore the content of this table again, so I was thinking to make a new dataframe and transfer the data to this table as:

data = [[1, 1,'server','10.129.29.153','2021-11-18'],
         [2, 2,'user','snt.marketdata','2021-11-18'],
          ]
df = pd.DataFrame(data, columns = ['P_Key','SR_NO','Details','Value','Last_Update_Date'])
df['Last_Update_Date'] =  pd.to_datetime(df['Last_Update_Date'], format='%Y-%m-%d')
df.to_sql(con=sql_engine, name='RM_ST_FFA_Control_table', if_exists='append', index=False)

But I am getting the error message as:

MSSQLDatabaseException: (544, b"Cannot insert explicit value for identity column in table 'RM_ST_FFA_Control_table' when IDENTITY_INSERT is set to OFF.DB-Lib error message 20018, severity 16:\nGeneral SQL Server error: Check messages from the SQL Server\n")

Can anyone please help me to resolve this issue by IDENTITY_INSERT set to on. I don't have access to SQL Server and I am using SqlAlchemy. Is it possible to fix this issue without deleting this table and creating another table with the same name?

Any help would be appreciated.

CodePudding user response：

You can simply send SET IDENTITY_INSERT RM_ST_FFA_Control_table ON before calling .to_sql():

import pandas as pd
import sqlalchemy as sa

connection_url = (
    "mssql pyodbc://scott:tiger^[email protected]/test"
    "?driver=ODBC Driver 17 for SQL Server"
)
engine = sa.create_engine(
    connection_url,
    fast_executemany=True,
)

table_name = "RM_ST_FFA_Control_table"

with engine.begin() as conn:
    # create empty table for testing
    conn.exec_driver_sql(f"DROP TABLE IF EXISTS {table_name}")
    conn.exec_driver_sql(
        f"CREATE TABLE {table_name} (id int identity primary key, x nvarchar(50))"
    )

    df = pd.DataFrame([(1, "foo"), (3, "baz")], columns=["id", "x"])

    # enable inserting values into IDENTITY column
    conn.exec_driver_sql(f"SET IDENTITY_INSERT {table_name} ON")

    df.to_sql(table_name, conn, if_exists="append", index=False)

    # check results
    print(conn.exec_driver_sql(f"SELECT * FROM {table_name}").fetchall())
    # [(1, 'foo'), (3, 'baz')]