pyodbc + MySQL + Windows:找不到数据源名称，并且未指定默认驱动程序 [英] pyodbc + MySQL + Windows: Data source name not found and no default driver specified

问题描述

我正在尝试使用pyodbc连接到Windows Server 2008 R2本地主机上的MySQL 5.6.我将完整安装用于localhost上的MySQL实例，包括ODBC连接器.我可以很漂亮地将它连接到远程SQL Server实例，但是为了我的一生，我无法让它连接到本地MySQL实例.我使用的是来自connectionstrings.com 的指南.

以下是一些代码:

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

这是错误:

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

我尝试了所有操作，从添加Provider=MSDASQL;到将连接字符串中的ANSI更改为UNICODE.有什么建议吗?

解决方案

我遇到了类似的问题.我正在使用Windows 8和mysql.

我解决问题的方法是进入

控制面板>系统和安全性>管理工具.> ODBC数据源

32位或64位版本取决于您的计算机.

然后单击系统DNS文件.如果看不到任何MySQL驱动程序，则必须单击ADD.它弹出一个列表，从该列表中选择MySQL驱动程序.

对我来说，它是MySQL ODBC 5.3 ANSI(它们也有一个unicode驱动程序).单击完成.完成此操作后，您必须将代码中的连接线更改为刚填写的相应驱动程序.

例如:

def create_mysql_conn():

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

这应该起作用，或者至少它解决了我的连接问题，因为我尝试的所有东西都遇到了各种不同的错误.这就是为我解决问题的原因.

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?

解决方案

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.

这篇关于pyodbc + MySQL + Windows:找不到数据源名称，并且未指定默认驱动程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！