为什么在Python中关闭Sqlite3的游标

使用Python时关闭游标有什么好处sqlite3模块 https://docs.python.org/2.7/library/sqlite3.html#module-sqlite3？或者它只是一个人工制品数据库API v2.0 https://www.python.org/dev/peps/pep-0249/这可能只会对其他数据库有用？

这是有道理的连接.close() https://docs.python.org/2.7/library/sqlite3.html#sqlite3.Connection.close释放资源；然而，目前尚不清楚是什么光标.close()实际上确实如此，无论它实际上释放了一些资源还是什么都不做。它的文档没有启发性：

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()
>>> help(c.close)
Help on built-in function close:

close(...)
    Closes the cursor.

请注意，这是一个完全不同的问题为什么查询sqlite数据库时需要创建游标？ https://stackoverflow.com/questions/6318126/why-do-you-need-to-create-a-cursor-when-querying-a-sqlite-database。我知道光标有什么用。问题是关于什么cursor.close()方法实际上是这样做的，以及调用它是否有任何好处。

Analysis

CPython _sqlite3.Cursor.close对应于pysqlite_cursor_close除了一些健全性检查并将其标记为已关闭之外，做这个 https://github.com/python/cpython/blob/3ad101b3/Modules/_sqlite/cursor.c#L970:

if (self->statement) {
    (void)pysqlite_statement_reset(self->statement);
    Py_CLEAR(self->statement);
}

pysqlite_statement_reset https://github.com/python/cpython/blob/3ad101b3/Modules/_sqlite/statement.c#L347依次调用sqlite3_reset https://sqlite.org/c3ref/reset.html来自 SQLite 的 C API：

调用 sqlite3_reset() 函数将准备好的语句对象重置回其初始状态，准备重新执行。任何使用 sqlite3_bind_*() API 绑定值的 SQL 语句变量都会保留其值。使用 sqlite3_clear_bindings() 重置绑定。

[...]

sqlite3_reset(S) 接口不会更改准备好的语句 S 上的任何绑定的值。

准备好的语句对象 https://sqlite.org/c3ref/stmt.htmlAPI用于绑定参数，例如在_sqlite3.Cursor.execute. So if sqlite3_clear_bindings使用时，它可能能够释放一些用于存储参数的内存，但我没有看到它在 CPython/pysqlite 中的任何地方被调用。

实验

I use 内存分析器 https://pypi.org/project/memory-profiler/绘制内存使用图表并生成逐行报告。

import logging
import sqlite3
import time

# For the function brackets to appear on the chart leave this out:
#
#     If your Python file imports the memory profiler 
#     "from memory_profiler import profile" these timestamps will not be
#     recorded. Comment out the import, leave your functions decorated, 
#     and re-run.
#
# from memory_profiler import profile


class CursorCuriosity:
  
    cursor_num = 20_000
    param_num = 200
    
    def __init__(self):
        self.conn = sqlite3.connect(':memory:')
        self.cursors = []
    
    @profile
    def create(self):
        logging.info('Creating cursors')
        sql = 'SELECT {}'.format(','.join(['?'] * self.param_num))
        for i in range(self.cursor_num):
            params = [i] * self.param_num
            cur = self.conn.execute(sql, params)
            self.cursors.append(cur)
    
    @profile
    def close(self):
        logging.info('Closing cursors')
        for cur in self.cursors:
            cur.close()

    @profile
    def delete(self):
        logging.info('Destructing cursors')
        self.cursors.clear()
    
    @profile    
    def disconnect(self):
        logging.info('Disconnecting')
        self.conn.close()
        del self.conn


@profile
def main():
    curcur = CursorCuriosity()
    
    logging.info('Sleeping before calling create()')
    time.sleep(2)
    curcur.create()
    
    logging.info('Sleeping before calling close()')
    time.sleep(2)
    curcur.close()
    
    logging.info('Sleeping before calling delete()')
    time.sleep(2)
    curcur.delete()
    
    logging.info('Sleeping before calling disconnect()')
    time.sleep(2)
    curcur.disconnect()
    
    logging.info('Sleeping before exit')
    time.sleep(2)  


