11

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?

sct.chang
  • 121
  • 1
  • 1
  • 4
  • 2
    Verify that the "bitness" of the installed MySQL Connector/ODBC matches the "bitness" of the version of Python you are using: they must both be 64-bit or both be 32-bit. Also, have you considered trying [MySQL Connector/Python](http://dev.mysql.com/downloads/connector/python/)? – Gord Thompson Feb 03 '15 at 10:06
  • Funny you should suggest the Python connector. Because of time constraints that is exactly what I did to make a deadline. I just wanted pyodbc to work because I already had it imported to pull data from SQL Server. I'll look into the 'bitness' business a little bit later. – sct.chang Feb 03 '15 at 22:35

4 Answers4

18

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.

Marcello B.
  • 4,177
  • 11
  • 45
  • 65
Thomas Johnson
  • 178
  • 1
  • 5
8

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/

Michał Niklas
  • 53,067
  • 18
  • 70
  • 114
1

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.

gbade_
  • 339
  • 1
  • 8
  • 21
0

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

I installed the 'Microsoft Access Database Engine 2010 Redistributable 64-bit' as I have 64-bit system and then added the DSN in the directory of MS Access database and it's working now. Here is the link of the video for how to create DSN and connect to python. https://www.youtube.com/watch?v=zw9P2wSnoIo

viveklande
  • 5
  • 3