MSSQLDatabaseException: (544, b"Cannot insert explicit value for identity column in table 'RM_ST_FFA_Control_table' when IDENTITY_INSERT is set to OFF

user_10537579 0
Viewed 781

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.

jupyter-notebook sqlalchemy python-3.x sql-server database
1 Answers

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^5HHH@192.168.0.199/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')]
user_21443900
Related