Changeset 31470 for trunk/ippToPsps/jython/mysql.py

Timestamp:

May 6, 2011, 12:43:46 PM (15 years ago)

Author:

rhenders

Message:

added methods to execute SQL queries so that stmt objects can be closed neatly; added methods to get and kill last connection to database

File:

• trunk/ippToPsps/jython/mysql.py (modified) (14 diffs)

trunk/ippToPsps/jython/mysql.py

@@ -37 +37 @@
         self.url = "jdbc:mysql://"+self.dbHost+"/"+self.dbName+"?user="+self.dbUser+"&password="+self.dbPass
         self.con = DriverManager.getConnection(self.url)
-        self.stmt = self.con.createStatement()
+        self.connectionID = self.getLastConnectionID()
+        self.logger.info("MySQL connection to %s with ID %d" % (dbType, self.connectionID))
+
+        #self.stmt = self.con.createStatement()
+
+
+    '''
+    Disconnect from database
+    '''
+    def disconnect(self):
+        self.con.close()
 
     '''
@@ -45 +55 @@
 
         self.logger.debug("MySql destructor")
-        self.stmt.close()
-        self.con.close()
+        self.disconnect()
+
+    '''
+    Kills the last connection ID, so long as it's not THIS connection ID
+    '''
+    def killLastConnectionID(self):
+   
+        connectionID = self.getLastConnectionID()
+        if connectionID == self.connectionID:
+            self.logger.error("NOT going to kill THIS connection ID")
+            return
+
+        sql = "KILL %d" % connectionID
+        self.execute(sql)
+
+    '''
+    Gets the last connection ID
+    '''
+    def getLastConnectionID(self):
+
+        sql = "SELECT ID \
+               FROM INFORMATION_SCHEMA.PROCESSLIST \
+               WHERE DB='" + self.dbName + "' \
+               ORDER BY ID"
+        rs = self.executeQuery(sql)
+        rs.last()
+        return rs.getInt(1)
 
     '''
@@ -54 +89 @@
 
         sql = "UPDATE " + table + " SET " + column + " = " + value
-        self.stmt.execute(sql)
+        self.execute(sql)
 
     '''
@@ -62 +97 @@
 
         sql = "DROP TABLE " + table
-        try: self.stmt.execute(sql)
+        try: self.execute(sql)
         except: return
 
@@ -74 +109 @@
         sql = "ALTER TABLE " + table + " ADD UNIQUE (" + column + ")"
         try:
-            self.stmt.execute(sql)
+            self.execute(sql)
         except: pass
             #self.logger.warn("Index already in place on '" + column + "' for table '" + table + "'")
@@ -82 +117 @@
     def createIndex(self, table, column):
 
-        self.logger.debug("Creating index on column '"+column+"' for table '"+table+"'")
+        #self.logger.debug("Creating index on column '"+column+"' for table '"+table+"'")
 
         sql = "CREATE INDEX "+table+"_"+column+"_index ON "+table+" ("+column+")"
         try:
-            self.stmt.execute(sql)
+            self.execute(sql)
         except: pass
             #self.logger.warn("Index already in place on '" + column + "' for table '" + table + "'")
+    '''
+    TODO
+    '''
+    def execute(self, sql):
+
+        stmt = self.con.createStatement()
+        stmt.execute(sql)
+        stmt.close()
+
+    '''
+    TODO
+    '''
+    def executeQuery(self, sql):
+
+        stmt = self.con.createStatement()
+        rs = stmt.executeQuery(sql)
+        #stmt.close()
+        return rs
 
     '''
@@ -96 +149 @@
 
        sql = "SHOW COLUMNS FROM " + tableName
-       rs = self.stmt.executeQuery(sql)
+       rs = self.executeQuery(sql)
        columns = []
        while (rs.next()): columns.append(rs.getString(1))
@@ -109 +162 @@
 
       sql = "UPDATE " + tableName + " SET " + column + " = " + sub + " WHERE " + column + " IS NULL"
-      self.stmt.execute(sql)
+      self.execute(sql)
 
     '''
@@ -123 +176 @@
           
           sql = "UPDATE " + tableName + " SET " + column + " = " + sub + " WHERE " + column + " IS NULL"
-          self.stmt.execute(sql)
+          self.execute(sql)
 
     '''
@@ -131 +184 @@
 
         sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + columnName + " = " + value
-        rs = self.stmt.executeQuery(sql)
+        rs = self.executeQuery(sql)
         rs.first()
-        nBadFlux = rs.getInt(1)
-        self.logger.info("%d NULL %s values in table %s. Deleting." % (nBadFlux, columnName, tableName))
+        nBad = rs.getInt(1)
+        self.logger.info("%5d NULL %s values in table %s. Deleting." % (nBad, columnName, tableName))
 
         sql="DELETE from " + tableName + " WHERE " + columnName + " = " + value
-        self.stmt.execute(sql)
+        self.execute(sql)
 
     '''
@@ -145 +198 @@
 
         sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + columnName + " IS NULL"
-        rs = self.stmt.executeQuery(sql)
+        rs = self.executeQuery(sql)
         rs.first()
-        nBadFlux = rs.getInt(1)
-        self.logger.info("%d NULL %s values in table %s. Deleting." % (nBadFlux, columnName, tableName))
+        nBad = rs.getInt(1)
+        self.logger.info("%5d NULL %s values in table %s. Deleting." % (nBad, columnName, tableName))
 
         sql="DELETE from " + tableName + " WHERE " + columnName + " IS NULL"
-        self.stmt.execute(sql)
+        self.execute(sql)
 
     '''
@@ -160 +213 @@
        # first, count rows
        sql = "SELECT COUNT(*) FROM " + tableName
-       rs = self.stmt.executeQuery(sql)
+       rs = self.executeQuery(sql)
        rs.first()
        numRows = rs.getInt(1)
@@ -175 +228 @@
           
           sql = "SELECT COUNT(*) FROM " + tableName + " WHERE " + column + " IS NULL"
-          rs = self.stmt.executeQuery(sql)
+          rs = self.executeQuery(sql)
           rs.first()
           if rs.getInt(1) == numRows:
@@ -191 +244 @@
         sql = "SELECT COUNT(*) FROM " + table
         try:
-            rs = self.stmt.executeQuery(sql)  
+            rs = self.executeQuery(sql)  
             rs.first()
             return rs.getInt(1)