I am working with an MSSQL database which runs on an On premise server. To access this database from python I use SQLalchemy (see code below). When I call the code below directly from the server it works great, but if I move the code to my local machine it throws an error.
The server is reachable from my local machine. (If I call ping {Host} I get a valid response and also I am connected to the same database with DBeaver).
As my first suspect was that different versions of some libraries are the reason I updated pandas, pyodbc and sqlalchemy all to the newest version on both machines.
As I understand the error message, it suggests that the connection string is wrong (which can hardly be if it works on the server?) My second suspect is that I need to install some thing on my local machine for the driver to work, but I cannot quite figure out what this would be. What am I missing? Does anyone know what the problem is?
ErrorMessage (Sorry that the last part is in German but it basically translates to "datasourcename could not be found and no default driver was defined"):
InterfaceError: (pyodbc.InterfaceError) ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben (0) (SQLDriverConnect)')
(Background on this error at: https://sqlalche.me/e/14/rvf5)
Note that I have replaced all variables which are written in {} for this question.
import pandas as pd
import sqlalchemy
query = "SELECT TOP(10)* From {table} "
engine = sqlalchemy.create_engine(f'mssql+pyodbc://{USER}:{PASSWORD}@{HOSTNAME}:1433/{DATABASENAME}?driver=SQL+Server+Native+Client+11.0', echo=True)
with engine.connect() as con:
SQl_result_df = pd.read_sql(query, con, coerce_float=False)