Welcome, guest | Sign In | My Account | Store | Cart
# Oracle database string search
# FB - 20130726
import sys
import os
import csv
import re
import cx_Oracle

connection = raw_input("Enter Oracle DB connection (uid/pwd@database) : ")
searchStr = raw_input("Enter search string : ")
cs = raw_input("Case-sensitive search (y/n): ").lower()
rs = raw_input("Regex search (y/n): ").lower()
wc = raw_input("Whole column must match (y/n): ").lower()

if cs == 'n':
    caseSensitive = False
elif cs == 'y':
    caseSensitive = True
else:
    print "Wrong choice!"
    os._exit(1)

if rs == 'n':
    regexSearch = False
elif rs == 'y':
    regexSearch = True
else:
    print "Wrong choice!"
    os._exit(1)

if wc == 'n':
    wholeColumn = False
elif wc == 'y':
    wholeColumn = True
else:
    print "Wrong choice!"
    os._exit(1)

if regexSearch:
    if caseSensitive:
        searchStr = re.compile(searchStr)
    else:
        searchStr = re.compile(searchStr, re.IGNORECASE)

print2Screen = True
printHeader = True # include column headers in each table output

curs = cx_Oracle.connect(connection).cursor()

tables = curs.execute("select table_name from user_tables").fetchall()
for tableNameTuple in tables:
    tableName = tableNameTuple[0]
    print "Searching table: " + tableName
    sql = "select * from " + tableName
    curs.execute(sql)

    # search the table and create a list of rows that contains the search string
    matchedRows = []
    for row_data in curs:
        # print row_data
        for col in row_data:
            colStr = str(col).strip()

            if wholeColumn: # match if search string fully matches column value
                if regexSearch:
                    if searchStr.match(colStr):
                        matchedRows.append(row_data)
                else:
                    if caseSensitive:
                        if colStr == searchStr:
                            matchedRows.append(row_data)
                    else: # not case sensitive
                        if colStr.lower() == searchStr.lower():
                            matchedRows.append(row_data)
            else: # match if search string included anywhere in column value
                if regexSearch:
                    if searchStr.search(colStr):
                        matchedRows.append(row_data)
                else:
                    if caseSensitive:
                        if colStr.find(searchStr) >= 0:
                            matchedRows.append(row_data)
                    else: # not case sensitive
                        if colStr.lower().find(searchStr.lower()) >= 0:
                            matchedRows.append(row_data)
                
    # if some rows mathed then output them
    if len(matchedRows) > 0:
        print "Found match in table " + tableName
        if print2Screen:
            print matchedRows
            print

        # create a CSV file for the table that has any match   
        csv_file_dest = tableName + ".csv"
        outputFile = open(csv_file_dest,'w') # 'wb'
        output = csv.writer(outputFile, dialect='excel')

        if printHeader: # add column headers if requested
            cols = []
            for col in curs.description:
                cols.append(col[0])
            output.writerow(cols)

        for rows in matchedRows: # add table rows
            output.writerow(rows)

        outputFile.close()

Diff to Previous Revision

--- revision 1 2010-07-11 19:32:36
+++ revision 2 2013-07-26 21:58:30
@@ -1,13 +1,16 @@
 # Oracle database string search
-# FB36 - 201007117
-
+# FB - 20130726
 import sys
+import os
 import csv
+import re
 import cx_Oracle
 
 connection = raw_input("Enter Oracle DB connection (uid/pwd@database) : ")
 searchStr = raw_input("Enter search string : ")
-cs = raw_input("Case-sensitive search (y/n): ")
+cs = raw_input("Case-sensitive search (y/n): ").lower()
+rs = raw_input("Regex search (y/n): ").lower()
+wc = raw_input("Whole column must match (y/n): ").lower()
 
 if cs == 'n':
     caseSensitive = False
@@ -15,48 +18,91 @@
     caseSensitive = True
 else:
     print "Wrong choice!"
