py 数据库

# !/usr/bin/python
# coding=UTF-8
# sql 参考
# http://www.kitebird.com/articles/pydbapi.html
import  cx_Oracle
import  re,os,sys

logTxt = " ./log "

dataFile  =   " ./data.txt "
fileCharset  =   " GBK "
dbCharset  =   " GBK "

username  =   " .... "
userpwd  =   " .... "
host  = " .... "
dbname = " .... "
# dbname="..."

MuById_SQL = " select count(*) from mn00_tbl_music tm where tm.id=:mid "

CRById_SQL = " select tc.music_id from mn05_tbl_copyright tc where tc.copyright_id=:crid "
CRById_In_Music_SQL = " update  mn05_tbl_copyright  set music_id=:mid where copyright_id=:crid "

CRByMNameAndAName_SQL = "  select tc.copyright_id from mn05_tbl_copyright tc where  (tc.actor_src_name=':acName' and tc.music_src_name=':muName' ) or (tc.actor_name=':acName' and tc.music_name=':muName')  " ;


#  60056601146|失意酒(童安格)|失意酒(童安格)|10030149
#  CRID | 编辑后歌曲（歌手）|歌曲（歌手） |MID
def  action(strs):
    result  =  selectSql( subSql(MuById_SQL,  " mid " , strs[ 3 ]) )
     if ( int(result[0][0]) > 0):
        actionCR(strs[0],strs[ 3 ])
     if ( strs[ 1 ]  and  strs[ 1 ] != ""  ):
        actionName(strs[ 1 ],strs[ 3 ])
     if ( strs[ 2 ]  and  strs[ 2 ] != ""  ):
        actionName(strs[ 2 ],strs[ 3 ])


def  actionName(mu_ac,mid):
    p  =  re.compile( " (.*)\((.*)\) " )
    m  =  p.match( mu_ac )
     if (m):
        crmaSql = subSql(CRByMNameAndAName_SQL, " muName " , m.group( 1 ))
        crmaSql = subSql(crmaSql, " acName " ,m.group( 2 ))
         for  row  in  selectSql( crmaSql ):
            actionCR(row[0]. __str__ () ,mid)
         # count = cursor.rowcount
    
def  actionCR(crid,mid):
    result  =  selectSql( subSql(CRById_SQL,  " crid " , crid )  ) 
     if ( result[0][0] == None  or  int(result[0][0]) == 0):
        upCr = subSql(CRById_In_Music_SQL, " crid " ,crid)
        upCr = subSql(upCr, " mid " ,mid)
         if (execSql(upCr) > 0):
            _log( ' match crid= ' + crid + '  muid= ' + mid)
def  subSql(strSql,subName,subValue):
    p  =  re.compile( " : " + subName)
     return  p.sub( subValue, strSql)
            
def  selectSql(sql):
     print   ' select= ' + sql
    cursor.execute(sql.encode(dbCharset))
     return  cursor.fetchall()

def  execSql(sql):
     print   ' exec= ' + sql
    cursor.execute(sql.encode(dbCharset))
    connection.commit()
     return  cursor.rowcount

def  _log(show):
     print  show
    log  =   ' echo  ' + show + '  >>  ' + logTxt
    os.system(log)
    
    
try :
    connStr = username + " / " + userpwd + " @ " + dbname
     print  connStr
    connection  =  cx_Oracle.connect(connStr) 
    cursor  =  connection.cursor()
    file  =  open(dataFile)
     if (len(sys.argv) == 2 ):
        startRow = int(sys.argv[ 1 ])
     else :
        startRow = 1
    row  =   1
     for  str  in  file.readlines():
         if (row >= startRow):
            str  =  re.compile( " \n " ).sub(  "" , str )
            strs  =  unicode(str, fileCharset).split( " | " )
            _log( ' start row= ' + row. __str__ () + '  crid= ' + strs[0] + '  muid= ' + strs[ 3 ])
             try :
                action(strs)
             except :
                _log( ' err row= ' + row. __str__ ())
            _log( ' end row= ' + row. __str__ () + '  crid= ' + strs[0] + '  muid= ' + strs[ 3 ])
        row += 1
finally :
    file.close()
    cursor.close()
    connection.close()