Truncate table not working with SQL server sqlalchemy engine and pandas
I have the same problem with pandas 0.19.2 and sqlalchemy 1.1.5.
As I see autocommit is not forced in engine.execute() when running a TRUNCATE statement. If I force it manually then TRUNCATE works perfectly:
from sqlalchemy.sql import text as sa_textengine.execute(sa_text('''TRUNCATE TABLE tempy''').execution_options(autocommit=True))It's fancy that DROP works perfectly without forcing autocommit...
This worked for me:
from sqlalchemy.orm import sessionmakerSession = sessionmaker(bind=engine)session = Session()session.execute('''TRUNCATE TABLE tempy''')session.commit()session.close()
Here is a full solution based from the question, using sqlalchemy 1.1.15 on Windows I was receiving errors trying to implement the other solutions:
import sqlalchemyengine = sqlalchemy.create_engine('mssql://myserver/mydb?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes')connection = engine.connect()truncate_query = sqlalchemy.text("TRUNCATE TABLE tempy")connection.execution_options(autocommit=True).execute(truncate_query)