Welcome, guest | Sign In | My Account | Store | Cart

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
  1
  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()
Created by FB36 on Sun, 11 Jul 2010 (MIT)
Python recipes (4591)
FB36's recipes (148)

Required Modules

Other Information and Tasks