pandas 到SQL服务器 [英] pandas to sql server

问题描述

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

I got following code. The problem is I could read data use panda.read_sql, but I could not use the DataFrame.to_sql() function.

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

错误如下:

在SQL'SELECT name FROM sqlite_master WHERE上执行失败 type ='table'AND name = ?;':(('42S02'，"[42S02] [Microsoft] [ODBC SQL 服务器驱动程序] [SQL Server]无效的对象名称"sqlite_master". (208) (SQLExecDirectW); [42000] [Microsoft] [ODBC SQL Server驱动程序] [SQL 服务器]声明无法准备. (8180))

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

Consider creating a sqlalchemy MSSQL engine and use that in pandas to_sql() con argument:

import sqlalchemy

...
engine = sqlalchemy.create_engine(
               "mssql+pyodbc://user:pwd@server/database",
               echo=False)

data.to_sql('test', con=engine, if_exists='replace')

这篇关于 pandas 到SQL服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！