TRUNCATE TABLE not working in Pyodbc

Question

import pyodbc

con=pyodbc.connect('Driver={SQL Server};Server=New;Database=Countrydatabase;Trusted_connection=yes')
cur=con.cursor()
cur.execute("TRUNCATE Countrydatabase..region")   
con.close()

I am trying to truncate a table using the pyodbc module. But it keeps throwing an error.

Traceback (most recent call last): File "C:/Users/sean/Desktop/script.py", line 6, in cur.execute("TRUNCATE Countrydatabase..region") pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Countrydatabase'. (102) (SQLExecDirectW)")

Same code works fine when I use "SELECT" statement.

Answer 1

My issue with Truncate not working appears to be the Auto Commit setting wasn't working after I explicitly called con.close():

con = pyodbc.connect("conn", autocommit=True)

Or you can explicitly perform commits before closing:

con.commit()

Ref: https://stackoverflow.com/a/31611279/845584

Answer 2

As the error messages shows, your SQL is incorrect.

It should be:

TRUNCATE TABLE region

(You don't need to specify the database, since you are connected to Countrydatabase already.)

Answer 3

PeterX's comment above was my solution. I set autocommit=True when instantiating the command and my tables started to be truncated.

Answer 4

Have you tried to prefix it with dbo. something like: dbo.tablename

I was playing around with it today and had the same issue and adding the dbo part worked :-)