Python 数据库连接 关闭

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

连接有 close 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

---

自从 pyodbc connection和 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 `with` statement!! 

见 https://github.com/mkleehammer/pyodbc/issues/43关于为什么不调用 conn.close() 的解释。

请注意，与原始代码不同，这会导致调用 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.