截断表不适用于 SQL Server sqlalchemy 引擎和 Pandas
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42079419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Truncate table not working with SQL server sqlalchemy engine and pandas
提问by scottlittle
I can successfully query and insert data using sqlalchemy and pandas:
我可以使用 sqlalchemy 和 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
Tryto 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
采纳答案by scottlittle
This worked for me:
这对我有用：
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
session.execute('''TRUNCATE TABLE tempy''')
session.commit()
session.close()
回答by ragesz
I have the same problem with pandas0.19.2 and sqlalchemy 1.1.5.
我对pandas0.19.2 和 sqlalchemy有同样的问题1.1.5。
As I see autocommitis not forced in engine.execute()when running a TRUNCATEstatement. If I force it manually then TRUNCATEworks perfectly:
正如我所看到的autocommit，engine.execute()在运行TRUNCATE语句时不会强制进入。如果我手动强制它然后TRUNCATE完美地工作：
from sqlalchemy.sql import text as sa_text
engine.execute(sa_text('''TRUNCATE TABLE tempy''').execution_options(autocommit=True))
It's fancy that DROPworks perfectly without forcing autocommit...
DROP无需强迫即可完美运行，这真是太棒了autocommit……
回答by Jason Ridenour
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:
这是基于该问题的完整解决方案，在 Windows 上使用 sqlalchemy 1.1.15 我在尝试实施其他解决方案时收到错误：
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)
回答by Cody Mayers
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:
在尝试了 ragesz 发布的解决方案但它对我不起作用（我的 sqlalchemy 版本是1.3.9）后，我得到了以下工作：
with engine.connect() as con:
con.execution_options(autocommit=True).execute("TRUNCATE TABLE foo;")
