#!/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()
整理 www.aygfsteel.com/Good-Game