Error while using the to_sql function [duplicate]

Question

I am using the following code to extract data from SQL into a Data Frame and it works fine.

import pyodbc 
cnxn = pyodbc.connect("Driver={SQL Server Native Client 11.0};"
                      "Server=DESKTOP-5422GFU;"
                      "Database=Python_Data;"
                      "Trusted_Connection=yes;"
                      "uid=User;pwd=password")

df = pd.read_sql_query('select * from Persons', cnxn)

df

But when I add this line

df.to_sql('test', schema = 'public', con = cnxn, index = False, 
if_exists = 'replace')

to send data back to the Server I get an error that says

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

I have tried a variety of solutions and just can't get it to work.

Samsonite Manly · Accepted Answer · 2018-09-10 20:57:14Z

-1

I believe the issue is in your "con = cnxn" statement. Try this:

import pandas as pd    
import sqlalchemy
from sqlalchemy import create_engine

OBDC_cnxn='Python_Data_ODBC' #this will be the name of the ODBC connection to this database in your ODBC manager.
engine = create_engine('mssql+pyodbc://'+ODBC_cnxn)

df.to_sql('test', schema = 'public', con = engine, index = False, 
if_exists = 'replace')

answered Sep 10, 2018 at 20:57

Samsonite Manly

7192 gold badges8 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

Error while using the to_sql function [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related