CODEWITHSUNDEEP
pythonsqlsql-serverpandassqlalchemy
Adi E
Adi E

Reputation: 488

How can I truncate a table using pandas?

I have a function that is executed few times, each time it appends elements to a table on SQL Server using this code:

import pandas as pd
import pandas.io.sql as pdsql
import pyodbc

params = [(self.key[int(el[0])], bid, label, tr_date, el[1]) for el in elements]
df = pd.DataFrame(params, columns=['ID', 'BID', 'Label', 'tr_date', 'Score'])
engine = sqlalchemy.create_engine('mssql+pyodbc://MY-SERVER/Test')
df.to_sql(out_tbl, engine, if_exists='append', index=False)

However, before entering the above code I want to truncate (or even drop) the table. I didn't find any dedicated function in pandas.io.sql. So I tried to create an empty data frame just to call:

df1 = pd.DataFrame()
df1.to_sql(out_tbl, engine, if_exists='replace', index=False)

This code does drop the table but then generates exception from sqlalchemy as it tries to re-create an empty table. I can catch and ignore it, and the next call to_sql() with if_exists='append' will create the table correctly, but this is pretty ugly.

Another way I tried is to clear all the rows from the original data frame, leaving the columns:

df1 = df.drop(df.index)
df1.to_sql(out_tbl, engine, if_exists='replace', index=False)

This almost works: it truncates the table but then insert a single record with all fields as NULL...

Currently my workaround is to keep another pyodbc connection just for this task:

sql = "DELETE FROM " + out_tbl
try:
    cursor.execute(sql)
except:                     # mainly if table doesn't exist
    pass

So is there a simple way to achieve this simple task with pandas?

thanks Adi

Upvotes: 5

Views: 15946

Answers (1)

Rizwan Ahmad
Rizwan Ahmad

Reputation: 91

Try this...I got the same problem in my project so just connect to db and execute the truncate command

params = [(self.key[int(el[0])], bid, label, tr_date, el[1]) for el in elements]
df = pd.DataFrame(params, columns=['ID', 'BID', 'Label', 'tr_date', 'Score'])
engine = sqlalchemy.create_engine('mssql+pyodbc://MY-SERVER/Test')
conn = engine.connect()
conn.execute("TRUNCATE TABLE out_tbl")
df.to_sql(out_tbl, engine, if_exists='append', index=False)

Upvotes: 9

Related Questions