通过 PYODBC 的 SQL 查询在一台机器上默默失败，在另一台机器上工作

发布于 2024-08-17 06:09:47 字数 1326 浏览 5 评论 0原文

我正在开发一个程序来自动解析 XML 文件中的数据并将其存储到多个数据库中。（具体来说，USGS 实时水质服务，如果有人感兴趣，请访问 http: //waterservices.usgs.gov/rest/WaterML-Interim-REST-Service.html) 它是使用 LXML 和 PYODBC 用 Python 2.5.1 编写的。数据库为Microsoft Access 2000。

连接函数如下：
def get_AccessConnection(db):
connString = 'DRIVER={Microsoft Access 驱动程序 (*.mdb)};DBQ=' + db
cnxn = pyodbc.connect(connString, autocommit=False)
光标 = cnxn.cursor()
返回cnxn，光标
其中 db 是数据库的文件路径。

节目内容：
a) 打开与数据库的连接
b) 解析该数据库的 2 到 8 个 XML 文件，并将其中的值构建为一系列记录以插入到数据库中（使用嵌套字典结构，而不是用户定义的类型）
c) 循环遍历一系列记录，cursor.execute() - 为每个记录执行一个 SQL 查询
d) 提交并关闭数据库连接

如果cursor.execute() 调用引发错误，它将回溯和查询写入日志文件并继续。

当我的同事在他的机器上运行它时，对于一个特定的数据库，特定的记录根本不存在，也没有记录任何错误。当我在计算机上通过完全相同的网络路径在完全相同的数据库副本上运行完全相同的代码时，所有应该存在的数据都在那里。

我和我的同事都使用 Windows XP 计算机，安装了 Microsoft Access 2000 和相同版本的 Python、lxml 和 pyodbc。我不知道如何检查我们是否拥有相同版本的 Microsoft ODBC 驱动程序。我无法找到存在的记录和不存在的记录之间的任何区别。我正在测试其他数据库是否会出现同样的问题，以及第三位同事的计算机上是否也会出现同样的问题。

我真正想知道的是任何人都能想到的会导致这种情况的事情，因为这对我来说没有意义。总结一下：执行 SQL 查询的 Python 代码在一台计算机上会默默地失败一半，而在另一台计算机上则可以完美运行。

编辑：
没有更多问题了。我刚刚让我的同事再次运行它，数据库已完全更新，没有丢失记录。仍然不知道为什么它一开始就失败了，也不知道它是否会再次发生，但“问题解决了”。

原文

I am working on a program to automate parsing data from XML files and storing it into several databases. (Specifically the USGS realtime water quality service, if anyone's interested, at http://waterservices.usgs.gov/rest/WaterML-Interim-REST-Service.html) It's written in Python 2.5.1 using LXML and PYODBC. The databases are in Microsoft Access 2000.

The connection function is as follows:
def get_AccessConnection(db):
connString = 'DRIVER={Microsoft Access Driver (*.mdb)};DBQ=' + db
cnxn = pyodbc.connect(connString, autocommit=False)
cursor = cnxn.cursor()
return cnxn, cursor
where db is the filepath to the database.

The program:
a) opens the connection to the database
b) parses 2 to 8 XML files for that database and builds the values from them into a series of records to insert into the database (using a nested dictionary structure, not a user-defined type)
c) loops through the series of records, cursor.execute()-ing an SQL query for each one
d) commits and closes the database connection

If the cursor.execute() call throws an error, it writes the traceback and the query to the log file and moves on.

When my coworker runs it on his machine, for one particular database, specific records will simply not be there, with no errors recorded. When I run the exact same code on the exact same copy of the database over the exact same network path from my machine, all the data that should be there is there.

My coworker and I are both on Windows XP computers with Microsoft Access 2000 and the same versions of Python, lxml, and pyodbc installed. I have no idea how to check whether we have the same version of the Microsoft ODBC drivers. I haven't been able to find any difference between the records that are there and the records that aren't. I'm in the process of testing whether the same problem happens with the other databases, and whether it happens on a third coworker's computer as well.

What I'd really like to know is ANYTHING anyone can think of that would cause this, because it doesn't make sense to me. To summarize: Python code executing SQL queries will silently fail half of them on one computer and work perfectly on another.

Edit:
No more problem. I just had my coworker run it again, and the database was updated completely with no missing records. Still no idea why it failed in the first place, nor whether or not it will happen again, but "problem solved."

分享到QQ

分享到微博