pymssql reconnect to db if connection dies

•

What is DaniWeb IT Discussion Community?

You're currently browsing the Python section within the Software Development category of DaniWeb, a massive community of 429,996 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,473 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our Python advertiser: Programming Forums

Thread Tools Display Modes

Views: 365 | Replies: 1

•

Join Date: Jul 2006

Posts: 29

Reputation: msaenz is an unknown quantity at this point

Rep Power: 3

Solved Threads: 0

msaenz

Offline

Light Poster

pymssql reconnect to db if connection dies

May 29th, 2008

Greetings,
I am trying to do a reconnect type of thing for when sqlserver disconnects me i check and reconnect to execute a query. here is some code that i wrote, I really don't know what to use since there is not isconnected method or reconnect method in pymssql. Any ideas or thoughts on how to implement this would be great.

Help with Code Tags

(Toggle Plain Text)

import pymssql
import pdb

#first time connected
dbConn=None
for i in range(4):
   try:
     dbConn = pymssql.connect('', 'user', 'signon', 'sqlserver01:1433')
     print "connected"
     break
   except pymssql.DatabaseError, err:
     print str(err)
     print "You have a connection issue"
     time.sleep(0.2)
   except:
     print "Another Exception occured, most likely User or Signon incorrect"
     time.sleep(0.2)

class SessionStuff():
#some more code
def Cursor():
    for i in range(4):
        try:
           c=dbConn.cursor()
           print "cursor created"
           print dbConn
           return c
           break
        except:
           print "error occured creating cursor, checking connection.."
           time.sleep(0.2)
def findPerson(name):
c=Cursor()
  for i in range(4):
        try:
           c.execute("select person from workoffice where name ='" ,name,"'"))
           result=c.fetchall()
           if len(result)==1:
              return c.fetchall()[0][0]
           else:
              return "%s:%s" % (str(host), str(port))
           break
     except pymssql.DatabaseError, err:
           print str(err)
           print "DB error retrying retrieve session"
           if "None" in str(err):
              print "created new instance of dbconnection old one dead"
              dbConn=pymssql.connect('', 'mtrader', 'mtr8d3r', 'sqldevch02:1433')
              c=dbConn.cursor()
              break
           else:
              time.sleep(0.2)

lets say i have this up and running then the database kills my connection my Cursor method still creates a cursor b/c it still has the object on there, so when it attempts to execute it goes to the except block the error: internal error: None (None) But I want to have like a reconnect attemp somewhere. I tried at the except but I still get the same issue. b/c it doesn't use the new isntance of the cursor c it still uses the old one. Any other posts or forums to direct me will be great. any help is appreciated thank you.