Python数据库连接Clos

Question

使用下面的代码会留下一个打开的连接，如何关闭？

import pyodbc
conn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') 

csr = conn.cursor()  
csr.close()
del csr

Answer 1

连接具有PEP-249（Python数据库API规范v2.0）中指定的^{}方法：

import pyodbc
conn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') 

csr = conn.cursor()  
csr.close()
conn.close()     #<--- Close the connection

---

由于pyodbcconnection和cursor都是上下文管理器，现在将其编写为：

import pyodbc
conn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') 
with conn:
    crs = conn.cursor()
    do_stuff
    # conn.commit() will automatically be called when Python leaves the outer `with` statement
    # Neither crs.close() nor conn.close() will be called upon leaving the the `with` statement!! 

有关为何不调用conn.close（）的解释，请参见https://github.com/mkleehammer/pyodbc/issues/43。

注意，与原始代码不同，这会导致调用conn.commit()。使用外部with语句控制何时要调用commit。

---

还要注意，无论是否使用with语句，每个the docs

Connections are automatically closed when they are deleted (typically when they go out of scope) so you should not normally need to call [conn.close()], but you can explicitly close the connection if you wish.

同样地for cursors（我的重点是）：

Cursors are closed automatically when they are deleted (typically when they go out of scope), so calling [csr.close()] is not usually necessary.

Answer 2

您可以将整个连接包装在上下文管理器中，如下所示：

from contextlib import contextmanager
import pyodbc
import sys

@contextmanager
def open_db_connection(connection_string, commit=False):
    connection = pyodbc.connect(connection_string)
    cursor = connection.cursor()
    try:
        yield cursor
    except pyodbc.DatabaseError as err:
        error, = err.args
        sys.stderr.write(error.message)
        cursor.execute("ROLLBACK")
        raise err
    else:
        if commit:
            cursor.execute("COMMIT")
        else:
            cursor.execute("ROLLBACK")
    finally:
        connection.close()

然后在需要数据库连接时执行以下操作：

with open_db_connection("...") as cursor:
    # Your code here

当您离开with块时，连接将关闭。如果发生异常或未使用with open_db_connection("...", commit=True)打开块，则此操作也将回滚事务。

Answer 3

您可以尝试关闭池，这是默认启用的。有关详细信息，请参见this讨论。

import pyodbc
pyodbc.pooling = False
conn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest') 

csr = conn.cursor()  
csr.close()
del csr