-    sys.exit()
+    os._exit(1)
 
+if rs == 'n':
+    regexSearch = False
+elif rs == 'y':
+    regexSearch = True
+else:
+    print "Wrong choice!"
+    os._exit(1)
+
+if wc == 'n':
+    wholeColumn = False
+elif wc == 'y':
+    wholeColumn = True
+else:
+    print "Wrong choice!"
+    os._exit(1)
+
+if regexSearch:
+    if caseSensitive:
+        searchStr = re.compile(searchStr)
+    else:
+        searchStr = re.compile(searchStr, re.IGNORECASE)
+
+print2Screen = True
 printHeader = True # include column headers in each table output
 
-orcl = cx_Oracle.connect(connection)
-curs = orcl.cursor()
+curs = cx_Oracle.connect(connection).cursor()
 
-sql = "select * from tab" # get a list of all tables
-curs.execute(sql)
+tables = curs.execute("select table_name from user_tables").fetchall()
+for tableNameTuple in tables:
+    tableName = tableNameTuple[0]
+    print "Searching table: " + tableName
+    sql = "select * from " + tableName
+    curs.execute(sql)
 
-for row_data in curs:
-    if not row_data[0].startswith('BIN$'): # skip recycle bin tables
-        tableName = row_data[0]
-        sql = "select * from " + tableName
-        curs2 = orcl.cursor()
-        curs2.execute(sql)
+    # search the table and create a list of rows that contains the search string
+    matchedRows = []
+    for row_data in curs:
+        # print row_data
+        for col in row_data:
+            colStr = str(col).strip()
 
-        # search the table and create a list of rows that contains the search string
-        matchedRows = []
-        for row_data in curs2:
-            # print row_data
-            for col in row_data:
-                if caseSensitive:
-                    if str(col).find(searchStr) >= 0:
+            if wholeColumn: # match if search string fully matches column value
+                if regexSearch:
+                    if searchStr.match(colStr):
                         matchedRows.append(row_data)
-                else: # not case sensitive
-                    if str(col).lower().find(searchStr.lower()) >= 0:
+                else:
+                    if caseSensitive:
+                        if colStr == searchStr:
+                            matchedRows.append(row_data)
+                    else: # not case sensitive
+                        if colStr.lower() == searchStr.lower():
+                            matchedRows.append(row_data)
+            else: # match if search string included anywhere in column value
+                if regexSearch:
+                    if searchStr.search(colStr):
                         matchedRows.append(row_data)
-                    
-        # if some rows mathed then output them
-        if len(matchedRows) > 0:
-            csv_file_dest = tableName + ".csv"
-            outputFile = open(csv_file_dest,'w') # 'wb'
-            output = csv.writer(outputFile, dialect='excel')
+                else:
+                    if caseSensitive:
+                        if colStr.find(searchStr) >= 0:
+                            matchedRows.append(row_data)
+                    else: # not case sensitive
+                        if colStr.lower().find(searchStr.lower()) >= 0:
+                            matchedRows.append(row_data)
+                
+    # if some rows mathed then output them
+    if len(matchedRows) > 0:
+        print "Found match in table " + tableName
+        if print2Screen:
+            print matchedRows
+            print
 
-            if printHeader: # add column headers if requested
-                cols = []
-                for col in curs2.description:
-                    cols.append(col[0])
-                output.writerow(cols)
+        # create a CSV file for the table that has any match   
+        csv_file_dest = tableName + ".csv"
+        outputFile = open(csv_file_dest,'w') # 'wb'
+        output = csv.writer(outputFile, dialect='excel')
 
-            for rows in matchedRows: # add table rows
-                output.writerow(rows)
+        if printHeader: # add column headers if requested
+            cols = []
+            for col in curs.description:
+                cols.append(col[0])
+            output.writerow(cols)
 
-            outputFile.close()
+        for rows in matchedRows: # add table rows
+            output.writerow(rows)
+
+        outputFile.close()

History