【python】couchbase稽核脚本

#couchbasescript.py
#!/usr/bin/python
# -*- coding:gbk -*-
#程序功能 验证couchbase中的键是否对应mysql表的值
from couchbase.bucket import Bucket
import ConfigParser
import MySQLdb
import time
import os

#couchbase配置
cp= ConfigParser.ConfigParser()
cp.read('my.conf')
couchbaseip=cp.get('couchbase', 'ip')
couchbaseport=cp.get('couchbase', 'port')
bucketname=cp.get('couchbase', 'buckname')
bucketpasswd=cp.get('couchbase', 'buckpasswd')
#print couchbaseip, buckname,buckpasswd,couchbaseport

#mysql配置
mysqlip=cp.get('mysql', 'ip')
mysqlport=cp.get('mysql', 'port')
mysqlusername=cp.get('mysql', 'username')
mysqldbname_tatrade=cp.get('mysql', 'dbname_tatrade')
mysqlpasswd=cp.get('mysql', 'passwd')


mysqlip1=cp.get('mysql', 'ip1')
mysqlport1=cp.get('mysql', 'port1')
mysqlusername1=cp.get('mysql', 'username1')
mysqldbname_tatrade1=cp.get('mysql', 'dbname_tatrade1')
mysqlpasswd1=cp.get('mysql', 'passwd1')
#print mysqlip, mysqlport, mysqlusername, mysqldbname_tatrade, mysqlpasswd

# 打开couchbase
couchbaseconnect='couchbase://'+couchbaseip+':' + couchbaseport +'/' + buckname
#print couchbaseconnect
#c = Bucket("couchbase://10.2.130.78:8091/assign", password='assign')

try:
    c = Bucket(couchbaseconnect, password=buckpasswd)
except:
    print "couchbase连接失败, 连接信息如下："
    print couchbaseconnect
    print "buchket的密码为：%s" % buckpasswd
    exit()

# 打开数据库连接
try:
    db1 = MySQLdb.connect(mysqlip,mysqlusername,mysqlpasswd,mysqldbname_tatrade)
    db2 = MySQLdb.connect(mysqlip1,mysqlusername1,mysqlpasswd1,mysqldbname_tatrade1)
except:
    print "数据库连接失败!连接信息如下："
    print "IP=%s, PORT=%s, username=%s, dbname=%s, password=%s" % (mysqlip, mysqlport, mysqlusername, mysqldbname_tatrade, mysqlpasswd)
    exit()



# 使用cursor()方法获取操作游标 
cursor1 = db1.cursor()
cursor2 = db2.cursor()

print "查询开始!"
sql= " select d_sysdate from  ta_ttainfo t where t.c_tacode='87' and t.c_tenantid='*' "

cursor1.execute(sql)
dcdate=cursor1.fetchone()


#目录是否存在
s=os.path.exists("result")
if (s == False ):
   os.mkdir("result")

# 打开文件
filename= "result/result_%d.txt" % dcdate
f=open(filename,'w')

f.write("\n")
f.write("                                      账户类核对数据稽核表                                   \n")
f.write("--------------------------------------------------------------------------------------------------")
f.write("\n")
f.write("数据库连接信息如下：\n")
f.write("数据库IP=%s, 端口=%s, 用户名=%s, 数据库=%s, 数据库密码=%s \n" % ( mysqlip, mysqlport, mysqlusername, mysqldbname_tatrade, mysqlpasswd))
f.write("数据库IP=%s, 端口=%s, 用户名=%s, 数据库=%s, 数据库密码=%s \n" % ( mysqlip1, mysqlport1, mysqlusername1, mysqldbname_tatrade1, mysqlpasswd1))
f.write("couchbase的连接信息：%s\n" % couchbaseconnect )
f.write("--------------------------------------------------------------------------------------------------")
f.write("\n")

f.write("检查点：检查确认表中的基金账号，是否在couchbase中存在\n")
############################################################账户申请couchbase查询##################################################33

