Python Pyodbc 错误数据源名称未找到且未指定默认驱动程序悖论

Pyodbc error Data source name not found and no default driver specified paradox

提问by Suit Boy Apps

I am attempting to use pyobdcto read data from a paradox database, and I keep getting the following error when attempting to connect to the database:

我正在尝试使用pyobdc从悖论数据库读取数据，并且在尝试连接到数据库时不断收到以下错误：

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

I have tried to create new DNS links for the database but it has not helped what so ever.

我曾尝试为数据库创建新的 DNS 链接，但它并没有帮助。

My system links are as follows:

我的系统链接如下：

My code is:

我的代码是：

import os
import sys
import time
import pyodbc

LOCATION = "c:\Users\Marcello\Desktop\DATA\ScorMonitor.db"

cnxn = pyodbc.connect(r"Driver={{Microsoft Paradox Driver (*.db )}};Fil=Paradox 5.X;DefaultDir={0};Dbq={0}; CollatingSequence=ASCII;")
cursor = cnxn.cursor()
cursor.execute("select last, first from test")
row = cursor.fetchone()
print row

采纳答案by Chad Kennedy

Two thoughts on what to check:

关于检查什么的两个想法：

1) Your connection string is wrong. There's a way to get a known good connection string directly from the ODBC Administrator program (taken from http://www.visokio.com/kb/db/dsn-less-odbc). These instructions assume you're using an MDB, but the same process will work for a paradox file

1) 您的连接字符串错误。有一种方法可以直接从 ODBC 管理器程序（取自http://www.visokio.com/kb/db/dsn-less-odbc）获取已知良好的连接字符串。这些说明假设您使用的是 MDB，但同样的过程也适用于悖论文件

• On a typical client PC, open Control Panel -> Administrative Tools -> Data Sources.
• Select the File DSN tab and click Add.
• Select the appropriate driver (e.g. "Microsoft Access Driver (*.mdb)") and click Next
• Click Browse and choose where you want to save the .dsn file (this is a temporary file you are going to delete later).
• Click Next then Finish.
• You will be shown the vendor-specific ODBC setup dialog. For example, with Microsoft Access, you might only need to click Select and browse to an existing .mdb file before clicking OK.
• Browse to the location of the .dsn file and open using Notepad.

• 在典型的客户端 PC 上，打开控制面板 -> 管理工具 -> 数据源。
• 选择文件 DSN 选项卡，然后单击添加。
• 选择适当的驱动程序（例如“Microsoft Access Driver (*.mdb)”）并单击下一步
• 单击浏览并选择要保存 .dsn 文件的位置（这是您稍后将要删除的临时文件）。
• 单击下一步然后完成。
• 您将看到供应商特定的 ODBC 设置对话框。例如，对于 Microsoft Access，您可能只需要在单击确定之前单击选择并浏览到现有的 .mdb 文件。
• 浏览到 .dsn 文件的位置并使用记事本打开。

In the DSN file you might see something similar to:

在 DSN 文件中，您可能会看到类似于以下内容的内容：

[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=C:\
DBQ=C:\db1.mdb

To convert the above to the full connection strring:

将上述内容转换为完整的连接字符串：

1. Omit the first [ODBC] line
2. Put curly braces around all values containing spaces
3. Put all name=value pairs on one line, separated by semicolons.

1. 省略第一行 [ODBC]
2. 在所有包含空格的值周围放置花括号
3. 将所有名称=值对放在一行上，用分号分隔。

This gives you the full connection string. In this example, the string becomes:

这为您提供了完整的连接字符串。在这个例子中，字符串变成：

DRIVER={Microsoft Access Driver (*.mdb)};UID=admin;UserCommitSync=Yes;Threads=3;SafeTransactions=0;PageTimeout=5;axScanRows=8;MaxBufferSize=2048;FIL={MS Access};DriverId=25;DefaultDir=C:\;DBQ=C:\db1.mdb

2) 32/64 bit mismatch. I've had troubles when mixing 32-bit python with 64-bit drivers, or vice-versa. You may want to check your Python interpreter and database driver line up.

2) 32/64 位不匹配。我在将 32 位 python 与 64 位驱动程序混合时遇到了麻烦，反之亦然。您可能需要检查您的 Python 解释器和数据库驱动程序。

回答by Grimravus

