python 在带有sqlite的Python中是否有必要关闭游标？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2330344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
In Python with sqlite is it necessary to close a cursor?
提问by user113946
Here is the scenario. In your function you're executing statements using a cursor, but one of them fails and an exception is thrown. Your program exits out of the function before closing the cursor it was working with. Will the cursor float around taking up space? Do I have to close the cursor?
这是场景。在您的函数中，您使用游标执行语句，但其中一个失败并引发异常。您的程序在关闭它正在使用的游标之前退出该函数。光标会飘来飘去占用空间吗？我必须关闭光标吗？
Additionally, the Python documentation has an example of cursor use and says: "We can also close the cursor if we are done with it." The keyword being "can," not "must." What do they mean precisely by this?
此外，Python 文档有一个使用游标的示例，并说：“如果我们完成了它，我们也可以关闭游标。” 关键字是“可以”，而不是“必须”。他们的意思是什么？
回答by Michael Ekstrand
It's probably a good idea (although it might not matter much with sqlite, don't know there, but it'll make your code more portable). Further, with recent Python (2.5+), it's easy:
这可能是一个好主意（尽管它可能与 sqlite 无关紧要，不知道，但它会使您的代码更具可移植性）。此外，使用最近的 Python (2.5+)，这很容易：
from __future__ import with_statement
from contextlib import closing
with closing(db.cursor()) as cursor:
# do some stuff
回答by Ben James
You're not obliged to call close()on the cursor; it can be garbage collected like any other object.
您没有义务调用close()光标；它可以像任何其他对象一样被垃圾收集。
But even if waiting for garbage collection sounds OK, I think it would be good style still to ensure that a resource such as a database cursor gets closed whether or not there is an exception.
但即使等待垃圾收集听起来不错，我认为确保诸如数据库游标之类的资源无论是否存在异常都被关闭仍然是一种很好的风格。
回答by Simon A. Eugster
I haven't seen any effect for the sqlite3.Cursor.close()operation yet.
我还没有看到sqlite3.Cursor.close()手术有任何效果。
After closing, you can still call fetch(all|one|many)which will return the remaining results from the previous execute statement. Even running Cursor.execute()still works ...
关闭后，您仍然可以调用fetch(all|one|many)which 将返回前一个 execute 语句的剩余结果。即使跑步Cursor.execute()仍然有效......
回答by Craig McQueen
Interestingly, the Python 3.0 docsays "We can also close the cursor if we are done with it", while the Python 2.7and 3.6doc says "We can also close the connectionif we are done with it".
有趣的是，Python 3.0 文档说“如果我们用完它，我们也可以关闭游标”，而Python 2.7和3.6文档说“如果我们用完它，我们也可以关闭连接”。
The Python 2.7 and 3.0-3.4 docs don't describe the cursor .close()method. But the Python 3.5 and 3.6 docs describe the cursor .close()method:
Python 2.7 和 3.0-3.4 文档没有描述游标.close()方法。但是 Python 3.5 和 3.6 文档描述了游标.close()方法：
Close the cursor now (rather than whenever
__del__is called).The cursor will be unusable from this point forward; a
ProgrammingErrorexception will be raised if any operation is attempted with the cursor.
现在关闭游标（而不是每次
__del__调用时）。从现在开始，光标将无法使用；一
ProgrammingError，如果任何操作试图用光标将引发异常。
回答by stackoverflowuser2010
This code will automatically close the Cursor. It will also automatically close and commit the Connection.
此代码将自动关闭Cursor. 它还将自动关闭并提交Connection.
import sqlite3
import contextlib
def execute_statement(statement):
with contextlib.closing(sqlite3.connect(path_to_file)) as conn: # auto-closes
with conn: # auto-commits
with contextlib.closing(conn.cursor()) as cursor: # auto-closes
cursor.execute(statement)
回答by Peer
All,
全部，
I was experiencing a gradual memory leak with my code (Python 3.8) using sqlite3. I traced the likely cause to my database classes. As it turns out, I would open and use a cursor but never closed it. The database remained open during the life of the program (a Windows Service) and would be closed on exit.
我使用 sqlite3 的代码（Python 3.8）逐渐出现内存泄漏。我将可能的原因追溯到我的数据库类。事实证明，我会打开并使用游标，但从未关闭它。数据库在程序（Windows 服务）的生命周期内保持打开状态，并在退出时关闭。
Once I began closing the cursors in all of my db operations which used them, my memory leak stopped, and the memory footprint became steady.
一旦我开始关闭所有使用它们的数据库操作中的游标，我的内存泄漏就停止了，并且内存占用变得稳定。
I would therefore suggest that you take the time to close your cursors. It makes the code more consistent and apparently, helps control memory consumed.
因此，我建议您花时间关闭游标。它使代码更加一致，显然有助于控制消耗的内存。
Here's an example of how I close the cursor:
这是我如何关闭光标的示例：
def write_to_db(self, cache_item:CacheEntry):
'''Write a single cache entry to the database'''
crsr = self._db_con.cursor()
# Load some data elements
fax_line_path = cache_item._dir_part
phone_line = cache_item._phone_line
sub_folder = cache_item._subfolder
fname = cache_item._fname
work_done = cache_item.get_workdone()
try:
crsr.execute(FilenameCacheDB.INSERT_CACHE,
(fax_line_path,
phone_line,
sub_folder,
fname,
work_done))
except Exception as e:
LOG.warning(f"Could not write {cache_item} to db because {e}")
raise e
finally:
#
# I was *not* closing the cursor prior
#
crsr.close()
self._db_con.commit()
回答by user-asterix
Looking at the code snippet and ideas given by stackoverflowuser2010 and Peer, gracefully handling cursors is easier using Python contextmanager.
查看 stackoverflowuser2010 和 Peer 给出的代码片段和想法，使用 Python 上下文管理器更容易优雅地处理游标。
from contextlib import contextmanager
@contextmanager
def OpenCursor(conn):
cursor = conn.cursor()
try:
yield (cursor)
except Exception as e:
cursor.close()
raise e
else:
cursor.close()
Usage without OpenCursor:
没有 OpenCursor 的用法：
def get(conn, key, default=None):
cursor = conn.cursor()
cursor.execute(f'SELECT value FROM table WHERE key=?', (key,))
row = cursor.fetchone()
if row:
return (True)
else:
return (default)
Usage with OpenCursor as context manager:
使用 OpenCursor 作为上下文管理器：
def get(conn, key, default=None):
with OpenCursor(conn) as cursor:
cursor.execute(f'SELECT value FROM table WHERE key=?', (key,))
row = cursor.fetchone()
if row:
return (True)
else:
return (default)
