pandas to sql server

Question

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?

Does test table exist in database in dbo schema? If not use the if_exists argument to replace/append. The default is fail. — Parfait
– Parfait, Commented Apr 8, 2016 at 0:27
@Parfait I used replace option, and it is giving the same error. — JOHN
– JOHN, Commented Apr 8, 2016 at 16:52
pandas to_sql does not support MS SQL Server connection directly, you need to use sqlalchemy to connect as shown in the answer of @Parfait — joris
– joris, Commented Apr 10, 2016 at 16:41

Parfait · Accepted Answer · 2016-04-10 15:38:23Z

20

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

answered Apr 10, 2016 at 15:38

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TPPZ Over a year ago

If there's more than one schema per DB, the table is identified by [MySchemaName].[MyTableName]. The schema can be passed as a parameter to to_sql like: my_dataframe.to_sql(name='MyTableName', con=engine, if_exists='replace', schema='MySchemaName'), if I was not passing the schema then it didn't work for me. Also the connection string that worked for me has this format:

"mssql+pyodbc:///?odbc_connect={}".format(urllib.parse.quote_plus("DRIVER=ODBC Driver 13 for SQL Server;SERVER={0};PORT=1433;DATABASE={1};UID={2};PWD={3};TDS_Version=8.0;".format(server, database, user, password)))

Parfait Over a year ago

@TPPZ ... thank you for your note which future readers will appreciate.

user1330974 Over a year ago

@TPPZ I spent almost an hour trying to resolve issues writing dataframe to MSSQL with pandas's to_sql via sqlalchemy, and your comment is the ONLY ONE that worked with all the info that I needed. Thanks very much for finally saving my evening!

Collectives™ on Stack Overflow

pandas to sql server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related