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)
the error is the following:
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)")
Is there a way to get around?
1 Answer
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')
