整个python应用程序中的单个数据库连接（遵循单例模式） [英] single database connection throughout the python application (following singleton pattern)

问题描述

我的问题是在整个应用程序中维护单个数据库连接的最佳方法是什么？ 使用Singleton模式？

需要注意的条件：

1. 如果有多个请求，我应该使用相同的连接


2. 如果连接已关闭，请创建一个新连接


3. 如果连接已超时，应新请求，我的代码应创建一个新连接。

---

Django ORM不支持我的数据库的驱动程序。并且由于相同的驱动程序相关问题，我正在使用 pyodbc 连接到数据库。现在，我在下面的类中创建和管理数据库连接：

---

The driver to my Database is not supported by the Django ORM. And due to same driver related issues, I am using pyodbc to connect to the database. Right now I am having below class for creating and managing the DB connections:

class DBConnection(object):
    def __init__(self, driver, serve,
                 database, user, password):

        self.driver = driver
        self.server = server
        self.database = database
        self.user = user
        self.password = password

    def __enter__(self):
        self.dbconn = pyodbc.connect("DRIVER={};".format(self.driver) +\
                                     "SERVER={};".format(self.server) +\
                                     "DATABASE={};".format(self.database) +\
                                     "UID={};".format(self.user) +\
                                     "PWD={};".format(self.password) + \
                                     "CHARSET=UTF8",
                                     # "",
                                     ansi=True)

        return self.dbconn

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.dbconn.close()

但是这种方法的问题在于它将为每个查询创建新的数据库连接。遵循单一模式的更好方法是什么？如果连接关闭，我可以想到的方式将保留对连接的引用。像这样的东西：

But the issue with this approach is that it will create new database connection for each query. What will be the better way to do it following singleton pattern? The way I can think of will hold the reference to the connection if the connection is closed. Something like:

 def get_database_connection():
     conn = DBConnection.connection
     if not conn:
          conn = DBConnection.connection = DBConnection.create_connection()
     return conn

实现此目标的最佳方法是什么？有任何建议/想法/示例吗？

PS：我正在检查使用 weakref 允许创建对对象的弱引用。我认为使用 weakref 和单例模式来存储连接变量将是一个好主意。这样，当不使用数据库时，我不必保持连接处于活动状态。你们怎么说？

PS: I was checking about using weakref which allows to create weak references to objects. I think it will be good idea to use weakref along with singleton pattern for storing the connection variable. This way I won't have to keep the connection alive when DB is not in use. What you guys say about this?

推荐答案

目前，我将继续使用单例类方法。任何人都在其中看到潜在的缺陷，请提一下它们：）

For now, I am going ahead with the singleton class approach. Anyone seeing the potential flaws in this, feel to mention them :)

DBConnector 类 >创建连接

DBConnector class for creating a connection

class DBConnector(object):

   def __init__(self, driver, server, database, user, password):

        self.driver = driver
        self.server = server
        self.database = database
        self.user = user
        self.password = password
        self.dbconn = None

    # creats new connection
    def create_connection(self):
        return pyodbc.connect("DRIVER={};".format(self.driver) + \
                              "SERVER={};".format(self.server) + \
                              "DATABASE={};".format(self.database) + \
                              "UID={};".format(self.user) + \
                              "PWD={};".format(self.password) + \
                              "CHARSET=UTF8",
                              ansi=True)

    # For explicitly opening database connection
    def __enter__(self):
        self.dbconn = self.create_connection()
        return self.dbconn

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.dbconn.close()

DBConnection 类用于管理连接

DBConnection class for managing the connections

class DBConnection(object):
    connection = None

    @classmethod
    def get_connection(cls, new=False):
        """Creates return new Singleton database connection"""
        if new or not cls.connection:
            cls.connection = DBConnector().create_connection()
        return cls.connection

    @classmethod
    def execute_query(cls, query):
        """execute query on singleton db connection"""
        connection = cls.get_connection()
        try:
            cursor = connection.cursor()
        except pyodbc.ProgrammingError:
            connection = cls.get_connection(new=True)  # Create new connection
            cursor = connection.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        return result

这篇关于整个python应用程序中的单个数据库连接（遵循单例模式）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！