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
6 个回答
0 人赞同
连接有一个close方法，如PEP-249 (Python Database API Specification 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 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!!
See https://github.com/mkleehammer/pyodbc/issues/43来解释为什么不调用conn.close()。
注意，与原始代码不同，这将导致conn.commit()被调用。使用外层的with语句来控制你希望commit被调用的时间。
还要注意，无论你是否使用with语句，每the docs,
当连接被删除时（通常是当它们超出范围时）会自动关闭，所以你通常不需要调用conn.close()，但如果你愿意，你可以显式关闭连接。
和类似的for cursors(我的强调)。
游标在被删除后会自动关闭（通常是当它们超出范围时）。所以通常不需要调用csr.close()。.
0 人赞同
你可以将整个连接包裹在一个上下文管理器中，如下所示。
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()
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)打开块，这也将回滚交易。
0 人赞同
你可以尝试关闭池子，它在默认情况下是启用的。见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
0 人赞同
你可以像下面这样定义一个DB类。另外，如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()
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())
0 人赞同
根据 pyodbc 文档，与 SQL 服务器的连接默认情况下不会关闭。一些数据库驱动程序在调用close()时不关闭连接，以节省到服务器的往返次数。
当你打电话时，要关闭你的连接close()你应该把池子设置为False。
import pyodbc
pyodbc.pooling = False
0 人赞同
The most common way to handle connections, if the language does not have a self closing construct like Using in .NET, then you should use a try -> finally to close the objects. Its possible that pyodbc does have some form of automatic closing but here is the code I do just in case:
conn = cursor = None
conn = pyodbc.connect('DRIVER=MySQL ODBC 5.1 driver;SERVER=localhost;DATABASE=spt;UID=who;PWD=testest')
cursor = conn.cursor()
# ... do stuff ...
finally:
