# coding=utf-8

import pymysql

import xlrd

from xlutils.copyimport copy

class testclass(object):

def __init__(self):

db = pymysql.connect(host="ip", user="user", password="password", db="db", port=3306)

db_dqz = pymysql.connect(host="ip", user="user", password="password", db="db", port=3306)

# 使用cursor()方法获取操作游标

        cur = db.cursor()

cur_dqz = db_dqz.cursor()

try:

# 打开表格

            workbook = xlrd.open_workbook('courtguide.xlsx')

# 定位sheet

            table1 = workbook.sheet_by_index(0)

# 获取总行数

            nrowsnum =int(table1.nrows)

newexcel = copy(workbook)

table2 = newexcel.get_sheet(0)

for iin range(nrowsnum -1):

sql_x = table1.cell_value(i +1, 2)

sql_y = table1.cell_value(i +1, 4)

#print(sql_x)

                # 执行sql语句

                cur.execute(sql_x)

cur_dqz.execute(sql_y)

results=cur.fetchone()

results_dqz = cur_dqz.fetchone()

#print(results[0],results_dqz[0])

#print(type(results), type(results_dqz))

                table2.write(i +1, 3, '%s' %results)

table2.write(i +1, 5, '%s' %results_dqz)

if results[0] == results_dqz[0]:

table2.write(i+1, 6, '相同')

else:

res = results[0] -results_dqz[0]

proportion=res/results[0]

res1=(results[0] -results_dqz[0])/results[0]

print(res,proportion)

#table2.write(i + 1, 6, '不相同=')

                  table2.write(i+1, 6, '不相同，误差为：%s'%proportion)

except  Exception  as e:

print(e)

print("请求过程中发生异常")

newexcel.save('newCourtGuide.xls')

print("执行完成")

# 关闭数据库连接

        db.close()

# 关闭数据库连接

        db_dqz.close()

#创建类对象，自动执行类中初始化方法

t = testclass()