# 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()