Database objects? Persistence? Sql Server woes

[Steve Holden]

Mike Howarth wrote:
> I've been having a few problems with connecting to SQL Server, initially I
> was using dblib however found some problems with returning text fields
> whereby all text fields were ignored and it bawked at multiline sql
> statements.
> 
> Having found these major stumbling blocks I've started using pymssql which
> seems less flaky. One problem I've stumbled across is that I seem to reach
> my max connections  on the database fairly easily (given how my script was
> written) and therefore have used the singleton pattern on my database
> object.
> 
> Great problem solved, or so I thought.
> 
> Using the following code I'm finding that the Set is now also being enforced
> as a singleton as well.
> 
> class Set(Database,Product):
>     
>      def __init__(self, *args, **kw): 
>          
>          Database.__init__(self)
>          Product.__init__(self)
> 
>      def dosomething(self):
>          cu = self.c.cursor()
> 
> Having sat back and thought about it, its easy to understand why this is
> occurring given I'm indicating that Set belongs to Database and therefore
> this is a singleton as well.
> 
> Being relatively new to Python I'm unsure on how to then go about using a
> database object between each class. Can anyone advise me on how they
> approach this, and whether there is a common approach to this?
> 
If you want to share a database object between (instances of) several 
classes there are two easy ways. The absolute easiest is to create a 
database connection that's global to the module. References to the 
connection inside you other objects will then reference the global 
database connection. e.g.:

conn = pymssql.connect(...)

class Set:

   ...

   def dosomething(self):
     self.cu = conn.cursor()

The other way is to pass the connection in to the object's __init__() 
method and save it as an instance variable so it can be used inside the 
instance's methods. e.g.:

conn = pymssql.connect(...)

class Set:

   def __init__(self, conn, ...):
     self.conn = conn
     ...

   def dosomething(self):
     self.cu = self.conn.cursor()

regards
  Steve
-- 
Steve Holden        +1 571 484 6266   +1 800 494 3119
Holden Web LLC/Ltd           http://www.holdenweb.com
Skype: holdenweb      http://del.icio.us/steve.holden
--------------- Asciimercial ------------------
Get on the web: Blog, lens and tag the Internet
Many services currently offer free registration
----------- Thank You for Reading -------------