Thanks for the question, I had a similar problem, and this question and the answers helped lead me to what I needed. The problem for me ended up being a mismatch between 64-bit Python and 32-bit ODBC Driver on Windows 10 (as Chad Kennedy suggested). I'm running a fully updated Fall Creator's Edition, and had Microsoft Office Pro 2016 installed. The MS Office installer still defaults to a 32-bit installation (don't get me started...) -- it doesn't ask about this at install time, so imagine my surprise when I discovered I was running 32-bit Office. Because of this, it installs the 32-bit ODBC driver for MS Access. There is a tiny unnoticeable link you can click in the MS Office installer dialog to force the 64-bit install.

谢谢你的问题，我有一个类似的问题，这个问题和答案帮助我找到了我需要的东西。对我来说，问题最终是 Windows 10 上 64 位 Python 和 32 位 ODBC 驱动程序之间的不匹配（正如 Chad Kennedy 所建议的）。我正在运行完全更新的 Fall Creator's Edition，并安装了 Microsoft Office Pro 2016。MS Office 安装程序仍然默认为 32 位安装（不要让我开始……）——它在安装时不会询问这个问题，所以想象一下当我发现我正在运行 32 位 Office 时我的惊讶. 因此，它会为 MS Access 安装 32 位 ODBC 驱动程序。您可以在 MS Office 安装程序对话框中单击一个不明显的微小链接以强制进行 64 位安装。

A 64-bit Python installation won't work with the 32-bit Microsoft Access ODBC driver, and Microsoft won't let you install the 64-bit ODBC driver if you have 32-bit MS Office installed on the machine.

64 位 Python 安装不适用于 32 位 Microsoft Access ODBC 驱动程序，如果您在计算机上安装了 32 位 MS Office，Microsoft 将不允许您安装 64 位 ODBC 驱动程序。

The fix was to UNINSTALL MS Office, and re-install it by using that tiny link on the install dialog to tell it to install as 64-bit. Don't worry, it remembers all of your recent files and settings, and email accounts in Outlook. Once that was done, I had the 64-bit ODBC driver, and my Python code connected to the database with no further problems.

修复方法是卸载 MS Office，然后通过使用安装对话框上的那个小链接告诉它安装为 64 位来重新安装它。不用担心，它会记住您最近使用的所有文件和设置，以及 Outlook 中的电子邮件帐户。完成后，我就有了 64 位 ODBC 驱动程序，并且我的 Python 代码连接到数据库没有其他问题。

回答by neogeomat

The shortcut for setting ODBC data sources maybe pointing to the 32bit data sources instead of 64bit.

设置 ODBC 数据源的快捷方式可能指向 32 位数据源而不是 64 位。

Go to control panel -> administrative tools --> select data sources(ODBC) --> then right click on that file --> go to properties --> in the shortcut tab -> change the path from %windir%\System32\odbcad32.exeto

转到控制面板-> 管理工具--> 选择数据源（ODBC）--> 然后右键单击该文件--> 转到属性--> 在快捷方式选项卡中-> 将路径更改%windir%\System32\odbcad32.exe为

%windir%\SysWOW64\odbcad32.exe

%windir%\SysWOW64\odbcad32.exe

回答by Mecaxis14

This problem can be solved having Office and Python interpreter with same bits configuration. But, it's true, I had 64bit Office and Python and doesn't work ...

这个问题可以通过使用相同位配置的 Office 和 Python 解释器来解决。但是，这是真的，我有 64 位 Office 和 Python 并且不起作用......

Here is the solution.

这是解决方案。

There are actually two (2) different Access ODBC drivers from Microsoft:

Microsoft 实际上有两 (2) 个不同的 Access ODBC 驱动程序：

1. Microsoft Access Driver (*.mdb)

   Microsoft Access Driver (*.mdb) - This is the older 32-bit "Jet" ODBC driver. It is included as a standard part of a Windows install. It only works with .mdb (not .accdb) files. It is also officially deprecated.

2. Microsoft Access Driver (*.mdb, *.accdb)

   Microsoft Access Driver (*.mdb, *.accdb) - This is the newer "ACE" ODBC driver. It is not included with Windows, but it is normally included as part of a Microsoft Office install. It is also available as a free stand-alone "redistributable" installer for machines without Microsoft Office. There are separate 64-bit and 32-bit versions of the "ACE" Access Database Engine (and drivers), and normally one has either the 64-bit version or the 32-bit version installed. (It is possible to force both versions to exist on the same machine but it is not recommended as it can "break" Office installations. Therefore, if you already have Microsoft Office it is highly recommended that you use a Python environment that matches the "bitness" of the Office install.)

1. Microsoft Access 驱动程序 (*.mdb)

   Microsoft Access 驱动程序 (*.mdb) - 这是较旧的 32 位“Jet”ODBC 驱动程序。它作为 Windows 安装的标准部分包含在内。它仅适用于 .mdb（而非 .accdb）文件。它也被官方弃用。

2. Microsoft Access 驱动程序（*.mdb、*.accdb）

   Microsoft Access 驱动程序 (*.mdb, *.accdb) - 这是较新的“ACE”ODBC 驱动程序。它不包含在 Windows 中，但通常作为 Microsoft Office 安装的一部分包含在内。它也可作为免费的独立“可再发行”安装程序用于没有 Microsoft Office 的机器。“ACE”Access 数据库引擎（和驱动程序）有单独的 64 位和 32 位版本，通常安装了 64 位版本或 32 位版本。（可以强制两个版本存在于同一台机器上，但不建议这样做，因为它可以“破坏”Office 安装。因此，如果您已经拥有 Microsoft Office，强烈建议您使用与“位”的 Office 安装。）

The easiest way to check if one of the Microsoft Access ODBC drivers is available to your Python environment (on Windows) is to do

检查 Microsoft Access ODBC 驱动程序之一是否可用于您的 Python 环境（在 Windows 上）的最简单方法是

import pyodbc

[x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]

导入pyodbc

[x for x in pyodbc.drivers() if x.startswith('Microsoft Access Driver')]

If you see an empty list then you are running 64-bit Python and you need to install the 64-bit version of the "ACE" driver. If you only see ['Microsoft Access Driver (*.mdb)'] and you need to work with an .accdb file then you need to install the 32-bit version of the "ACE" driver.

如果您看到一个空列表，那么您正在运行 64 位 Python，您需要安装 64 位版本的“ACE”驱动程序。如果您只看到 ['Microsoft Access Driver (*.mdb)'] 并且您需要使用 .accdb 文件，那么您需要安装 32 位版本的“ACE”驱动程序。

In this link you can download your driver LINK

在此链接中，您可以下载驱动程序LINK

Info source

信息来源

Regards!

问候！