How to take a full table back-up in Cx_Oracle?

Here is a python script written for you to take a back of all tables information. As you might be only required to take back-up specific table(s) you can tweek below line from code

sql = "select * from tab" # get a list of all tables

 Code:

# Export Oracle database tables to CSV files
# FB36 - 201007117

import sys
import csv
import cx_Oracle

connection = raw_input("Enter Oracle DB connection (uid/pwd@database) : ")
orcl = cx_Oracle.connect(connection)
curs = orcl.cursor()

printHeader = True # include column headers in each table output

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]

        # output each table content to a separate CSV file
        csv_file_dest = tableName + ".csv"
        outputFile = open(csv_file_dest,'w') # 'wb'
        output = csv.writer(outputFile, dialect='excel')
        sql = "select * from " + tableName
        curs2 = orcl.cursor()
        curs2.execute(sql)

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

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

        outputFile.close()

Source: http://code.activestate.com/recipes/577304-export-oracle-database-to-csv-using-cx_oracle/

Golden Rule : Don’t re-invent the wheel.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s