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

Oracle Database Regex Search using cx_Oracle (Python recipe) by FB36
ActiveState Code (http://code.activestate.com/recipes/577303/)

Finds all database rows in all tables that contain any column which matches to given regex and outputs a CSV file for each table containing matches.

Python, 51 lines
Download
 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
# Oracle database regex search
# FB36 - 201007117

import sys
import csv
import re
import cx_Oracle

connection = raw_input("Enter Oracle DB connection (uid/pwd@database) : ")
searchStr = raw_input("Enter regex search string : ")
p = re.compile(searchStr)

printHeader = True # include column headers in each table output

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

sql = "select * from tab" # get a list of all tables
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 matches the search regex
        matchedRows = []
        for row_data in curs2:
            # print row_data
            for col in row_data:
                if p.match(str(col)):
                    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')

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

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

            outputFile.close()
Tags: csv, database