Use Python script to comply sqlldr’s tool auto

The CSV file that use python script was output.The CSV file was loaded the oracle database.
we will use the sqlldr tools comply the function.

NOTE:
1.csv file format ,for example:xxxx-yyyymmdd.csv
2.os env:linux,don't fit the windows
3.the python script at the oracle database server machine.
4.configure the python on the oracle database server machine 


# -*- coding: utf-8 -*-
"""
Created on Thu Nov 24 17:04:34 2016

@author: trsenzhang
"""

import os 
import sys
import time

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
#python script directory
CUR_PATH = os.path.dirname(os.path.realpath(__file__))

def sed(type,filename="",s_str="",d_str=""):  
  '''
  a behind the "s_str" line append "d_str". if "s_str" is null, then append in the end of the file;
  i in front of "s_str" line append "d_str". if "s_str" is null,then append in the header of the;
  d  delete the "s_str" line 
  s replace string of "s_str" to "d_str"
  '''

  fp = open(filename)
  cont = fp.read()
  fp.close()
  content = cont.split("\n")
  content2 = cont.split("\n")
  cnt = 0
  idx = 0
  if type == 'a':
      if not s_str:
          content.append(d_str)
      else:
          for i in content2:
              if i.find(s_str) != -1:
                  x = idx + 1 + cnt
                  content.insert(x,d_str)
                  cnt += 1
              idx += 1
  elif type == 'i': 
      if not s_str: 
          content.insert(0,d_str)
      else:  
          for i in content2: 
              if i.find(s_str) != -1:  
                  x = idx + cnt  
                  content.insert(x,d_str)  
                  cnt += 1 
              idx += 1 
  elif type == 'd':  
      for i in content2:  
          if i.find(s_str) != -1:  
              idx = content.remove(i)  
  elif type == 's':
      cont=str.replace(cont,s_str,d_str)
      content=cont.split("\n")
  fp = open(filename, "w")  
  fp.write("\n".join(content))  
  fp.close()

def getSqlldir():            
    ctlfile=CUR_PATH+'/sqlldir/orders.ctl'
    csvfile1=CUR_PATH+'/report/Orders-'+str(int(time.strftime("%Y%m%d",time.localtime()))-2)+'.csv'
    csvfile2=CUR_PATH+'/report/Orders-'+str(int(time.strftime("%Y%m%d",time.localtime()))-1)+'.csv'
    sqllogfile=CUR_PATH+'/sqlldir/log/Orders_sqlldir_'+str(int(time.strftime("%Y%m%d",time.localtime()))-1)+'.log'
    badfile=CUR_PATH+'/sqlldir/log/Orders_sqlldir_'+str(int(time.strftime("%Y%m%d",time.localtime()))-1)+'.bad'
    #modify the sqlldr controlfile    
    sed('s',ctlfile,csvfile1,csvfile2)
    #load the csv to oracle database.
    os.system('/u01/app/oracle/product/11.2.0/dbhome_1/bin/sqlldr system/oracle control='+ctlfile+' log='+sqllogfile+' bad='+badfile+' skip=1')

if __name__ == '__main__':
    if not os.path.exists('%s/sqlldir' % CUR_PATH):
        os.mkdir('%s/sqlldir' % CUR_PATH)
  if not os.path.exists('%s/sqlldir/log' % CUR_PATH):
              os.mkdir('%s/sqlldir/log' %s CUR_PATH):
    getSqlldir()

How use hython’s smtplib and email module ?

first,Verification module is or not install. 

