Python：使用 pyodbc 并替换行字段值
我试图弄清楚是否可以使用 pyodbc。我仔细阅读了文档并注意到它说“行值可以被替换”但我没能让它工作。
更具体地说，我试图替换 python 变量中的行值。我尝试过：
将连接自动提交设置为“True”
确保这不是数据类型问题
这是我执行 SQL 查询的代码片段，使用 fetchone() 只获取一条记录（我知道使用此脚本查询仅返回一条记录），然后我获取现有值对于字段（字段位置整数存储在 z 变量中），然后通过从脚本中创建的现有 python 字典访问它来获取我想要写入该字段的新值。
pSQL = "SELECT * FROM %s WHERE %s = '%s'" % (reviewTBL, newID, basinID)
cursor.execute(pSQL)
record = cursor.fetchone()
if record:
oldVal = record[z]
val = codeCrosswalk[oldVal]
record[z] = val
我已经尝试了我能想到的一切，但无法让它发挥作用。我只是误解了帮助文档吗？
脚本成功运行，但新分配的值似乎从未提交。我什至尝试将“print str(record[z])this 放在 record[z] = val 行之后，以查看表中的字段是否具有新值，并且新值会像工作一样打印......但是如果脚本完成后我检查表，旧值仍然在表字段中，
非常感谢对此的任何见解...我希望这会像在 MS Access 数据库中使用 VBA 一样使用 ADO 记录集来工作。循环遍历表中的记录并将值分配给变量中的字段。
谢谢， 汤姆
I'm trying to figure out if it's possible to replace record values in a Microsoft Access (either .accdb or .mdb) database using pyodbc. I've poured over the documentation and noted where it says that "Row Values Can Be Replaced" but I have not been able to make it work.
More specifically, I'm attempting to replace a row value from a python variable. I've tried:
setting the connection autocommit to "True"
made sure that it's not a data type issue
Here is a snippet of the code where I'm executing a SQL query, using fetchone() to grab just one record (I know with this script the query is only returning one record), then I am grabbing the existing value for a field (the field position integer is stored in the z variable), and then am getting the new value I want to write to the field by accessing it from an existing python dictionary created in the script.
pSQL = "SELECT * FROM %s WHERE %s = '%s'" % (reviewTBL, newID, basinID)
cursor.execute(pSQL)
record = cursor.fetchone()
if record:
oldVal = record[z]
val = codeCrosswalk[oldVal]
record[z] = val
I've tried everything I can think bit cannot get it to work. Am I just misunderstanding the help documentation?
The script runs successfully but the newly assigned value never seems to commit. I even tried putting "print str(record[z])this after the record[z] = val line to see if the field in the table has the new value and the new value would print like it worked...but then if I check in the table after the script has finished the old values are still in the table field.
Much appreciate any insight into this...I was hoping this would work like how using VBA in MS Access databases you can use an ADO Recordset to loop through records in a table and assign values to a field from a variable.
thanks,
Tom
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
pyodbc 文档中的“行值可以被替换”指的是您可以修改返回的行对象上的值，例如在开始使用它们之前执行一些清理或转换。这并不意味着这些更改将自动保留在数据库中。为此，您必须使用 sql UPDATE 语句。
The "Row values can be replaced" from the pyodbc documentation refers to the fact that you can modify the values on the returned row objects, for example to perform some cleanup or conversion before you start using them. It does not mean that these changes will automatically be persisted in the database. You will have to use sql UPDATE statements for that.