I am getting an error when I try to write a pandas dataframe back to my SQL database-CodePudding

I am trying to read the Orders table from the Northwind database and then write it back to the database under a new table name called 'Orders_New'. I have the following Python code:

import pyodbc 
import pandas as pd

conn_str = (
    r'DRIVER={SQL Server};'
    r'SERVER=DESKTOP-A1DUCDS\SQLEXPRESS;'
    r'DATABASE=Northwind;'
    r'Trusted_Connection=yes;'
)
cnxn = pyodbc.connect(conn_str)

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

print(df)

df.to_sql('Orders_New',con=cnxn,index=False)

However, when I run the code it gives me the following error:

DatabaseError: 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); [42S02] [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared. (8180)")

CodePudding user response：

As per the docs, the second parameter of DataFrame.to_sql() is expected to be sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection

con sqlalchemy.engine.(Engine or Connection) or sqlite3.Connection Using SQLAlchemy makes it possible to use any DB supported by that library. Legacy support is provided for sqlite3.Connection objects. The user is responsible for engine disposal and connection closure for the SQLAlchemy connectable See here.