▲hckr.fyi // thoughts

PYODBC--Data source name not found and no default driver specified

by Michael Szul on

Python has been an amazing language for a very long period of time, supplanting Perl after the failure of Perl 6 to materialize into the promised revamp it was supposed to be. With the rise of data science and machine learning, Python's stock has continued to skyrocket as the language of choice for many programmers in data science thanks to packages like Pandas and Numpy.

Sometimes though, Python can still feel a bit cobbled together when trying to use it on a Windows machine. Ever move from one machine to another, and then suddenly get the following error:

PYODBC--Data source name not found and no default driver specified

Everything was working fine on your machine, but a production deployment is suddenly blowing up.

The problem is that there are multiple ODBC drivers available for SQL Server, and a driver name could be different on your production machine than your local machine.

For example, maybe you have this:

DATABASE='DRIVER={SQL Server Native Client 11.0};SERVER=database.example.com;DATABASE=MYDB;UID=MYUSER;PWD=HulkSad!'

In this example, the driver is named {SQL Server Native Client 11.0}, but in production, it might be {SQL Server}.

In fact, there are numerous drivers:

The ones above are the ones recommended by Microsoft moving forward; however, there are a handful of deprecated drivers that you'll likely need to consider:

The one you need depends on the version of SQL Server that you're running. You can get this information in the Administrative Tools section of the Control Panel in Windows. Just click on Data Sources and look for the Drivers section. Alternatively, you could always use trial-and-error.