Database connection wrapper

Question

I have written the following class to make life easier for me:

import pymssql
class DatabaseConnection:
 def __init__(self):
 self.connection = pymssql.connect(host='...', user='...', password='...', database='...', as_dict=True)
 def select(self, statement, arguments={}):
 cur = self.connection.cursor()
 cur.execute(statement, arguments)
 return list(cur)
 def __enter__(self):
 return self
 def __exit__(self, type, value, traceback):
 if self.connection:
 self.connection.close() 

I use it like this:

<script language = "Python" runat="server">
 # get all that data we need to present on this asp page
 with database.DatabaseConnection() as connection:
 orders = connection.select('select * from ordersview')
 special_orders = connection.select('select * from ordersview where paymenttype = %(paymentType)s', {'paymentType': 'Card'})
</script>
<script language = "Python" runat="server">
 # later on lets use that data
 for row in special_orders:
 ...
</script>

I intend on having a connection host class later to manage the database hosts I wish to connect to but for now I hard code this.

Have I done anything here which is not recommended or unsafe in your opinion? Is it a reasonable design? Is it ok to return list(cur) since the iterator will be out of scope otherwise, outside the with scope?

Thanks for your help,

Barry

Answer 1

I think you'd better use static database.DatabaseConnection.getInstance() instead of custruction of new object. This will be more flexible in future. You will be able to use this static method as factory, singleton or connection pool manager which will fit your needs.