python之Pandas 到 sql server

我得到了以下代码。问题是我可以使用 panda.read_sql 读取数据，但我无法使用 DataFrame.to_sql() 函数。

%matplotlib inline 
import pandas as pd 
import pyodbc 
from datetime import datetime 
import numpy as np 
import matplotlib.pyplot as plt 
 
pd.set_option('display.max_rows', 50) 
pd.set_option('display.max_columns', 15) 
pd.set_option('precision', 4) 
 
conn = pyodbc.connect(r"Driver={SQL Server};Server=dev;Database=test1") 
 
data = pd.read_sql_query( 
    """ 
SELECT * 
FROM   sys.tables 
    """ 
    , con = conn) 
 
print data 
data.to_sql('test', con = conn) 

错误如下:

Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW); [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

有办法绕过吗？

请您参考如下方法:

考虑创建一个 sqlalchemy MSSQL engine并在 pandas to_sql() con 参数中使用它:

import sqlalchemy 
 
... 
engine = sqlalchemy.create_engine( 
               "mssql+pyodbc://user:pwd@server/database", 
               echo=False) 
 
data.to_sql('test', con=engine, if_exists='replace')