Finds all database rows in all tables that contain a given search string and outputs a CSV file for each table that has any match.
Python, 108 lines
Download
Copy to clipboard1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 | # 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()
|
