截断表不适用于SQL Server SQLAlchemy引擎和 pandas [英] Truncate table not working with SQL server sqlalchemy engine and pandas

问题描述

我可以使用sqlalchemy和pandas成功查询和插入数据

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')

读取tempy表:

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

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

   tempID  tempValue
0       1          2

附加新数据:

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

尝试截断数据:

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

推荐答案

这对我有用:

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

这篇关于截断表不适用于SQL Server SQLAlchemy引擎和 pandas 的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！