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

- Merlin

Answer 1

根据PEP-249（Python数据库API规范v2.0），连接具有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()
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语句，根据文档，

连接在被删除时（通常是当它们超出范围时）会自动关闭，因此通常不需要调用[conn.close()]，但如果您愿意，可以明确地关闭连接。

同样对于游标（我强调）：

当游标被删除时（通常是当它们超出范围时），游标会自动关闭，因此通常不需要调用[csr.close()]。

- unutbu

---

这是最佳实践吗？手动关闭游标，然后删除它，最后关闭连接？ - Dustin Michels

根据文档所述，“连接在被删除时（通常是超出范围时）会自动关闭，因此您通常不需要调用此函数，但如果需要，您可以显式地关闭连接”。对于游标也是如此。 - unutbu

@TuanDT：这里不需要使用del。它确实会关闭游标，但通常情况下并不必要，因为当csr超出作用域时，同样的操作会自动发生（而且通常很快）。只有在想要从命名空间中删除csr和/或将对该对象的引用减少一个时才使用它。当我第一次发布这篇文章时，我保留了del，因为它并不一定是错误的，而且我认为OP理解了它的目的。但由于它似乎引起了混淆，并且不需要关闭连接，所以现在我将其删除。 - unutbu

使用上下文管理器语法时，pyodbc对象不会自动关闭！！正如您链接的文档所指出的那样，使用上下文管理器语法相当于提交但不关闭连接或光标。请参见https://github.com/mkleehammer/pyodbc/issues/43 - johnDanger

@johnDanger 你所说的上下文管理器，是指 with 语句吗？ - NoName

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)打开该块，这也会回滚事务。

- AndrewF

---

好主意，但我使用Mysql和Sqlite……不是Oracle（嗯，不是直接的:-)！）连接字符串到驱动程序在哪里……为什么要导入sys？ - Merlin

哎呀，我以为我已经用 pyodbc 替换掉了我的 Oracle 特定代码，但我忽略了一个（现在已修复）。由于两者都使用常见的 PEP 249 数据库 API，因此语法相同。我导入了 sys，这样我就可以将任何异常写入标准错误中了。您还可以使用日志记录或普通的打印语句。并且，您可以将与之前相同的连接字符串传递给 open_db_connection()。 - AndrewF

好的，看一下代码：我这样做能得到什么？似乎有很多额外的代码行来检查连接是否打开？ - Merlin

这正是我一直在寻找的，现在我已经成功地使用MySQLdb运行了它，但你需要将import contextlib更改为from contextlib import contextmanager或者将你的装饰器更改为@contextlib.contextmanager。两种方法都可以，但我更喜欢前者。 - jedmao

很棒的方法，我一直在寻找这样的东西。但是我该如何将它放入一个类中呢？目前我的代码将pyodbc连接作为类的一个字段：self.db = pyodbc.connect(path)，现在当我想要覆盖它到你的方式：self.db = open_db_connection("...") 或者其他任何方式时，我的连接在初始化后立即关闭了 pyodbc.ProgrammingError: The cursor's connection has been closed. - （而我希望在整个运行时都保持连接）。 - Intelligent-Infrastructure

Answer 3

您可以尝试关闭连接池，它默认是开启的。有关更多信息，请参见此讨论。

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

- Matthew Rankin

---

有趣的是我可以使用连接池，但是MySQL会使用新的ID启动另一个连接。 - Merlin

Answer 4

您可以按照以下方式定义一个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()
        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())

- Betran Jacob

---

我认为最好的解决方案在这个链接中：https://dev59.com/ZloT5IYBdhLWcg3w4CiO#38078544。 - Tomy

Answer 5

根据pyodbc文档，连接SQL服务器默认情况下不会关闭。一些数据库驱动程序在调用close()时不会关闭连接以节省与服务器的往返次数。

当您调用close()时要关闭连接，您应该将连接池设置为False:

import pyodbc

pyodbc.pooling = False

- hamzed

---

当你说“call close()”时，你是指cursor.close()吗？我认为是这样的，因为connection.close()会避免设置池为false的需要。 - Sean McCarthy

Answer 6

如果语言没有像 .NET 中的 Using 这样的自关闭结构，则处理连接的最常见方法是使用 try -> finally 来关闭对象。虽然 pyodbc 可能具有某种形式的自动关闭，但为了保险起见，我还是会使用以下代码：

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

    cursor = conn.cursor()  

    # ... do stuff ...

finally:
    try: cursor.close()
    except: pass
    try: conn.close()
    except: pass

- Timothy C. Quinn