轉載：使用Python一鍵產生Oracle效能excel曲線圖

標籤：color   extend   directory   問題   oracle資料庫   insert   tutorial   oca   官方網站   

文章轉自：http://www.shsnc.cn/show-109-970-1.html

進一步具體可參考：http://xlsxwriter.readthedocs.io/tutorial01.html

 

1. 為什麼要產生曲線圖

做oracle資料庫營運，有時會經常要看一周內資料庫啟動並執行情況，而通過效能曲線圖最能展現資料庫系統的效能變化情況了。如果有安裝oracle的EM，看這個也不是問題。但有些資料庫是沒安裝的，而且EM的是較難以實現定製化的報表需求的，難以把圖表發給使用者。

 

先來看一下要產生的資料：

 

 

還有些附加的要求，如產生報表的工具需要在UNIX/LINUX下執行，能一鍵直接產生exel報表，可以方便地插入到word中。

 

本文正是如何使用python實現這一過程的，主要面向oracle dba且對程式設計語言有一定瞭解的讀者。

 

2. 為什麼要使用 python

 

如果說到為什麼要使用python，只要稍微瞭解一下自動化營運，就會明白python是這一領域編程的王者。業界最有名的自動化營運工具SaltStack及ansible都是基於python實現的。

 

3. 如何做到

 

接下講在已經安裝好oracle資料庫11g DB的Linux環境下，如何一步一步實現最終目標。

 

3.1. 安裝 python

 

在大部份linux及unix下本身會內建python環境，但版本可能過舊，可能會導致後面使用到的一些包不相容，因此建議進行新安裝。安裝時可以考慮使用root帳號進行安裝，如果沒有root許可權，使用主機帳號oracle也可以進行安裝。

 

Python的版本選擇了最新的2.7.10版本，未選3.5主要原因是考慮到3.x下的軟體包還不如2.x下豐富。

 

以oracle帳號安裝python樣本：

 

推薦使用原始碼方式進行安裝，主要的是從 http://www.python.org/ftp/python/下載 Python 的原始碼。選擇最新的版本，下載.tgz 檔案，執行通常的 configure, make, make install 步驟。

 

參考如下：

 

本文設定的python主安裝目錄為/home/oracle/opt/python2.7.10/

 

切換至oracle使用者並建立安裝目錄

 

su - oracle
[[email protected] ~]$ mkdir -p /home/oracle/opt/python2.7.10/

 

設定python環境變數

 

[[email protected] ~]$ vi ~/.bash_profile
在PATH一行加入python路徑
PATH=/home/oracle/opt/python2.7.10/bin:/usr/sbin:/sbin:/usr/bin:$PATH:$HOME/bin:
 
export PATH

 

下載安裝包

 

[[email protected] ~]$ wget https://www.python.org/ftp/python/2.7.10/Python-2.7.10.tgz --no-check-certificate
 
