python单例模式数据库连接失败_整个python应用程序中的单个数据库连接（遵循单例模式）...

My Question is what is the best way to maintain the single database connection in the entire application? Using Singleton Pattern? How?

Conditions that are needed to be taken care of:

In case of multiple requests, I should be using the same connection

In case connection is closed, create a new connection

If the connection has timed-out, on new request my code should create a new connection.

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

What will be the best way to achieve this? Any suggestion/ideas/examples?

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 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 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