Execution failed on sql - Error - Pandas.to.sql()

Question

I want to save a data frame in a Database table. What I did :

Connect to azure Sql server DB

import pyodbc

# Create
server = 'XXXXXXXXXXXXXXXXXXXX'
database = 'XXXXXXXXXXXXXXXXXXX' 
username = 'XXXXXXXXXXXXXXXX'
password = 'XXXXXXXXXXXX'
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

Create Table

#create_table = """
CREATE TABLE forecast_data (
    CompanyEndDate text,
    Retailer text,
    Store_Name text,
    Category text,
    Description text,
    QtySold int);

cursor.execute(create_table)
cnxn.commit()

Use pandas to_sql

data.to_sql('forecast_data', con=cnxn)

I get this error:

ProgrammingError                          Traceback (most recent call last)
/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1680         try:
-> 1681             cur.execute(*args, **kwargs)
   1682             return cur

ProgrammingError: ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW)")

The above exception was the direct cause of the following exception:

DatabaseError                             Traceback (most recent call last)
7 frames
/usr/local/lib/python3.7/dist-packages/pandas/io/sql.py in execute(self, *args, **kwargs)
   1691 
   1692             ex = DatabaseError(f"Execution failed on sql '{args[0]}': {exc}")
-> 1693             raise ex from exc
   1694 
   1695     @staticmethod

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': ('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'sqlite_master'. (208) (SQLExecDirectW)")

Any one have an idea what is going on ?

buymeacoffee · Accepted Answer · 2021-05-25 16:25:12Z

1

When import sqlalchemy, you can use to_sql.

Related Post:

pandas to sql server

import sqlalchemy
...  
engine = sqlalchemy.create_engine(
           "mssql+pyodbc://user:pwd@server/database",
           echo=False)
data.to_sql('forecast_data', con=cnxn, if_exists='replace')

When import pyodbc, you can use to_sql.

Your code should like below. You also can read my answer in below post.

Logon failed (pyodbc.InterfaceError) ('28000', "[28000] [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'xxxx'

answered May 25, 2021 at 16:25

buymeacoffee

22.6k2 gold badges23 silver badges52 bronze badges

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

1 Comment

tbone Over a year ago

Is it possible to open a dataframe (against MS SQL SERVEr) with this syntax? I have tried every option I've come across and they all get one error or anoother.

Collectives™ on Stack Overflow

Execution failed on sql - Error - Pandas.to.sql()

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related