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

Question

使用 Python 的 sqlite3 module 时关闭游标有什么好处吗？ ？或者它只是 DB API v2.0 的产物那可能只对其他数据库有用？

connection.close() 是有道理的释放资源；然而，尚不清楚 cursor.close() 究竟做了什么，它是释放了一些资源还是什么都不做。它的文档没有启发性:

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

close(...)
    Closes the cursor.

请注意，这是一个与 Why do you need to create a cursor when querying a sqlite database? 完全不同的问题.我知道游标是做什么用的。问题是关于 cursor.close() 方法实际做什么以及调用它是否有任何好处。

Answer 1

分析

CPython _sqlite3.Cursor.close 对应于 pysqlite_cursor_close 除了一些完整性检查并将其标记为已关闭外，does this :

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

pysqlite_statement_reset依次调用sqlite3_reset来自 SQLite 的 C API:

The sqlite3_reset() function is called to reset a prepared statement object back to its initial state, ready to be re-executed. Any SQL statement variables that had values bound to them using the sqlite3_bind_*() API retain their values. Use sqlite3_clear_bindings() to reset the bindings.

[...]

The sqlite3_reset(S) interface does not change the values of any bindings on the prepared statement S.

Prepared Statement Object API用于绑定(bind)参数，例如在 _sqlite3.Cursor.execute 中。因此，如果使用 sqlite3_clear_bindings，它可能已经能够释放一些用于存储参数的内存，但我没有看到它在 CPython/pysqlite 的任何地方被调用。

实验

我使用 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 释放内存(关闭不会)