我可以使用 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')
读取临时表:
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()
再次读取表,但截断失败:
df = pd.read_sql(sql_command, engine)
print df
tempID tempValue
0 1 2
1 4 6
原文由 scottlittle 发布,翻译遵循 CC BY-SA 4.0 许可协议
这对我有用: