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

Total
1
Shares

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()
  1. 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()

  1. 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 ?


Solution

When import sqlalchemy, you can use to_sql.

Related Post:

pandas to sql server

import sqlalchemy
...  
engine = sqlalchemy.create_engine(
           "mssql+pyodbc://user:[email protected]/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’

enter image description here

Leave a Reply

Your email address will not be published. Required fields are marked *