Error when trying to write data to a Datamart database using pyodbc

Question

I have the following script (I have masked some sensitive information):

import pyodbc
import pandas as pd

pd.set_option('display.max_columns', None) 
server = '1xxxxxxxxxxxxxxx.datamart.pbidedicated.windows.net'
database = 'db_powerbiprodgbr_2xxxxxxx_10054574_fb38'
username ='xxxxxx@xxxxxxxx.co.uk'
Authentication='ActiveDirectoryInteractive'
driver= '{ODBC Driver 17 for SQL Server}'

conn = pyodbc.connect('DRIVER='+driver+
                      ';SERVER='+server+
                      ';PORT=1433;DATABASE='+database+
                      ';UID='+username+
                      ';AUTHENTICATION='+Authentication
                      )

#df=conn.execute("SELECT * FROM model.Sheet1")

df = pd.read_sql("SELECT * FROM model.Sheet1", conn) 

print(df)

df.to_sql("New", conn, schema=None, if_exists='fail')

When I run the script I get the following error:

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

The error comes from the last line:

df.to_sql("New", conn, schema=None, if_exists='fail')

After reading online. Many people are saying that a SQLAlchemy engine needs to be created in order to write to the database. I had tried the following but it didn't work:

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine('mssql+pyodbc://@1xxxxxxxxx/db_xxxxxxxxxxxxx?driver=ODBC+Driver+17+for+SQL+Server')

query = 'SELECT * FROM model.Sheet1'
df = pd.read_sql(sql=query, con=engine)
print(df)

df.to_sql(con=engine, name='table_name', if_exists='replace')

Error when trying to write data to a Datamart database using pyodbc

0 Answers0