0

I am trying to load data frame 'f3' to MS SQL server running in localhost with the help of python script. Here is my code;

import sqlalchemy

import pyodbc

engine = sqlalchemy.create_engine("mssql+pyodbc://localhost/test_project")

f3.to_sql("test", engine)

However, I am getting an error which I could not figure out what I did wrong. Can somebody please help me to find what's wrong I am doing?

Below is the error I am getting;

DBAPIError: (pyodbc.Error) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')

write the DataFrame to a table in the SQL database

lazzy_ms
  • 1,169
  • 2
  • 18
  • 40
Ishwor Bhatta
  • 139
  • 1
  • 3
  • 13
  • You left out the crucial information: what is the error? We probably don't need the whole Python backtrace, but we certainly need the text of the SQL Server error. IIRC `to_sql` normally creates a table. If you don't have permission to do that, that might would be one issue. – James K. Lowden Feb 25 '17 at 18:57
  • Thanks James. I just added that information. Can you please help me now? Its a local machine. So I put localhost and no username and password. Could that be an issue? – Ishwor Bhatta Feb 25 '17 at 19:02
  • The error indicates you're not connecting to the database. You're not passing the right connection information to sqlalchemy, or your basic ODBC setup isn't working. Make sure something simple like `select count(*) from systables` works in pyodbc, then do the same in sqlalchemy. After that works, *then* try something with Pandas. – James K. Lowden Feb 27 '17 at 19:06

1 Answers1

0

The problem you made in the connection string with mssql database. You have to put it as show in below,

engine = sqlalchemy.create_engine('mssql+pyodbc://'+user+':'+password+'@'+host+':'+port+'/'+database+'?'\
          +'driver=SQL+Server+Native+Client+11.0')

Please make sure that, put correct details into variables that used in connection string. You may use different driver as well according to your please change the driver, in my case I put SQL+Server+Native+Client+11.0.

Dinusha Thilakarathna
  • 422
  • 6
  • 13