Python 数据库连接关闭
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3783238/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Python Database connection Close
提问by Merlin
Using the code below leaves me with an open connection, how do I close?
使用下面的代码让我有一个打开的连接，我该如何关闭？
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
采纳答案by unutbu
Connections have a closemethod as specified in PEP-249 (Python Database API Specification v2.0):
连接具有closePEP-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
Since the pyodbcconnectionand cursorare both context managers, nowadays it would be more convenient (and preferable) to write this as:
由于pyodbc连接和游标都是上下文管理器，现在将其写为更方便（也更可取）：
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!!
See https://github.com/mkleehammer/pyodbc/issues/43for an explanation for why conn.close() is not called.
请参阅https://github.com/mkleehammer/pyodbc/issues/43以了解为什么不调用 conn.close() 的解释。
Note that unlike the original code, this causes conn.commit()to be called. Use the outer withstatement to control when you want committo be called.
请注意，与原始代码不同，这会导致conn.commit()被调用。使用外部with语句来控制何时需要commit调用。
Also note that regardless of whether or not you use the withstatements, per the docs,
另请注意，无论您是否使用这些with语句，根据文档，
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.
连接被删除时会自动关闭（通常是当它们超出范围时），因此您通常不需要调用 [
conn.close()]，但如果您愿意，您可以明确关闭连接。
and similarly for cursors(my emphasis):
与游标类似（我的重点）：
Cursors are closed automatically when they are deleted (typically when they go out of scope), so calling [
csr.close()] is not usually necessary.
游标在被删除时会自动关闭（通常是当它们超出范围时），因此
csr.close()通常不需要调用 [ ]。
回答by Matthew Rankin
You might try turning off pooling, which is enabled by default. See thisdiscussion for more information.
您可以尝试关闭默认情况下启用的池。有关更多信息，请参阅此讨论。
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
回答by AndrewF
You can wrap the whole connection in a context manager, like the following:
您可以将整个连接包装在上下文管理器中，如下所示：
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()
Then do something like this where ever you need a database connection:
然后在需要数据库连接的地方执行以下操作：
with open_db_connection("...") as cursor:
# Your code here
The connection will close when you leave the with block. This will also rollback the transaction if an exception occurs or if you didn't open the block using with open_db_connection("...", commit=True).
当您离开 with 块时，连接将关闭。如果发生异常或者您没有使用with open_db_connection("...", commit=True).
回答by hamzed
According to pyodbc documentation, connections to the SQL server are not closed by default.Some database drivers do not close connections when close() is called in order to save round-trips to the server.
根据 pyodbc 文档，默认情况下不会关闭与 SQL 服务器的连接。某些数据库驱动程序在调用 close() 时不会关闭连接，以保存与服务器的往返。
To close your connection when you call close()you should set pooling to False:
要在调用close()时关闭连接，您应该将 pooling 设置为 False：
import pyodbc
pyodbc.pooling = False
回答by Betran Jacob
You can define a DB class as below. Also, as andrewfsuggested, use a context manager for cursor access.I'd define it as a member function. This way it keeps the connection open across multiple transactions from the app code and saves unnecessary reconnections to the server.
您可以定义一个数据库类，如下所示。此外，正如andrewf建议的那样，使用上下文管理器进行游标访问。我将其定义为成员函数。通过这种方式，它可以通过应用程序代码在多个事务之间保持连接打开，并避免不必要的重新连接到服务器。
import pyodbc
class MS_DB():
""" Collection of helper methods to query the MS SQL Server database.
"""
def __init__(self, username, password, host, port=1433, initial_db='dev_db'):
self.username = username
self._password = password
self.host = host
self.port = str(port)
self.db = initial_db
conn_str = 'DRIVER=DRIVER=ODBC Driver 13 for SQL Server;SERVER='+ \
self.host + ';PORT='+ self.port +';DATABASE='+ \
self.db +';UID='+ self.username +';PWD='+ \
self._password +';'
print('Connected to DB:', conn_str)
self._connection = pyodbc.connect(conn_str)
pyodbc.pooling = False
def __repr__(self):
return f"MS-SQLServer('{self.username}', <password hidden>, '{self.host}', '{self.port}', '{self.db}')"
def __str__(self):
return f"MS-SQLServer Module for STP on {self.host}"
def __del__(self):
self._connection.close()
print("Connection closed.")
@contextmanager
def cursor(self, commit: bool = False):
"""
A context manager style of using a DB cursor for database operations.
This function should be used for any database queries or operations that
need to be done.
:param commit:
A boolean value that says whether to commit any database changes to the database. Defaults to False.
:type commit: bool
"""
cursor = self._connection.cursor()
try:
yield cursor
except pyodbc.DatabaseError as err:
print("DatabaseError {} ".format(err))
cursor.rollback()
raise err
else:
if commit:
cursor.commit()
finally:
cursor.close()
ms_db = MS_DB(username='my_user', password='my_secret', host='hostname')
with ms_db.cursor() as cursor:
cursor.execute("SELECT @@version;")
print(cur.fetchall())
