pyodbc + MySQL + Windows: Data source name not found and no default driver specified

I was having a similar issue. I am using windows 8, and mysql.

The way I solved the problem was by going into my

control panel>Systems and Security>Administrative Tools.>ODBC Data Sources

Either the 32 bit or 64 bit version depending on your computer.

Then you click on the System DNS file. If you do not see any MySQL driver you have to click ADD. It brings up a list, from that list select the MySQL driver.

For me it was MySQL ODBC 5.3 ANSI(they have a unicode driver also). Click finish. Once you do that then you have to change your connection line in your code to the corresponding Driver that you just filled out.

Ex:

def create_mysql_conn(): return pyodbc.connect(r'Driver={MySQL ODBC 5.3 ANSI Driver};Server=MSSQLSRV;Database=ecomm;Trusted_Connection=yes;')

This should work, or at least it solved my connection issue because I was getting all sorts of different errors with everything I tried. This was what solved the issue for me.

In Win64 there are two ODBC environments: 32 and 64 bit and you can work with Python 32 bit or Python 64 bit. Check what version of Python you use and then use proper version of odbcad32.exe (32 bit version is located in the SysWoW64 directory) to create SystemDSN. With SystemDSN you can check if you can connect to the database. Then try to connect to DB from Python code.

You can list ODBC datasources available to pyodbc via my code recipe: https://code.activestate.com/recipes/578815-printing-list-of-odbc-data-sources-with-pyodbc-mod/

Head to Administrative Tools and the ODBC Data Sources. You should click on the DNS file. Most likely you'd see Microsoft Access and excel as the only data sources available, so you need to add your SQL Server. Mine was SQL Server 2008 R2, and so far, it has worked.