是否可以使用pyodbc读取Paradox gui中打开的Paradox表? [英] Is it possible to use pyodbc to read Paradox tables that are open in the Paradox gui?

问题描述

我正在一个管理非常差的旧版Paradox数据库系统的环境中工作. (我不是管理员.)我一直在使用pyodbc与我们的表进行交互，并且基本功能似乎正常工作.这是一些(有效的)测试代码:

I'm working in a environment with a very poorly managed legacy Paradox database system. (I'm not the administrator.) I've been messing around with using pyodbc to interact with our tables, and the basic functionality seems to work. Here's some (working) test code:

import pyodbc

LOCATION = "C:\test"

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

问题在于，我们大多数重要表几乎总是在某人的Paradox GUI中打开.每当我尝试从这些表之一执行select时，都会出现此错误:

The problem is that most of our important tables are going to be open in someone's Paradox GUI at pretty much all times. I get this error whenever I try to do a select from one of those tables:

pyodbc.Error: ('HY000', "[HY000] [Microsoft][ODBC Paradox Driver] Could not lock 
table 'test'; currently in use by user '(unknown)' on machine '(unknown)'. (-1304) 
(SQLExecDirectW)")

显然，这是因为在调用cursor.execute()时pyodbc试图锁定该表.这种行为非常合理，因为cursor.execute()运行任意SQL代码并可能更改表.

This is, obviously, because pyodbc tries to lock the table when cursor.execute() is called on it. This behavior makes perfect sense, since cursor.execute() runs arbitary SQL code and could change the table.

但是，Paradox本身(通过其gui)似乎可以很好地处理多个用户.如果您在人们使用表格时尝试对其进行重组，它只会给您带来类似的错误.

However, Paradox itself (through its gui) seems to handle multiple users fine. It only gives you similar errors if you try to restructure the table while people are using it.

有什么办法可以让pyodbc使用某种只读模式，从而在我做select之类的时候不必锁定表?还是锁定了我将无法解决的工作原理的基本部分?

Is there any way I can get pyodbc to use some sort of read-only mode, such that it doesn't have to lock the table when I'm just doing select and such? Or is locking a fundamental part of how it works that I'm not going to be able to get around?

使用其他模块的解决方案也完全可以.

Solutions that would use other modules are also totally fine.

推荐答案

好，我终于明白了.

显然，odbc不喜欢没有主键的Paradox表.在任何情况下，您都无法更新没有主键的表，除非您是唯一尝试访问该表的用户，否则您将无法从没有主键的表中读取内容.

Apparently, odbc dislikes Paradox tables which have no primary key. You cannot update tables with no primary key under any circumstances, and you cannot read from tables with no primary key unless you are the only user trying to access that table.

不相关地，如果您不提供密码，从受密码保护的表中也会得到基本上相同的错误消息.

Unrelatedly, you get essentially the same error messages from password-protected tables if you don't supply a password.

因此，我正在两个不同的表上测试脚本，其中一个表既具有密码又具有主键，而其中一个则既没有密码又具有主键.我假设错误消息具有相同的根本原因，但是实际上这是两个不同的问题，具有不同的解决方案.

So I was testing my script on two different tables, one of which has both a password and a primary key, and one of which had neither. I assumed the error messages had the same root cause, but it was actually two different problems, with different solutions.

如果在某人的GUI中打开没有主键的表，似乎仍然无法访问它们，但这是一个较小的问题.

There still seems to be no way to get access to tables without primary keys if they are open in someone's GUI, but that's a smaller issue.

这篇关于是否可以使用pyodbc读取Paradox gui中打开的Paradox表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！