0

I have a pandas data frame called : data I am trying to read this pandas dataframe into a table in sql server. I am able to read data into python from sql but I am expiering problems loading the dataframe into a table.

I have tried a few examples but keep on getting the same 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)")

At the moment I have the following code:

PASSW           = 'test'   
SERVER          = '111.111.11'
DB              = 'Database'
Table           = 'TableName'



data = pd.read_csv('2019_Prediction_24_10.csv')
cnxn = pyodbc.connect(DRIVER='{ODBC Driver 13 for SQL Server}', SERVER= SERVER, 
                          DATABASE= DB,User = 'User', Password = PASSW)

data.to_sql(con=cnxn, name='Predictions',schema = 'PA' ,if_exists='replace')

I am new to pyodbc and using python together with sql server, Am not quite sure what is going wrong let alone fix it.

Can please someone assist me, or point me in the right direction

Helena Viljoen
  • 19
  • 4

1 Answers1

0

As noted in the to_sql documentation:

con : sqlalchemy.engine.Engine or sqlite3.Connection

You have supplied to_sql with a (pyodbc) Connection object, so pandas is treating it like a SQLite connection. To use to_sql with SQL Server you'll need to install SQLAlchemy, create an Engine object, and pass that to to_sql.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418