--2015-10-25 12:41:56--  https://www.python.org/ftp/python/2.7.10/Python-2.7.10.tgz
Resolving www.python.org... 103.245.222.223
Connecting to www.python.org|103.245.222.223|:443... connected.
WARNING: cannot verify www.python.org‘s certificate, issued by `/C=US/O=DigiCert Inc/OU=www.digicert.com/CN=DigiCert SHA2 Extended Validation Server CA‘:
  Unable to locally verify the issuer‘s authority.
HTTP request sent, awaiting response... 200 OK
Length: 16768806 (16M) [application/octet-stream]
...

 

解壓安裝包並進入目錄

 

[[email protected] Python-2.7.10]$ tar xfz Python-2.7.10.tgz
[[email protected] Python-2.7.10]$ cd Python-2.7.10

 

執行configure命令

 

[[email protected] Python-2.7.10]$./configure --prefix=/home/oracle/opt/python2.7.10/
checking build system type... i686-pc-linux-gnu
checking host system type... i686-pc-linux-gnu
checking for --enable-universalsdk... no
checking for --with-universal-archs... 32-bit
....
config.status: pyconfig.h is unchanged
creating Modules/Setup
creating Modules/Setup.local
creating Makefile

 

執行make命令

 

[[email protected] Python-2.7.10]$ make
...

 

執行make install命令

 

 [[email protected] Python-2.7.10]$ make install
...

 

驗證python

 

[[email protected] Python-2.7.10]$ python
Python 2.7.10 (default, Oct 25 2015, 13:07:20)
[GCC 4.1.2 20080704 (Red Hat 4.1.2-46)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> 

 

到此python的2.7.10 for linux已安裝完成

 

3.2.     安裝 cx_Oracle 模組

 

安裝cx_Oracle的主要目的是使python能串連oracle資料庫，用於擷取資料庫中的會話記錄

 

前提條件為

 

oracle資料庫軟體或用戶端已經安裝好，版本10g以上，關鍵環境變數ORACLE_HOME, LD_LIBRARY_PATH已正確配置

 

本文的關鍵設定如下：

vi ~/.bash_profile
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib

 

重新整理環境變數

 

source ~/.bash_profile

 

python 版本建議是2.6以上

 

cx_Oracle的為https://pypi.python.org/pypi/cx_Oracle/#downloads,到本文截止時最的安裝包為cx_Oracle-5.2.tar.gz

 

安裝及測試過程如下：

下載

wget https://pypi.python.org/packages/source/c/cx_Oracle/cx_Oracle-5.2.tar.gz#md5=6a49e1aa0e5b48589f8edfe5884ff5a5 --no-check-certificate

 

解壓

 

gunzip cx_Oracle-5.2.tar.gz
tar xvf cx_Oracle-5.2.tar

 

執行安裝

 

cd cx_Oracle-5.2
python setup.py install

 

測試cx_Oracle模組是否正常

 

>>> import cx_Oracle
>>> 

 

如出現以下ImportError提示，表示資料庫軟體未正常安裝或關鍵環境變更設定不正確

 

>>> import cx_Oracle
Traceback (most recent call last):
  File "", line 1, in 
ImportError: libclntsh.so.11.1: cannot open shared object file: No such file or directory

 

測試使用python串連oracle資料庫

 

vi test_cx_oracle.py

 

# -*- coding:UTF-8 -*-
import cx_Oracle                                #引用模組cx_Oracle
conn=cx_Oracle.connect( ‘scott/[email protected]:1521/orcl‘)   #串連資料庫，格式為：使用者名稱/密碼@IP:連接埠/服務名
c=conn.cursor()                                  #擷取cursor
x=c.execute( ‘select * from scott.emp‘)                    #使用cursor進行各種操作
print x.fetchone()                                #列印一行
row = x.fetchall()
for r  in row:                                    #迴圈列印所有行
     print r
c.close()                                         #關閉cursor
conn.close()                                      #關閉串連

 

執行測試指令碼

 

python test_cx_oracle.py

 

出現類似如下結果，表明python已正常串連資料庫，並成功擷取資料，列印出來

 

[[email protected] ~]$ python test_cx_oracle.py
(7369, ‘SMITH‘, ‘CLERK‘, 7902, datetime.datetime(1980, 12, 17, 0, 0), 800.0, None, 20)
(7499, ‘ALLEN‘, ‘SALESMAN‘, 7698, datetime.datetime(1981, 2, 20, 0, 0), 1600.0, 300.0, 30)
(7521, ‘WARD‘, ‘SALESMAN‘, 7698, datetime.datetime(1981, 2, 22, 0, 0), 1250.0, 500.0, 30)
(7566, ‘JONES‘, ‘MANAGER‘, 7839, datetime.datetime(1981, 4, 2, 0, 0), 2975.0, None, 20)
(7654, ‘MARTIN‘, ‘SALESMAN‘, 7698, datetime.datetime(1981, 9, 28, 0, 0), 1250.0, 1400.0, 30)
(7698, ‘BLAKE‘, ‘MANAGER‘, 7839, datetime.datetime(1981, 5, 1, 0, 0), 2850.0, None, 30)
(7782, ‘CLARK‘, ‘MANAGER‘, 7839, datetime.datetime(1981, 6, 9, 0, 0), 2450.0, None, 10)
(7788, ‘SCOTT‘, ‘ANALYST‘, 7566, datetime.datetime(1987, 4, 19, 0, 0), 3000.0, None, 20)
(7839, ‘KING‘, ‘PRESIDENT‘, None, datetime.datetime(1981, 11, 17, 0, 0), 5000.0, None, 10)
(7844, ‘TURNER‘, ‘SALESMAN‘, 7698, datetime.datetime(1981, 9, 8, 0, 0), 1500.0, 0.0, 30)
(7876, ‘ADAMS‘, ‘CLERK‘, 7788, datetime.datetime(1987, 5, 23, 0, 0), 1100.0, None, 20)
(7900, ‘JAMES‘, ‘CLERK‘, 7698, datetime.datetime(1981, 12, 3, 0, 0), 950.0, None, 30)
(7902, ‘FORD‘, ‘ANALYST‘, 7566, datetime.datetime(1981, 12, 3, 0, 0), 3000.0, None, 20)
(7934, ‘MILLER‘, ‘CLERK‘, 7782, datetime.datetime(1982, 1, 23, 0, 0), 1300.0, None, 10)

 

到此，串連oracle的cx_Oracle模組安裝測試完成

 

3.3.     安裝 xlsxwriter 模組

 

為什麼要安裝xlsxwirter模組呢，主要是用來以python方式讀寫xlsx檔案，也就是excel檔案，並操作excel組建圖表

 

以下為安裝及測試過程

 

：https://pypi.python.org/pypi/XlsxWriter#downloads

 

下載

 

wget https://pypi.python.org/packages/source/X/XlsxWriter/XlsxWriter-0.7.7.tar.gz#md5=c68dca16927e2a919837c68ff63b1e5b  --no-check-certificate

 

解壓

 

gunzip XlsxWriter-0.7.7.tar.gz
tar xvf XlsxWriter-0.7.7.tar

 

執行安裝

 

cd XlsxWriter-0.7.7
python setup.py install

 

測試xlsxwriter模組是否正常

 

>>> import xlsxwriter
>>> 

 

測試寫xlsx檔案

 

vi test_xlsx.py

 

# -*- coding:UTF-8 -*-

import xlsxwriter

workbook = xlsxwriter.Workbook( ‘hello.xlsx‘)
worksheet = workbook.add_worksheet()

worksheet.write( ‘A1‘,  ‘Hello world‘)

workbook.close()

 

下載產生的檔案hello.xlsx，用excel開啟可以看到，產生結果如下：

 

 

從可以看到，已在A1儲存格正確寫入Hello world。

 

到此，xlsxwriter模組安裝完成。

 

3.4.     編寫一鍵產生指令碼

 

資料從何而來？

 

要產生曲線圖，總有得有資料。

 

通過以下語句便可以查詢oracle資料庫AWR報告中會話變化情況資料（事先給相應帳號授權，如：grant select any dictionary to scott）：

 

SELECT
 to_char(trunc(b.end_interval_time+10/60/24,‘hh24‘),‘yyyymmdd_hh24‘) snap_time,     
       sum(decode(a.instance_number, 1, a.value)) "INST_1",
       sum(decode(a.instance_number, 2, a.value)) "INST_2",
       c.value max_session,
       d.name ,a.snap_id
  FROM DBA_HIST_SYSSTAT a, dba_hist_snapshot b, (select value from v$parameter where name=‘sessions‘) c,v$database d
 WHERE a.SNAP_ID = b.snap_id
   AND a.instance_number = b.instance_number
   AND STAT_NAME = ‘logons current‘
 GROUP BY d.name,a.snap_id, c.value,  to_char(trunc(b.end_interval_time+10/60/24,‘hh24‘),‘yyyymmdd_hh24‘)
 order by snap_id;

 

 

中的INST_2沒有資料，是因為本次串連的資料庫為單一實例資料庫，所以資料為空白。

 

開啟xlsxwriter官方網站，http://xlsxwriter.readthedocs.org/，看看是如何產生excel曲線圖的。

 

在首頁找到Chart Examples，再進入Example: Line Chart頁面。

 

http://xlsxwriter.readthedocs.org/example_chart_line.html

 

以下的官方給出的例子：

 

 

#######################################################################
#
# An example of creating Excel Line charts with Python and XlsxWriter.
#
# Copyright 2013-2015, John McNamara, [email protected]
#
import xlsxwriter

workbook = xlsxwriter.Workbook( ‘chart_line.xlsx‘)
worksheet = workbook.add_worksheet()
bold = workbook.add_format({ ‘bold‘: 1})

# Add the worksheet data that the charts will refer to.
headings = [ ‘Number‘,  ‘Batch 1‘,  ‘Batch 2‘]
data = [
    [2, 3, 4, 5, 6, 7],
    [10, 40, 50, 20, 10, 50],
    [30, 60, 70, 50, 40, 30],
]

worksheet.write_row( ‘A1‘, headings, bold)
worksheet.write_column( ‘A2‘, data[0])
worksheet.write_column( ‘B2‘, data[1])
worksheet.write_column( ‘C2‘, data[2])

# Create a new chart object. In this case an embedded chart.
chart1 = workbook.add_chart({ ‘type‘:  ‘line‘})

# Configure the first series.
chart1.add_series({
     ‘name‘:        ‘=Sheet1!$B$1‘,
     ‘categories‘:  ‘=Sheet1!$A$2:$A$7‘,
     ‘values‘:      ‘=Sheet1!$B$2:$B$7‘,
})

# Configure second series. Note use of alternative syntax to define ranges.
chart1.add_series({
     ‘name‘:       [ ‘Sheet1‘, 0, 2],
     ‘categories‘: [ ‘Sheet1‘, 1, 0, 6, 0],
     ‘values‘:     [ ‘Sheet1‘, 1, 2, 6, 2],
})

# Add a chart title and some axis labels.
chart1.set_title ({ ‘name‘:  ‘Results of sample analysis‘})
chart1.set_x_axis({ ‘name‘:  ‘Test number‘})
chart1.set_y_axis({ ‘name‘:  ‘Sample length (mm)‘})

# Set an Excel chart style. Colors with white outline and shadow.
chart1.set_style(10)

# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart( ‘D2‘, chart1, { ‘x_offset‘: 25,  ‘y_offset‘: 10})


workbook.close()

 

結合cx_Oracle的模組部分代碼，再稍作修改，便可得出以下代碼：

 

# -*- coding:UTF-8 -*-
#######################################################################
#
# An example of creating Excel Line charts with Python and XlsxWriter.
#
# Copyright 2013-2015, John McNamara, [email protected]
#
import xlsxwriter, cx_Oracle, sys
 
reload(sys)
sys.setdefaultencoding(‘utf-8‘)
 
conn = cx_Oracle.connect(‘scott/[email protected]:1521/orcl)  # 串連資料庫
 
c = conn.cursor()  # 擷取cursor
 
x = c.execute(‘‘‘
SELECT
     to_char(trunc(b.end_interval_time+10/60/24,‘hh24‘),‘yyyymmdd_hh24‘) snap_time,
           sum(decode(a.instance_number, 1, a.value)) "INSTANCE_1",
           sum(decode(a.instance_number, 2, a.value)) "INSTANCE_2",
           c.value max_session,
           d.name ,a.snap_id
      FROM DBA_HIST_SYSSTAT a, dba_hist_snapshot b, (select value from v$parameter where name=‘sessions‘) c,v$database d
     WHERE a.SNAP_ID = b.snap_id
       AND a.instance_number = b.instance_number
       AND STAT_NAME = ‘logons current‘
     GROUP BY d.name,a.snap_id, c.value,  to_char(trunc(b.end_interval_time+10/60/24,‘hh24‘),‘yyyymmdd_hh24‘)
     order by snap_id
‘‘‘)
dbname = x.fetchone()[4]
row = x.fetchall()
 
workbook = xlsxwriter.Workbook(‘chart_line.xlsx‘)
worksheet = workbook.add_worksheet()
bold = workbook.add_format({‘bold‘: 1})
 
# Add the worksheet data that the charts will refer to.
headings = [‘DATE_TIME‘, ‘INST_1‘, ‘INST_2‘, ‘MAXSESS‘, ‘DBNAME‘, ‘SNAP_ID‘, ]
 
worksheet.write_row(‘A1‘, headings, bold)
 
# worksheet.write_column(‘A2‘, row[0])
i = 0
for r in row:
    worksheet.write_row(‘A‘ + str(i + 2), row[i])
    i = i + 1
 
# Create a new chart object. In this case an embedded chart.
chart1 = workbook.add_chart({‘type‘: ‘line‘})
 
chart1.add_series({
    ‘name‘: [‘Sheet1‘, 0, 1],
    ‘categories‘: [‘Sheet1‘, 1, 0, i, 0],
    ‘values‘: [‘Sheet1‘, 1, 1, i, 1],
})
 
# Configure second series. Note use of alternative syntax to define ranges.
chart1.add_series({
    ‘name‘: [‘Sheet1‘, 0, 2],
    ‘categories‘: [‘Sheet1‘, 1, 0, i, 0],
    ‘values‘: [‘Sheet1‘, 1, 2, i, 2],
})
 
# Add a chart title and some axis labels.
chart1.set_title({‘name‘: dbname + ‘資料庫會話數‘})
 
# Set an Excel chart style. Colors with white outline and shadow.
chart1.set_style(10)
 
# Insert the chart into the worksheet (with an offset).
worksheet.insert_chart(‘D2‘, chart1, {‘x_offset‘: 25, ‘y_offset‘: 10})
 
c.close()
workbook.close()

 

儲存為chart_line.py

 

執行python chart_line.py

 

開啟產生的excel檔案chart_line.xlsx，便可看到產生的如表

 

 

以只有一條線，是因為串連的單一實例資料庫，如果串連到rac資料庫，便可產生兩個節點的圖表。

 

4. 總結

 

本文以python的方式從無到有實現的python串連oracle資料庫，一鍵式產生excel圖表，僅供各位營運的同學參考，有很多不完善的地方，還請多多包涵，如有需要可自行擴充。

 

當然，以java或其他方式也可以實現，但Python的簡單易用，以很少的代碼量，讓我們很容易就實現了自己想要的功能。畢竟，人生有很多選擇，選擇一種自己喜歡的過吧。

 

至於個人是如何想這樣做的呢，主要是參考了劉天斯的《Python自動化營運：技術與最佳實務》，一步步百度、GOOGLE而來的。

轉載：使用Python一鍵產生Oracle效能excel曲線圖