利用 pyodbc 管理内存使用

在使用 pyodbc (3.0.7) 从 SQL Server 数据库检索数据时，您可能会遇到内存使用问题。随着程序的运行，sqlservr.exe 进程的内存使用量会不断增加。以下是导致此问题的代码示例：

import pyodbc

# 连接到数据库
cnxnstring = "DRIVER={SQL Server};Server=servername;Database=dbname; \
                        User Id=uid; Password=pwd;"
cnxn = pyodbc.connect(cnxnstring)
dbcursor = cnxn.cursor()

# 从数据库中检索数据并存储在字典中
strsql = 'SELECT CELLID,MEAN FROM TABLE_1'
dbcursor.execute(strsql)
dict1 = {}
for line in dbcursor:
    dict1.update({line.CELLID: line.MEAN})

# 对字典进行某些操作 - 此处的代码与问题无关

# 清理工作
dbcursor.close()
cnxn.close()

即使您关闭了与 SQL Server 数据库的连接，Windows 也不会释放相关的内存。内存使用量会随着时间的推移不断增加，最终迫使您重启计算机。

解决方案

要解决此问题，可以使用以下方法：

1. 使用游标逐行获取数据。 这种方法可以减少一次加载的数据量，从而减少内存使用。可以使用 pyodbc 的 fetchmany() 方法来实现。

dbcursor.execute(strsql)
while True:
    rows = dbcursor.fetchmany(100)
    if not rows:
        break
    for line in rows:
        dict1.update({line.CELLID: line.MEAN})

2. 使用存储过程。 存储过程可以将查询结果存储在临时表中，从而减少内存使用。

dbcursor.execute("CREATE PROCEDURE GetTableData AS
    SELECT CELLID, MEAN FROM TABLE_1")
dbcursor.execute("EXEC GetTableData")
while True:
    rows = dbcursor.fetchmany(100)
    if not rows:
        break
    for line in rows:
        dict1.update({line.CELLID: line.MEAN})

3. 使用 SQL Server 的批处理功能。 批处理功能可以将多个查询合并成一个查询，从而减少与数据库的交互次数。

dbcursor.execute("BEGIN TRANSACTION")
dbcursor.execute("SELECT CELLID, MEAN FROM TABLE_1")
dbcursor.execute("SELECT CELLID, MEAN FROM TABLE_2")
dbcursor.execute("SELECT CELLID, MEAN FROM TABLE_3")
dbcursor.execute("COMMIT TRANSACTION")
while True:
    rows = dbcursor.fetchmany(100)
    if not rows:
        break
    for line in rows:
        dict1.update({line.CELLID: line.MEAN})

4. 使用连接池。 连接池可以减少创建和销毁数据库连接的次数，从而减少内存使用。

import pyodbc
from pymssql import connect

# 创建连接池
pool = connect(cnxnstring)

# 从连接池中获取连接
with pool.acquire() as conn:
    with conn.cursor() as cursor:
        # 执行查询
        cursor.execute("SELECT CELLID, MEAN FROM TABLE_1")

        # 逐行获取数据
        while True:
            rows = cursor.fetchmany(100)
            if not rows:
                break
            for line in rows:
                dict1.update({line.CELLID: line.MEAN})

5. 使用内存优化的表。 内存优化的表可以将数据存储在内存中，从而提高性能和减少内存使用。

CREATE TABLE MyTable (
    CELLID INT NOT NULL,
    MEAN FLOAT NOT NULL
)
WITH (MEMORY_OPTIMIZED = ON);

6. 使用临时表。 临时表可以存储临时数据，在不需要时可以删除。

CREATE TABLE #MyTable (
    CELLID INT NOT NULL,
    MEAN FLOAT NOT NULL
);
INSERT INTO #MyTable (CELLID, MEAN)
SELECT CELLID, MEAN
FROM TABLE_1;
SELECT * FROM #MyTable;
DROP TABLE #MyTable;

7. 使用视图。 视图可以将多个表的数据合并成一个表，从而减少内存使用。

CREATE VIEW MyView AS
SELECT CELLID, MEAN
FROM TABLE_1
UNION
SELECT CELLID, MEAN
FROM TABLE_2;
SELECT * FROM MyView;