[oracle@datahouse report]$ python
Python 2.7.12 |Anaconda 4.2.0 (64-bit)| (default, Jul  2 2016, 17:42:40) 
[GCC 4.4.7 20120313 (Red Hat 4.4.7-1)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
Anaconda is brought to you by Continuum Analytics.
Please check out: http://continuum.io/thanks and https://anaconda.org
>>> import smtplib
>>> dir(smtplib)
['CRLF', 'LMTP', 'LMTP_PORT', 'OLDSTYLE_AUTH', 'SMTP', 'SMTPAuthenticationError', 'SMTPConnectError', 'SMTPDataError', 'SMTPException', 'SMTPHeloError', 'SMTPRecipientsRefused', 'SMTPResponseException', 'SMTPSenderRefused', 'SMTPServerDisconnected', 'SMTP_PORT', 'SMTP_SSL', 'SMTP_SSL_PORT', 'SSLFakeFile', '_MAXLINE', '__all__', '__builtins__', '__doc__', '__file__', '__name__', '__package__', '_addr_only', '_have_ssl', 'base64', 'email', 'encode_base64', 'hmac', 'quoteaddr', 'quotedata', 're', 'socket', 'ssl', 'stderr']
>>> import email
>>> dir(email)
['Charset', 'Encoders', 'Errors', 'FeedParser', 'Generator', 'Header', 'Iterators', 'LazyImporter', 'MIMEAudio', 'MIMEBase', 'MIMEImage', 'MIMEMessage', 'MIMEMultipart', 'MIMENonMultipart', 'MIMEText', 'Message', 'Parser', 'Utils', '_LOWERNAMES', '_MIMENAMES', '__all__', '__builtins__', '__doc__', '__file__', '__name__', '__package__', '__path__', '__version__', '_name', '_parseaddr', 'base64MIME', 'base64mime', 'email', 'encoders', 'importer', 'message_from_file', 'message_from_string', 'mime', 'quopriMIME', 'sys', 'utils']
>>>

implementation mail detail informations 

[oracle@datahouse ~]$ more mail.py        
#/usr/bin/env python
# -*- coding: utf-8 -*-

import os
import re
import sys
import time
import email
import datetime
import smtplib
import urllib2
import sys
import socket
from email.MIMEMultipart import MIMEMultipart
from email.MIMEText import MIMEText

class Mail(object):
 """邮件"""
 host = 'mail.163.com.cn'
 port = ''
 user = ''
 psd = ''
 fromAdd = ''
 toAdd = []
 subject = ''
 plainText = ''
 htmlText = '''<html>
 <head><title>''' + subject + '''</title></HEAD>
 <body><span style='cursor=hand'><B>HTML文本</B></span></html>
  '''
 def __init__(self, host='mail.163.com.cn', port='25', user='it', psd='xxxxx', mailFrom='it@163.com.cn', mailTo=['trsenzhang@163.com.cn']):
  ipadd=socket.gethostbyname(host)
  self.port = port
  self.host = host
  self.user = user
  self.psd = psd
  self.fromAdd = mailFrom
  self.toAdds = mailTo
  self.port = port
  
 def sendMsg(self, subject, msg, attachment_file=None, _login='false'):
  server = self.host 
  user = self.user
  psd = self.psd
  mailto = ';'.join(self.toAdds)
  msgRoot = MIMEMultipart('related')
  msgRoot['Subject'] = subject
  msgRoot['From'] = '系统监测<' + self.fromAdd + '>'
  msgRoot['To'] = mailto
  msgRoot.preamble = 'This is a multi-part message in MIME format.'
  #Encapsulate the plain and HTML versions of the message body in an
  #'alternative' part, so message agents can decide which they want to display.
  msgAlternative = MIMEMultipart('alternative')
  msgRoot.attach(msgAlternative)
  #Define the text informations
  msgText = MIMEText(msg, 'plain', 'gb2312')
  msgAlternative.attach(msgText)
  contype = 'application/octet-stream'
  maintype, subtype = contype.split('/', 1)
  print attachment_file
  
  if attachment_file:
   #设置附件头
   for fn in attachment_file:
    data = open(fn,'rb')
    file_msg = email.MIMEBase.MIMEBase(maintype, subtype)
    file_msg.set_payload(data.read())
    data.close()
    bn=os.path.basename(fn)
    file_msg.add_header('Content-Disposition','attachment',filename=bn)
    msgAlternative.attach(file_msg)
    email.Encoders.encode_base64(file_msg)   
  #发送邮件
  try:
   smtp = smtplib.SMTP()
   smtp.connect(server, self.port)
   if _login == 'true':
    smtp.starttls()
   smtp.login(user, psd)
   smtp.sendmail(self.fromAdd, self.toAdds, msgRoot.as_string())
   smtp.quit()
   smtp.close()
  except Exception,e :
   return
  return
  
 def sendEmail(self, authInfo, fromAdd, toAdd, subject, plainText, htmlText):
  strFrom = fromAdd
  strTo = ', '.join(toAdd)
  server = authInfo.get('server')
  user = authInfo.get('user')
  passwd = authInfo.get('password')
  if not (server and user and passwd) :
    return

  #设定root信息
  msgRoot = MIMEMultipart('related')
  msgRoot['Subject'] = subject
  msgRoot['From'] = strFrom
  msgRoot['To'] = strTo

  #Encapsulate the plain and HTML versions of the message body in an
  #'alternative' part, so message agents can decide which they want to display.
  msgAlternative = MIMEMultipart('alternative')
  msgRoot.attach(msgAlternative)
  #设定纯文本信息
  msgText = MIMEText(plainText, 'plain', 'gb2312')
  msgAlternative.attach(msgText)
  #发送邮件
  smtp = smtplib.SMTP()
  #设定调试级别，依情况而定
  #smtp.set_debuglevel(1)
  smtp.connect(server)
  #smtp.login(user, passwd)
  smtp.sendmail(strFrom, strTo, msgRoot.as_string())
  smtp.quit()
  return

execute the scripts 


[oracle@datahouse ~]$ more t1.py 
#!/bin/env python
#coding:utf-8

import sys
import os
import socket
from mail import Mail
from utility import logger
import time

os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK'

#mail function
def mail(logfile):
    mail_to=['trsenzhang@163.com.cn']
    port="25"
    m=Mail(port=port,mailTo=mail_to)
    try:
        subject='数据仓库orders表加工'
        content=open(logfile,'r').read()
        m.sendMsg(subject,content)
    except:
        return

if __name__ == '__main__':

# mail report
 logfile= '/report/log/'+'orders_'+time.strftime("%Y%m%d",time.localtime())+'.log'
 f=open(logfile,'w')
 f.write('orders在:'+time.strftime("%Y:%m:%d %H:%M:%S",time.localtime())+' 完成CSV文件转换\n')
 f.close()
 mail(logfile)        
            
Any error
Don't config the /etc/resolv.conf to DNS

[oracle@datahouse report]$ python t1.py 
Traceback (most recent call last):
  File "t1.py", line 18, in <module>
    ipadd=socket.gethostbyname('mail.163.com.cn')
socket.gaierror: [Errno -3] Temporary failure in name resolution

solution:
Config the /etc/resolv.conf

Python Achieve Encryption and Decryption

    Security is a very important topic.Using python programe processing data in the database maybe need to encrypt and decrpypt.
    Today,we provid the way that use the md5 algorithm. the coding as follows:    
    we can only study Crypto.Cipher module of AES and Random
    
# sudo pip install pycrypto 
from Crypto.Cipher import AES
from Crypto import Random

BS = 16
pad = lambda s: s + (BS – len(s) % BS) * chr(BS – len(s) % BS)
unpad = lambda s : s[0:-ord(s[-1])]
#32 bit key
key = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'

class AESCipher:
    def __init__(self):
        """
        Requires hex encoded param as a key
        """
        self.key = key.decode("hex") 
        self.cipher = AES.new(self.key,AES.MODE_ECB)

    def encrypt( self, string):
        """
        Returns hex encoded encrypted value!
        """
        encrypted_string = string
        try:
            raw = pad(string) 
            encrypted_string = self.cipher.encrypt(raw).encode('hex')
            encrypted_string = encrypted_string.upper()
        except Exception as e:
            pass        
        return encrypted_string

    def decrypt( self, encrypted_string ):
        """
        Requires hex encoded param to decrypt
        """
        decrypted_string =encrypted_string
        try:
            decrypted_string = unpad(self.cipher.decrypt(encrypted_string.decode('hex')))
        except Exception as e:
            pass        
        return decrypted_string

if __name__ == '__main__'
    aes = AESCipher()
    ….
    


 

Python Data Structures’s LIST

The list slice technology
list[start:end:step]==>>
1.elements of the start are including in the new list,but elements of the end aren't including in the new list.
2.if start and end is null ,then start is the list's staring  postition, the end is the list' ending postition.
3.if step=-1 and start is null and end is null ,then the list was reversed.

l =         [1,2,3,4,5,6,7,8,9]
index:             0 1 2 3 4 5 6 7 8
reverse index: -9 -8 -7 -6 -5 -4 -3 -2 -1

for example :
l[:]
l = range(1,10,1)
l
Out[63]: [1, 2, 3, 4, 5, 6, 7, 8, 9]

l[1:4]
Out[64]: [2, 3, 4]

l[:]
Out[65]: [1, 2, 3, 4, 5, 6, 7, 8, 9]

l
Out[84]: [1, 2, 3, 4, 5, 6, 7, 8, 9]

l[-9:-7]
Out[85]: [1, 2]

Using for iterative list
#The coding come from python org web.
for n in range(2,10,2):
     for x in range(2,n,2):
         if n % x == 0:
             print n, 'equals', x, '*', n/x
             break
     else:
         # loop fell through without finding a factor
         print n, 'is a prime number'
         
"""
the coding explain:
when n == 2, x fetch value from []. so it will into else statement.
when n == 3, x fetch value from [2]. so it will into else statement.
when n == 4 , x fetch value from [2,3].so 4 % 2 == 0 in the one cycle
when n == 5 , x fetch value from [2,3,4]. so it will into else statement.
when n == 6 , x fetch value from [2,3,4,5]. in the one cycle
when n == 7, x fetch value from [2,3,4,5]. so it will into else statement.
…
when n == 9, x fetch value from [2,3,4,5,6,7,8]. so 9 % 3 == 0 in the two cycle
"""