When trying to insert a small amount of data into a table in mssql, through a dataframe, the to_sql() method shows the number of lines inserted but there is no insertion in the database. I know there are other questions similar to this one, but unfortunately none solve my problem.
This is my code:
connection
engine = sa.create_engine('mssql://server/db_name?trusted_connection=yes&driver=ODBC+Driver+17+for+SQL+Server')
code
conn = engine.connect()
conn.exec_driver_sql("SET IDENTITY_INSERT [dbo].[my_table] ON")
data_frame.to_sql('[dbo].[my_table]',conn , schema='dbo', if_exists='append', index=False)
I am using a local server and windows authentication in this project.
This
data_frame.to_sql('[dbo].[my_table]',conn , schema='dbo', if_exists='append', index=False)
Should be
df.to_sql('my_table',conn , schema='dbo', if_exists='append', index=False)
Otherwise it creates and new table in your default schema called "[dbo].[my_table]", and loads it like this:
declare @p1 int
--set @p1=4
exec sp_prepexec @p1 output,N'@P1 int',N'INSERT INTO dbo.[[dbo]].[my_table]]] (id) VALUES (@P1)',3
select @p1
which creates and loads a new table in your default schema called "[dbo].[my_table]".