A python script that converts Oracle Database data to csv

Purpose

Dictionary information of Oracle Database, Using the python library (cx_Oracle) This is a general-purpose data acquisition script.

why?

I think that it is common to get it with spool of sqlplus After all it is late, isn't it? Isn't it troublesome to write a script according to the acquired data every time? It's good to get it, but the format is rattling and you often get it again, right?

Created script

staticinfo.py


#!/usr/bin/python
import cx_Oracle
import os
import sys
import csv

USER='******'            #Please change according to the environment(system)
PASS='********'          #Please change according to the environment(manager)
HOST='***.***.***.***'   #Please change according to the environment(192.168.0.1)
PORT='****'              #Please change according to the environment(1521)
SERVICE_NAME='****'      #Please change according to the environment(ORCL)
FETCH_ROWS=100           #Please change according to the environment

argv = sys.argv
argc = len(argv)
if argc != 2:
  print('Usage: %s filename or tablename' % argv[0])
  quit()

print("execution..." + argv[1])

p1 = argv[1]
name, ext = os.path.splitext(p1)

if ext == ".sql":
  #SQL statement acquisition
  f = open(p1)
  sql = f.read()
  f.close()

  out_file_name = name + '.csv'
else:
  #SQL statement creation
  sql = 'select * from %s' % p1

  out_file_name = p1.replace('$','_') + '.csv'

with cx_Oracle.connect(USER,PASS,HOST+':'+PORT+'/'+SERVICE_NAME) as conn:
  #SQL statement execution
  cur_detail = conn.cursor()
  cur_detail.execute(sql)

  #Get column name
  csv_header = [str[0] for str in cur_detail.description]

  #Get the data part
  f = open(out_file_name, 'w')
  writer = csv.writer(f, lineterminator='\n', quoting=csv.QUOTE_ALL)
  writer.writerow(csv_header)
  while 1:
    csv_detail = cur_detail.fetchmany(FETCH_ROWS)
    if len(csv_detail) == 0:
      break
    writer.writerows(csv_detail)
  cur_detail.close()

Execution method

$ python staticinfo.py dba_users

-It is necessary to specify environment variables (ORACLE_HOME / LD_LIBRARY_PATH), etc. -The target file (dba_users.csv) is created in the current directory. -If you prepare a script in advance, it is possible to convert the result of the script to csv.

Because it is troublesome

Create a shell like the one below and output it all at once.

staticinfo.sh


#!/bin/sh
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0.2/client_1
export LANG=ja_JP.UTF-8
export NLS_LANG=Japanese_Japan.AL32UTF8
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

python staticinfo.py dba_users
python staticinfo.py dba_profiles
python staticinfo.py dba_data_files
python staticinfo.py dba_temp_files
python staticinfo.py dba_tablespaces
python staticinfo.py dba_segments
python staticinfo.py dba_roles
python staticinfo.py dba_role_privs
python staticinfo.py dba_sys_privs
python staticinfo.py dba_tab_privs
python staticinfo.py gv\$database
python staticinfo.py gv\$instance
python staticinfo.py gv\$log
python staticinfo.py gv\$logfile
python staticinfo.py gv\$controlfile
python staticinfo.py gv\$parameter2

python staticinfo.py 01_tablespace_capacity.sql
python staticinfo.py 02_datafile_capacity.sql
python staticinfo.py 03_tempfile_capacity.sql

Finally

I wanted to make it generic, so I was particular about executing the SQL file and automatically getting the column name. Of course, it is also possible to acquire user data. Depending on the customization, I think that the script has potential.

Recommended Posts

A python script that converts Oracle Database data to csv
Python script that outputs all records of Oracle table to CSV file
[Python] A convenient library that converts kanji to hiragana
Python script to create a JSON file from a CSV file
A Python program that converts ical data into text
A Python script that saves a clipboard (GTK) image to a file.
[Python] How to store a csv file as one-dimensional array data
How to run a Maya Python script
I made a web application in Python that converts Markdown to HTML
How to read a CSV file with Python 2/3
"Python Kit" that calls a Python script from Swift
Write CSV data to AWS-S3 with AWS-Lambda + Python
A Python script that reads a SQL file, executes BigQuery and saves the csv
[Grasshopper] When creating a data tree on Python script
[Python3] Connection to Oracle Database and SQL execution [cx_Oracle]
A memorandum to run a python script in a bat file
What's in that variable (when running a Python script)
[Python] How to write a docstring that conforms to PEP8
A server that echoes data POSTed with flask / python
How to save a table scraped by python to csv
A Python script that crawls RSS in Azure Status and posts it to Hipchat
A python script that imports a dated csv file into BigQuery as a time partition table
A program that summarizes the transaction history csv data of SBI SECURITIES stocks [Python3]
A python script that wants to use Mac startup / end time for attendance management
A memo that reads data from dashDB with Python & Spark
Published a library that hides character data in Python images
[Python] Creating a GUI tool that automatically processes CSV of temperature rise data in Excel
How to run a Python program from within a shell script
[Linux] Copy data from Linux to Windows with a shell script
Creating a Python script that supports the e-Stat API (ver.2)
Five useful Python data types that are easy to forget
Build a Python environment and transfer data to the server
I tried to make a generator that generates a C # container class from CSV with Python
Python code for writing CSV data to DSX object storage
Let's create a program that automatically registers ID/PW from CSV to Bitwarden with Python + Selenium
Write to csv with Python
A Python script that goes from Google search to saving the Search results page at once
How to write a metaclass that supports both python2 and python3
[Introduction to Python] Combine Nikkei 225 and NY Dow csv data
A Python script that stores 15 years of MLB game data in MySQL in 10 minutes (Baseball Hack!)
A Python program that aggregates time usage from icalendar data
A Python script that allows you to check the status of the server from your browser
A Python script that compares the contents of two directories
[Python] A story that seemed to fall into a rounding trap
I want to use a wildcard that I want to shell with Python remove
How to pass arguments to a Python script in SPSS Modeler Batch
Create a shell script to run the python file multiple times
[Python] A memo that I tried to get started with asyncio
I wrote a function to load a Git extension script in Python
I wrote a script to extract a web page link in Python
A script that uses boto to upload a specified folder to Amason S3
How to update a Tableau packaged workbook data source using Python
A special Python codec that seems to know but does not know
[Python] Read a csv file with a large data size using a generator
I made a library that adds docstring to a Python stub file.
Created a service that allows you to search J League data
A python script that deletes ._DS_Store and ._ * files created on Mac
[python] A note that started to understand the behavior of matplotlib.pyplot
[Good By Excel] python script to generate sql to convert csv to table
[Python] A program that rotates the contents of the list to the left
How to write a Python class