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

Timestamp:

Apr 3, 2012, 4:40:01 PM (14 years ago)

Author:

rhenders

Message:

added methid to add a column to any table that represents a row count

File:

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

trunk/ippToPsps/jython/mysql.py

@@ -35 +35 @@
 
         # set up JDBC connection
-        self.url = "jdbc:mysql://"+self.dbHost+"/"+self.dbName+"?user="+self.dbUser+"&password="+self.dbPass
+        self.url = "jdbc:mysql://"+self.dbHost+"/"+self.dbName+"?autoReconnect=true&user="+self.dbUser+"&password="+self.dbPass
         try:
             self.con = DriverManager.getConnection(self.url)
@@ -148 +148 @@
 
         return name
+
+    '''
+    Drops a column from a table
+    '''
+    def dropColumn(self, table, column):
+
+        sql = "ALTER TABLE " + table + " DROP " + column
+        try: self.execute(sql)
+        except: return
 
     '''
@@ -355 +364 @@
             return -1
 
-
+    '''
+    Adds a column to this table which contains a row count
+    '''
+    def addRowCountColumn(self, table, colName):
+
+        self.execute("ALTER TABLE " + table + " ADD COLUMN " + colName + " INTEGER FIRST")
+        self.execute("SET @i = 0")
+        self.execute("UPDATE " + table + " SET " + colName + "=(@i:=@i+1)")
+        self.execute("ALTER TABLE " + table + " ADD PRIMARY KEY (" + colName + ")")
+