# SQL 查询语句
sql = ''' SELECT c_fundacco from ta_taccoconfirm 
              where d_cdate= (select d_sysdate from  ta_ttainfo t where t.c_tacode='87' and t.c_tenantid='*' )
                   and c_businflag='81'
                   and c_tacode= '87'
                   and c_tenantid= '*'
                   and c_status='1'  
      '''
f.write("\n")
f.write("查询的SQL语句: %s \n" % sql )
f.write("--------------------------------------------------------------------------------------------------")
f.write("\n")
f.write("\n")



f.write("开始时间: " + time.strftime("%Y-%m-%d %H:%M:%S") + "\n")
f.write("分库%s的检查结果：\n" % mysqldbname_tatrade)
f.write("\n")
try:
   # 执行SQL语句
   cursor1.execute(sql)
   # 获取所有记录列表
   results = cursor1.fetchall()
   #print results
   for row in results:
    c_fundacco = row[0]
        # 打印结果
    #print "账户申请表中的数据fundacco=%s" % (c_fundacco )
    #获取couchbase中的基金账号
        try:
            res = c.get("*_"+c_fundacco)
            #print res.value['custkey']
            res=c.get(res.value['custkey'])
            couchbase_fundacco=res.value['fundacco']
            if (couchbase_fundacco <> c_fundacco):
                #print "不相同的基金账号(确认表的账号=%s, couchbase=%s)" % (couchbase_fundacco, c_fundacco)
                f.write("不相同的基金账号(确认表的账号=%s, couchbase=%s) \n" % (couchbase_fundacco, c_fundacco))
        except:
            #print "在couchbase中查不到的账号账号%s" %(c_fundacco)
            f.write("在couchbase中查不到的账号账号: %s \n" %(c_fundacco))
except Exception,e:
    print Exception, ":", e

f.write("\n\n")
f.write("分库%s的检查结果：\n" % mysqldbname_tatrade1)

try:
   # 执行SQL语句
   cursor2.execute(sql)
   # 获取所有记录列表
   results = cursor2.fetchall()
   #print results
   for row in results:
    c_fundacco = row[0]
        # 打印结果
    #print "账户申请表中的数据fundacco=%s" % (c_fundacco )
    #获取couchbase中的基金账号
        try:
            res = c.get("*_"+c_fundacco)
            #print res.value['custkey']
            res=c.get(res.value['custkey'])
            couchbase_fundacco=res.value['fundacco']
            if (couchbase_fundacco <> c_fundacco):
                #print "不相同的基金账号(确认表的账号=%s, couchbase=%s)" % (couchbase_fundacco, c_fundacco)
                f.write("不相同的基金账号(确认表的账号=%s, couchbase=%s) \n" % (couchbase_fundacco, c_fundacco))
        except:
            #print "在couchbase中查不到的账号账号%s" %(c_fundacco)
            f.write("在couchbase中查不到的账号账号: %s \n" %(c_fundacco))
except Exception,e:
    print Exception, ":", e

db1.close()
db2.close()
f.close

f.write("\n\n")
f.write("结束时间: " + time.strftime("%Y-%m-%d %H:%M:%S") + "\n")
print "查询完成!"

os.system("pause")

#my.cfg
#目录配置文件中可以支持两个分库的设置
[couchbase]
ip=10.2.130.78
port=8091
bucketname=assign
bucketpasswd=assign

[mysql]
ip=10.2.130.78
port=3306
username=root
dbname_tatrade=hs_tatrade
passwd=hstest@1

ip1=10.2.130.78
port1=3306
username1=root
dbname_tatrade1=hs_tatrade1
passwd1=hstest@1

需要安装如下软件清单：
1.pyhton-2.7.12
2.MySQL-python-1.2.5.win32-py2.7
3.couchbase-2.1.2.win32-py2.7
把python/bin的安装目录添加到环境变量中。

注：
所有软件下载地址