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

Question

I am trying to connect to MySQL 5.6 on a Windows Server 2008 R2 localhost with pyodbc. I used the full installation for the MySQL instance on the localhost, including the ODBC connector. I have it connecting to a remote SQL Server instance beautifully, but for the life of me I can't get it to connect to the local MySQL instance. I am using this guide from connectionstrings.com as reference.

Here's some code:

import pyodbc

def create_mssql_conn():
    return pyodbc.connect(r'Driver={SQL Server};Server=MSSQLSRV;Database=ecomm;Trusted_Connection=yes;')

def create_mysql_conn():
    return pyodbc.connect(r'Provider=MSDASQL;Driver={MySQL ODBC 5.6 UNICODE Driver};Server=127.0.0.1;Database=ecomm;User=root;Password=myP@$$w0rd;Option=3;')

# conn = create_mssql_conn() # This one works
conn = create_mysql_conn() # This one breaks
cursor = conn.cursor()
cursor.execute('SELECT * FROM inventory')

while 1:
    row = cursor.fetchone()
    if not row:
        break
    print row

Here is the error:

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

I tried everything from adding Provider=MSDASQL; to changing ANSI to UNICODE in the connection string. Any suggestions?

Answer 1

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.