Truncate table not working with SQL server sqlalchemy engine and pandas

Question

I can successfully query and insert data using sqlalchemy and pandas:

from sqlalchemy import create_engine
import pandas as pd
engine = create_engine('mssql://myserver/mydb?driver=SQL+Server+Native+Client+11.0?trusted_connection=yes')

Read tempy table:

sql_command = """
select top 100 * from tempy
"""

df = pd.read_sql(sql_command, engine)
print df

   tempID  tempValue
0       1          2

Append new data:

df_append = pd.DataFrame( [[4,6]] , columns=['tempID','tempValue']) 
df_append.to_sql(name='tempy', con=engine, if_exists = 'append', index=False)

df = pd.read_sql(sql_command, engine)
print df

   tempID  tempValue
0       1          2
1       4          6

Try to truncate data:

connection = engine.connect()
connection.execute( '''TRUNCATE TABLE tempy''' )
connection.close()

Read table again, but truncate failed:

df = pd.read_sql(sql_command, engine)
print df

   tempID  tempValue
0       1          2
1       4          6

Solution 1

This worked for me:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
session.execute('''TRUNCATE TABLE tempy''')
session.commit()
session.close()

Solution 2

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_text

engine.execute(sa_text('''TRUNCATE TABLE tempy''').execution_options(autocommit=True))

It's fancy that DROP works perfectly without forcing autocommit...

Solution 3

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 sqlalchemy
engine = 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)

Solution 4

After trying the solution posted by ragesz and it not working for me (my sqlalchemy version is 1.3.9), I got the following to work instead:

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

View more solutions