if __name__ == '__main__':
    logging.basicConfig(level='INFO', format='%(asctime)s %(message)s')
    main()

我首先运行它profile导入注释掉以获得情节。

mprof run -T 0.05 cursor_overhead.py
mprof plot

然后通过导入在终端中获取输出。

mprof run -T 0.05 cursor_overhead.py

Line #    Mem usage    Increment  Occurences   Line Contents
============================================================
    51     19.1 MiB     19.1 MiB           1   @profile
    52                                         def main():
    53     19.1 MiB      0.0 MiB           1       curcur = CursorCuriosity()
    54                                             
    55     19.1 MiB      0.0 MiB           1       logging.info('Sleeping before calling create()')
    56     19.1 MiB      0.0 MiB           1       time.sleep(2)
    57   2410.3 MiB   2391.2 MiB           1       curcur.create()
    58                                             
    59   2410.3 MiB      0.0 MiB           1       logging.info('Sleeping before calling close()')
    60   2410.3 MiB      0.0 MiB           1       time.sleep(2)
    61   2410.3 MiB      0.0 MiB           1       curcur.close()
    62                                             
    63   2410.3 MiB      0.0 MiB           1       logging.info('Sleeping before calling delete()')
    64   2410.3 MiB      0.0 MiB           1       time.sleep(2)
    65   1972.2 MiB   -438.1 MiB           1       curcur.delete()
    66                                             
    67   1972.2 MiB      0.0 MiB           1       logging.info('Sleeping before calling disconnect()')
    68   1972.2 MiB      0.0 MiB           1       time.sleep(2)
    69   1872.7 MiB    -99.5 MiB           1       curcur.disconnect()
    70                                             
    71   1872.7 MiB      0.0 MiB           1       logging.info('Sleeping before exit')
    72   1872.7 MiB      0.0 MiB           1       time.sleep(2) 

以及个人方法的完整性。

Line #    Mem usage    Increment  Occurences   Line Contents
============================================================
    24     19.1 MiB     19.1 MiB           1       @profile
    25                                             def create(self):
    26     19.1 MiB      0.0 MiB           1           logging.info('Creating cursors')
    27     19.1 MiB      0.0 MiB           1           sql = 'SELECT {}'.format(','.join(['?'] * self.param_num))
    28   2410.3 MiB      0.0 MiB       20001           for i in range(self.cursor_num):
    29   2410.1 MiB      0.0 MiB       20000               params = [i] * self.param_num
    30   2410.3 MiB   2374.3 MiB       20000               cur = self.conn.execute(sql, params)
    31   2410.3 MiB     16.9 MiB       20000               self.cursors.append(cur)

Line #    Mem usage    Increment  Occurences   Line Contents
============================================================
    33   2410.3 MiB   2410.3 MiB           1       @profile
    34                                             def close(self):
    35   2410.3 MiB      0.0 MiB           1           logging.info('Closing cursors')
    36   2410.3 MiB      0.0 MiB       20001           for cur in self.cursors:
    37   2410.3 MiB      0.0 MiB       20000               cur.close()

Line #    Mem usage    Increment  Occurences   Line Contents
============================================================
    39   2410.3 MiB   2410.3 MiB           1       @profile
    40                                             def delete(self):
    41   2410.3 MiB      0.0 MiB           1           logging.info('Destructing cursors')
    42   1972.2 MiB   -438.1 MiB           1           self.cursors.clear()

Line #    Mem usage    Increment  Occurences   Line Contents
============================================================
    44   1972.2 MiB   1972.2 MiB           1       @profile    
    45                                             def disconnect(self):
    46   1972.2 MiB      0.0 MiB           1           logging.info('Disconnecting')
    47   1972.2 MiB      0.0 MiB           1           self.conn.close()
    48   1872.7 MiB    -99.5 MiB           1           del self.conn

结论

1. 关闭一个sqlite3.Cursor不释放内存（但做了一些工作，操纵 SQLite 准备好的语句的状态）
2. 删除/销毁游标可以释放内存
3. 删除/销毁sqlite3.Connection释放内存（关闭则不会）