错误：通过Python增加MaxLocksPerFile注册表条目

Question

我正在从Python运行一个相当复杂的更新MS-Access查询：

 qry = '''
        UPDATE H500_ODFlows INNER JOIN H500_UPDATE ON 
        (H500_ODFlows.Product = H500_UPDATE.Product) 
        AND (H500_ODFlows.Dest = H500_UPDATE.DestCode) 
        AND (H500_ODFlows.Orig = H500_UPDATE.OrigCode) 
        SET H500_ODFlows.Pieces = [H500_UPDATE].[Pieces],
        H500_ODFlows.Weight = [H500_UPDATE].[Weight], 
        H500_ODFlows.Cons = [H500_UPDATE].[Pieces], 
        H500_ODFlows.DeadWeight = [H500_UPDATE].[DeadWeight], 
        H500_ODFlows.DoNotRead = [H500_UPDATE].DoNotRead,
        H500_ODFlows.[_OrigCountryCode] = [H500_UPDATE].[_OrigCountryCode],
        H500_ODFlows.[_DestCountryCode] = [H500_UPDATE].[_DestCountryCode]
    '''

try:
    crsr.execute(lb.cleanqry(qry))
    cnxn.commit()
    print('Updating was successful.')
except Exception as err:
    print('Updating failed. See the error.' + str(err))

但得到以下错误：

('HY000', '[HY000] [Microsoft][ODBC Microsoft Access Driver] File sharing lock count exceeded. Increase MaxLocksPerFile registry entry. (-1033) (SQLExecDirectW)')

我按照指示增加了“MaxLocksPerFile”，但它没有帮助。此外，查询在msaccess中运行非常正常，但不是通过Python运行的。有什么建议吗？在

Answer 1

尝试使用autocommit on运行查询。这样，数据库就不需要让所有这些锁保持打开状态，而只需在查询运行时提交所有内容。在

 qry = '''
        UPDATE H500_ODFlows INNER JOIN H500_UPDATE ON 
        (H500_ODFlows.Product = H500_UPDATE.Product) 
        AND (H500_ODFlows.Dest = H500_UPDATE.DestCode) 
        AND (H500_ODFlows.Orig = H500_UPDATE.OrigCode) 
        SET H500_ODFlows.Pieces = [H500_UPDATE].[Pieces],
        H500_ODFlows.Weight = [H500_UPDATE].[Weight], 
        H500_ODFlows.Cons = [H500_UPDATE].[Pieces], 
        H500_ODFlows.DeadWeight = [H500_UPDATE].[DeadWeight], 
        H500_ODFlows.DoNotRead = [H500_UPDATE].DoNotRead,
        H500_ODFlows.[_OrigCountryCode] = [H500_UPDATE].[_OrigCountryCode],
        H500_ODFlows.[_DestCountryCode] = [H500_UPDATE].[_DestCountryCode]
    '''

try:
    cnxn.autocommit = True
    crsr.execute(lb.cleanqry(qry))
    print('Updating was successful.')
except Exception as err:
    print('Updating failed. See the error.' + str(err))

Answer 2

因为您注意到：查询在msaccess中运行得很好，但不是通过Python运行的。其中一个可能的原因是，访问存储查询比应用层查询更高效，因为引擎保存和缓存最佳执行计划。在应用层（Python、VBA等）中，当处理stringsql语句时，Jet/ACE引擎没有时间计划最佳执行。在

因此，请考虑以下几点：

1. 向相应表的JOIN变量添加所需的任何索引。在
2. 将UPDATE查询另存为数据库中存储的查询。在这里，保存过程检查语法、计算和优化计划以及缓存统计信息。在
3. 在数据库中运行Compact&Repair以刷新统计信息。在

然后，使用CALL命令将查询作为存储过程在Python中运行：

# SET AUTOCOMMIT PREFERENCE IN CONNECTION
cnxn = pyodbc.connect(..., autocommit=True)
...
crsr.execute("{CALL myUpdateQuery}")