CODEWITHSUNDEEP
python
Baz
Baz

Reputation: 13135

Database connection wrapper

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

Upvotes: 1

Views: 2094

Answers (1)

varela
varela

Reputation: 1331

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.

Upvotes: 1

Related Questions