Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.lang.python > #79074 > unrolled thread
| Started by | Sagar Deshmukh <sagarddd@gmail.com> |
|---|---|
| First post | 2014-10-13 18:47 -0700 |
| Last post | 2014-10-13 23:09 -0700 |
| Articles | 3 — 2 participants |
Back to article view | Back to comp.lang.python
Need help in pulling SQL query out of log file... Sagar Deshmukh <sagarddd@gmail.com> - 2014-10-13 18:47 -0700
Re: Need help in pulling SQL query out of log file... alex23 <wuwei23@gmail.com> - 2014-10-14 15:39 +1000
Re: Need help in pulling SQL query out of log file... Sagar Deshmukh <sagarddd@gmail.com> - 2014-10-13 23:09 -0700
| From | Sagar Deshmukh <sagarddd@gmail.com> |
|---|---|
| Date | 2014-10-13 18:47 -0700 |
| Subject | Need help in pulling SQL query out of log file... |
| Message-ID | <6200437a-b90a-4b60-b287-bd5b7fe37e9d@googlegroups.com> |
Hi,
I have a log file which has lot of information like..SQL query.. number of records read...records loaded etc..
My requirement is i would like to read the SQL query completly and write it to another txt file.. also the log file may not be always same so can not make static choices...
my logfile is like below :
*****LOg file starts******
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8005 Writer run started.
Fri Aug 08 16:00:04 2014 : READER_1_2_1> BLKR_16007 Reader run started.
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8158
*****START LOAD SESSION*****
Load Start Time: Fri Aug 08 16:00:04 2014
Target tables:
EIS_REQUEST_LOG_26MAYBKP
T_delta_parm_file
Fri Aug 08 16:00:04 2014 : READER_1_2_1> RR_4010 SQ instance [SQ_Shortcut_to_EIS_REQUEST_LOG] SQL Query [SELECT EIS_REQUEST_LOG_26MAYBKP.RqstId, EIS_REQUEST_LOG_26MAYBKP.RQSTLoadStatCd FROM EIS_REQUEST_LOG_26MAYBKP]
Fri Aug 08 16:00:04 2014 : READER_1_2_1> RR_4049 RR_4049 SQL Query issued to database : (Fri Aug 08 16:00:04 2014)
Fri Aug 08 16:00:04 2014 : READER_1_2_1> RR_4035 SQL Error [
FnName: Prepare -- [Teradata][ODBC Teradata Driver][Teradata Database] Object 'EIS_REQUEST_LOG_26MAYBKP' does not exist. ].
Fri Aug 08 16:00:04 2014 : READER_1_2_1> BLKR_16004 ERROR: Prepare failed.
Fri Aug 08 16:00:04 2014 : READER_1_1_1> RR_4029 SQ Instance [SQ_RSTS_Tables] User specified SQL Query [--- Approved By ICC Team-------
---SELECT A.LOGSYS ,
---D."/BIC/NIGNRCSYS" ,
---B.ODSNAME ,
---C.ODSNAME_TECH ,
---C.PARTNO ,
---C.REQUEST ,
---E.SID
---FROM
---sapbzd."RSISOSMAP" A,
---sapbzd."RSTSODS" B,
---sapbzd."RSTSODSPART" C,
---sapbzd."/BIC/PNIGNRCSYS" D,
---sapbzd."/BI0/SREQUID" E,
---sapbzd."RSMONMESS" F
---WHERE A.OLTPSOURCE = ('ZNK_SHP_DDLN_CREATE_BE','ZNK_KNVP2_BD','ZNK_ZVBW_RTN_ORD_ITM_BN','2LIS_02_SCL_BE','ZNK_FX_CRCY_HIS_BE','ZNK_PO_FX_CALC_LOG_BD','2LIS_12_VCHDR_BE','2LIS_02_HDR_BN','ZNK_SHP_DDLN_CHANGE_BD','1_CO_PAGL11000N1_BE','0CUSTOMER_ATTR_BE','2LIS_08TRTLP_BD','2LIS_02_SCN_BD','2LIS_02_HDR_BD','2LIS_13_VDITM_BE','0CO_OM_CCA_9_BE','ZNK_SO_BDSI_OPNDMD_BE','2LIS_11_VAHDR_BE','ZNK_ZVBW_MBEW_BE','2LIS_13_VDHDR_BN','ZNK_SHP_DDLN_CHANGE_BE','NK_ADDR_NUMBR_BN','0CUSTOMER_TEXT_BE','6DB_J_3ABD_DELTA_AFFL_AD','0MAT_PLANT_ATTR_BE','ZNK_BDCPV_BD','1_CO_PAGL11000N1_BD','2LIS_11_VASTI_BD','ZNK_ZVBW_MSKU_BE','ZNK_SHP_DDLN_CREATE_BD','0SCEM_1_BC','2LIS_11_VAHDR_BD','2LIS_11_VASCL_BD','0MATERIAL_TEXT_BE','0MATERIAL_ATTR_BE','ZNK_BDCPV_BE','2LIS_02_ITM_BN','2LIS_11_VASCL_BE','2LIS_11_VAITM_BN','NK_ADDR_NUMBR_BE','2LIS_08TRTK_BE','ZNK_SD_LIKPPS_BN','2LIS_03_BF_BE','ZNK_SO_BDBS_ALLOC_BD','ZNK_TD_3AVASSO_BN','0EC_PCA_3_BD','ZNK_TD_3AVAP_BE','2LIS_11_VAITM_BE','0CUST_SALES_ATTR_BN','0EC_PCA_3_BE','2LIS_13_VDITM_BN','2LIS_11_VASTH_BD','2LIS_13_VDITM_BD','0CUST_SALES_ATTR_BD','ZNK_TD_3AVASSO_BD','2LIS_02_SCN_BE','2LIS_08TRTS_BD','0CUSTOMER_ATTR_BN','ZNK_TD_3AVASSO_BE','ZNK_ZVBW_MSLB_BE','ZNK_TD_3AVAP_BD','0CUSTOMER_TEXT_BN','6DB_J_3ABD_DELTA_US_AD','0CUSTOMER_TEXT_BD','2LIS_11_VAHDR_BN','ZNK_SO_BDBS_ALLOC_BN','0GL_ACCOUNT_TEXT_BE','0GL_ACCOUNT_TEXT_BD','2LIS_11_VAITM_BD','ZNK_TD_3AVATL_BE','ZNK_SO_BDBS_ALLOC_BE','ZNK_EBAN_BE','ZNK_SO_BDSI_OPNDMD_BN','ZNK_SD_LIKPPS_BD','ZNK_ZVBW_RTN_ORD_ITM_BE','2LIS_08TRTS_BN','2LIS_02_HDR_BE','ZNK_TD_3AVATL_BD','ZNK_VBPA_BE','ZNK_FX_CRCY_HIS_BD','2LIS_13_VDHDR_BE','NK_ADDR_NUMBR_BD','2LIS_12_VCITM_BD','2LIS_08TRTK_BD','2LIS_11_VASCL_BN','ZNK_ZVBW_MCHB_BE','6DB_J_3ABD_SCL_DELTA_AP_AE','ZNK_SO_BDSI_OPNDMD_BD','ZNK_KNVP2_BE','0MAT_SALES_ATTR_BE','ZNK_TD_3AVAP_BN','2LIS_13_VDHDR_BD','0GL_ACCOUNT_ATTR_BD','2LIS_02_SCL_BD','ZNK_VBPA_BD','2LIS_02_ITM_BD','ZNK_TD_3AVATL_BN','ZNK_ZVBW_RTN_ORD_ITM_BD','ZNK_PO_FX_CALC_LOG_BE','6DB_J_3ABD_DELTA_EMEA_AD','0GL_ACCOUNT_ATTR_BE','2LIS_03_BF_BD','2!
LIS_11_V
ASTI_BE','0CO_OM_CCA_9_BD','0CUST_SALES_ATTR_BE','2LIS_12_VCITM_BE','0CUSTOMER_ATTR_BD','2LIS_02_ITM_BE','2LIS_08TRTLP_BE','2LIS_12_VCHDR_BD','ZNK_EBAN_BD','2LIS_08TRTS_BE','2LIS_02_SCL_BN','2LIS_11_VASTH_BE','ZNK_SD_LIKPPS_BE')
---AND A.LOGSYS = D."/BIC/NKLOGSYST"
---AND A.OBJVERS = 'A'
---AND A.TRANSTRU = B.ODSNAME
---AND B.DATETO = 99990101
---AND B.OBJSTAT = 'ACT'
---AND B.ODSNAME_TECH = C.ODSNAME_TECH
---AND C.DELFLAG <> 'X'
---AND D."/BIC/NIGNRCSYS" = ('R3_PRA','R3_PRD','R3_PRF','EM_EMP')
---AND D.OBJVERS = 'A'
---AND E.REQUID = C.REQUEST
---AND F.RNR = C.REQUEST
---AND F.MSGNO = '344'
---AND F.AUFRUFER = '09'---
SELECT A.LOGSYS ,
D."/BIC/NIGNRCSYS" ,
B.ODSNAME ,
C.ODSNAME_TECH ,
C.PARTNO ,
C.REQUEST ,
E.SID
FROM
sapbzd."RSISOSMAP" A,
sapbzd."RSTSODS" B,
sapbzd."RSTSODSPART" C,
sapbzd."/BIC/PNIGNRCSYS" D,
sapbzd."/BI0/SREQUID" E,
sapbzd."RSMONMESS" F
WHERE
A.OLTPSOURCE in ('ZNK_SHP_DDLN_CREATE_BE','ZNK_KNVP2_BD','ZNK_ZVBW_RTN_ORD_ITM_BN','2LIS_02_SCL_BE','ZNK_FX_CRCY_HIS_BE','ZNK_PO_FX_CALC_LOG_BD','2LIS_12_VCHDR_BE','2LIS_02_HDR_BN','ZNK_SHP_DDLN_CHANGE_BD','1_CO_PAGL11000N1_BE','0CUSTOMER_ATTR_BE','2LIS_08TRTLP_BD','2LIS_02_SCN_BD','2LIS_02_HDR_BD','2LIS_13_VDITM_BE','0CO_OM_CCA_9_BE','ZNK_SO_BDSI_OPNDMD_BE','2LIS_11_VAHDR_BE','ZNK_ZVBW_MBEW_BE','2LIS_13_VDHDR_BN','ZNK_SHP_DDLN_CHANGE_BE','NK_ADDR_NUMBR_BN','0CUSTOMER_TEXT_BE','6DB_J_3ABD_DELTA_AFFL_AD','0MAT_PLANT_ATTR_BE','ZNK_BDCPV_BD','1_CO_PAGL11000N1_BD','2LIS_11_VASTI_BD','ZNK_ZVBW_MSKU_BE','ZNK_SHP_DDLN_CREATE_BD','0SCEM_1_BC','2LIS_11_VAHDR_BD','2LIS_11_VASCL_BD','0MATERIAL_TEXT_BE','0MATERIAL_ATTR_BE','ZNK_BDCPV_BE','2LIS_02_ITM_BN','2LIS_11_VASCL_BE','2LIS_11_VAITM_BN','NK_ADDR_NUMBR_BE','2LIS_08TRTK_BE','ZNK_SD_LIKPPS_BN','2LIS_03_BF_BE','ZNK_SO_BDBS_ALLOC_BD','ZNK_TD_3AVASSO_BN','0EC_PCA_3_BD','ZNK_TD_3AVAP_BE','2LIS_11_VAITM_BE','0CUST_SALES_ATTR_BN','0EC_PCA_3_BE','2LIS_13_VDITM_BN','2LIS_11_VASTH_BD','2LIS_13_VDITM_BD','0CUST_SALES_ATTR_BD','ZNK_TD_3AVASSO_BD','2LIS_02_SCN_BE','2LIS_08TRTS_BD','0CUSTOMER_ATTR_BN','ZNK_TD_3AVASSO_BE','ZNK_ZVBW_MSLB_BE','ZNK_TD_3AVAP_BD','0CUSTOMER_TEXT_BN','6DB_J_3ABD_DELTA_US_AD','0CUSTOMER_TEXT_BD','2LIS_11_VAHDR_BN','ZNK_SO_BDBS_ALLOC_BN','0GL_ACCOUNT_TEXT_BE','0GL_ACCOUNT_TEXT_BD','2LIS_11_VAITM_BD','ZNK_TD_3AVATL_BE','ZNK_SO_BDBS_ALLOC_BE','ZNK_EBAN_BE','ZNK_SO_BDSI_OPNDMD_BN','ZNK_SD_LIKPPS_BD','ZNK_ZVBW_RTN_ORD_ITM_BE','2LIS_08TRTS_BN','2LIS_02_HDR_BE','ZNK_TD_3AVATL_BD','ZNK_VBPA_BE','ZNK_FX_CRCY_HIS_BD','2LIS_13_VDHDR_BE','NK_ADDR_NUMBR_BD','2LIS_12_VCITM_BD','2LIS_08TRTK_BD','2LIS_11_VASCL_BN','ZNK_ZVBW_MCHB_BE','6DB_J_3ABD_SCL_DELTA_AP_AE','ZNK_SO_BDSI_OPNDMD_BD','ZNK_KNVP2_BE','0MAT_SALES_ATTR_BE','ZNK_TD_3AVAP_BN','2LIS_13_VDHDR_BD','0GL_ACCOUNT_ATTR_BD','2LIS_02_SCL_BD','ZNK_VBPA_BD','2LIS_02_ITM_BD','ZNK_TD_3AVATL_BN','ZNK_ZVBW_RTN_ORD_ITM_BD','ZNK_PO_FX_CALC_LOG_BE','6DB_J_3ABD_DELTA_EMEA_AD','0GL_ACCOUNT_ATTR_BE','2LIS_03_BF_BD','2LIS_11_V!
ASTI_BE'
,'0CO_OM_CCA_9_BD','0CUST_SALES_ATTR_BE','2LIS_12_VCITM_BE','0CUSTOMER_ATTR_BD','2LIS_02_ITM_BE','2LIS_08TRTLP_BE','2LIS_12_VCHDR_BD','ZNK_EBAN_BD','2LIS_08TRTS_BE','2LIS_02_SCL_BN','2LIS_11_VASTH_BE','ZNK_SD_LIKPPS_BE')
AND A.LOGSYS = D."/BIC/NKLOGSYST"
AND A.OBJVERS = 'A'
AND A.TRANSTRU = B.ODSNAME
AND B.DATETO = 99990101
AND B.OBJSTAT = 'ACT'
AND B.ODSNAME_TECH = C.ODSNAME_TECH
AND C.DELFLAG <> 'X'
AND D."/BIC/NIGNRCSYS" in ('R3_PRA','R3_PRD','R3_PRF','EM_EMP')
AND D.OBJVERS = 'A'
AND E.REQUID = C.REQUEST
AND F.RNR = C.REQUEST
AND F.MSGNO = '344'
AND F.AUFRUFER = '09']
Fri Aug 08 16:00:04 2014 : READER_1_1_1> RR_4049 RR_4049 SQL Query issued to database : (Fri Aug 08 16:00:04 2014)
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8333 Rolling back all the targets due to fatal session error.
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8325 Final rollback executed for the target [Shortcut_to_EIS_REQUEST_LOG_DFX] at end of load
Fri Aug 08 16:00:04 2014 : WRITER_1_*_1> WRT_8035 Load complete time: Fri Aug 08 16:00:04 2014
LOAD SUMMARY
============
WRT_8036 Target: EIS_REQUEST_LOG_26MAYBKP (Instance Name: [Shortcut_to_EIS_REQUEST_LOG_DFX])
WRT_8044 No data loaded for this target
WRT_8036 Target: T_delta_parm_file (Instance Name: [Shortcut_to_T_delta_parm_file])
WRT_8044 No data loaded for this target
*****Log file ends******
Please help me on this....
[toc] | [next] | [standalone]
| From | alex23 <wuwei23@gmail.com> |
|---|---|
| Date | 2014-10-14 15:39 +1000 |
| Message-ID | <m1icv8$auh$1@dont-email.me> |
| In reply to | #79074 |
On 14/10/2014 11:47 AM, Sagar Deshmukh wrote:
> I have a log file which has lot of information like..SQL query.. number of records read...records loaded etc..
> My requirement is i would like to read the SQL query completly and write it to another txt file..
Generally we encourage people to post what they've tried to the list. It
helps us identify what you know and what you need help with.
However, given:
> the log file may not be always same so can not make static choices...
You'll probably want to use regular expressions:
https://docs.python.org/howto/regex.html
Regexps let you search through the text for known patterns and extract
any that match. To extract all SQL query sections, you'll need to come
up with a way of uniquely identifying them from all other sections.
Looking at your example log file, it looks like they're all of the format:
SQL Query [<the actual sql query>]
From that we can determine that all SQL queries are prefixed by 'SQL
Query [' and suffixed by ']', so the content you want is everything
between those markers. So a possible regular expression might be:
SQL Query \[(.*?)\]
To quickly explain this:
1. "SQL Query " matches on that string
2. Because [] have meaning for regexes, to match on literal
brackets you need to escape them via \[ and \]
3. ( ) is a group, whats contained in here will be returned
4. .* means to grab all matching text
5. ? means to do an "ungreedy" grab ie it'll stop at the first \]
it encounters.
Pulling the queries out of your log file should be as simple as:
import re
log = open('logfile').read()
queries = re.findall("SQL Query \[(.*?)\]", log, re.DOTALL)
Because the queries can fall across multiple lines, the re.DOTALL flag
is required to treat EOL markers as characters.
Hope this helps.
[toc] | [prev] | [next] | [standalone]
| From | Sagar Deshmukh <sagarddd@gmail.com> |
|---|---|
| Date | 2014-10-13 23:09 -0700 |
| Message-ID | <1bdfe07b-45f4-4549-959e-3eb176a8b055@googlegroups.com> |
| In reply to | #79076 |
On Monday, 13 October 2014 22:40:07 UTC-7, alex23 wrote:
> On 14/10/2014 11:47 AM, Sagar Deshmukh wrote:
>
> > I have a log file which has lot of information like..SQL query.. number of records read...records loaded etc..
>
> > My requirement is i would like to read the SQL query completly and write it to another txt file..
>
>
>
> Generally we encourage people to post what they've tried to the list. It
>
> helps us identify what you know and what you need help with.
>
>
>
> However, given:
>
>
>
> > the log file may not be always same so can not make static choices...
>
>
>
> You'll probably want to use regular expressions:
>
>
>
> https://docs.python.org/howto/regex.html
>
>
>
> Regexps let you search through the text for known patterns and extract
>
> any that match. To extract all SQL query sections, you'll need to come
>
> up with a way of uniquely identifying them from all other sections.
>
> Looking at your example log file, it looks like they're all of the format:
>
>
>
> SQL Query [<the actual sql query>]
>
>
>
> From that we can determine that all SQL queries are prefixed by 'SQL
>
> Query [' and suffixed by ']', so the content you want is everything
>
> between those markers. So a possible regular expression might be:
>
>
>
> SQL Query \[(.*?)\]
>
>
>
> To quickly explain this:
>
>
>
> 1. "SQL Query " matches on that string
>
> 2. Because [] have meaning for regexes, to match on literal
>
> brackets you need to escape them via \[ and \]
>
> 3. ( ) is a group, whats contained in here will be returned
>
> 4. .* means to grab all matching text
>
> 5. ? means to do an "ungreedy" grab ie it'll stop at the first \]
>
> it encounters.
>
>
>
> Pulling the queries out of your log file should be as simple as:
>
>
>
> import re
>
>
>
> log = open('logfile').read()
>
> queries = re.findall("SQL Query \[(.*?)\]", log, re.DOTALL)
>
>
>
> Because the queries can fall across multiple lines, the re.DOTALL flag
>
> is required to treat EOL markers as characters.
>
>
>
> Hope this helps.
Hi,
This helps and its working... Thank you so much....
[toc] | [prev] | [standalone]
Back to top | Article view | comp.lang.python
csiph-web