pyodbc + MySQL + Windows：未找到数据源名称且未指定默认驱动程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28288902/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
pyodbc + MySQL + Windows: Data source name not found and no default driver specified
提问by sct.chang
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.comas reference.
我正在尝试使用 pyodbc 连接到 Windows Server 2008 R2 本地主机上的 MySQL 5.6。我在本地主机上使用了 MySQL 实例的完整安装，包括 ODBC 连接器。我将它连接到远程 SQL Server 实例，但在我的一生中，我无法让它连接到本地 MySQL 实例。我使用来自 connectionstrings.com 的本指南作为参考。
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 ANSIto UNICODEin the connection string. Any suggestions?
我什么都试过，从加入Provider=MSDASQL;到改变ANSI到UNICODE连接的字符串中。有什么建议？
回答by Thomas Johnson
I was having a similar issue. I am using windows 8, and mysql.
我遇到了类似的问题。我正在使用 Windows 8 和 mysql。
The way I solved the problem was by going into my
我解决问题的方法是进入我的
control panel>Systems and Security>Administrative Tools.>ODBC Data Sources
控制面板>系统和安全>管理工具。>ODBC数据源
Either the 32 bit or 64 bit version depending on your computer.
32 位或 64 位版本取决于您的计算机。
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.
然后单击系统 DNS 文件。如果您没有看到任何 MySQL 驱动程序，您必须单击添加。它会弹出一个列表，从该列表中选择 MySQL 驱动程序。
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.
对我来说是 MySQL ODBC 5.3 ANSI（他们也有一个 unicode 驱动程序）。单击完成。完成此操作后，您必须将代码中的连接线更改为刚刚填写的相应驱动程序。
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.
这应该有效，或者至少它解决了我的连接问题，因为我尝试过的一切都遇到了各种不同的错误。这就是为我解决问题的原因。
回答by Micha? Niklas
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 SysWoW64directory) to create SystemDSN. With SystemDSN you can check if you can connect to the database. Then try to connect to DB from Python code.
在 Win64 中有两种 ODBC 环境：32 位和 64 位，您可以使用 Python 32 位或 Python 64 位。检查您使用的 Python 版本，然后使用正确版本odbcad32.exe（32 位版本位于SysWoW64目录中）来创建 SystemDSN。使用 SystemDSN，您可以检查是否可以连接到数据库。然后尝试从 Python 代码连接到 DB。
You can list ODBC datasources available to pyodbcvia my code recipe: https://code.activestate.com/recipes/578815-printing-list-of-odbc-data-sources-with-pyodbc-mod/
您可以pyodbc通过我的代码配方列出可用的 ODBC 数据源：https: //code.activestate.com/recipes/578815-printing-list-of-odbc-data-sources-with-pyodbc-mod/
回答by gbade_
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.
前往管理工具和 ODBC 数据源。您应该单击 DNS 文件。您很可能会看到 Microsoft Access 和 excel 作为唯一可用的数据源，因此您需要添加 SQL Server。我的是 SQL Server 2008 R2，到目前为止，它已经工作了。
回答by viveklande
For the error mentioned "pyodbc.Error: ('IM002', '[IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (0) (SQLDriverConnect)')"
对于提到的错误“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
我安装了“Microsoft Access Database Engine 2010 Redistributable 64-bit”，因为我有 64 位系统，然后在 MS Access 数据库的目录中添加了 DSN，现在它正在工作。这是有关如何创建 DSN 并连接到 python 的视频链接。 https://www.youtube.com/watch?v=zw9P2wSnoIo
