操作mysql（获取列名与值）--不同服务器

__description__='Finding data in different servers'

import pymysql
import json
import decimal
import datetime

db1=pymysql.connect(host="服务器地址",port=3306,user='用户名',password='密码',db='数据库名称',charset='utf8')
db2=pymysql.connect(host='服务器地址',port=3306,user='用户名',password='密码',db='数据库名称',charset='utf8')
cursor1 = db1.cursor()
cursor2 = db2.cursor()

sql_1 = "SELECT * FROM member WHERE phone LIKE '18%'"
cursor1.execute(sql_1)

sql_2="select * from osc_order where customer_id='4700%' and status_name in('已收') order by created_at DESC "
cursor2.execute(sql_2)

class DecimalEncoder(json.JSONEncoder):
    def default(self,obj):
        if isinstance(obj,decimal.Decimal):#decimal类型转换，使其可以转换为json格式数据
            return float(obj)
        elif isinstance(obj,datetime.datetime):#datetime类型转换，使其可以转换为json格式数据
            return obj.__str__()
        return super(DecimalEncoder,self).default(obj)

def sqltest():
    index1=cursor1.description #获取列名属性值（设计表）
    print(index1)
    result1=[]
    for res in cursor1.fetchall():
        row={}
        for i in range(len(index1)):
            row[index1[i][0]]=res[i]
        result1.append(row)
    print(json.dumps(result1,indent=4,ensure_ascii=False,sort_keys=True))
    # for i in result1:
    #     print(json.dumps(i,indent=4))

    print('\n')

    index2=cursor2.description
    print(index2)
    result2=[]
    for res in cursor2.fetchall():
        row={}
        for i in range(len(index2)):
            row[index2[i][0]]=res[i]
        result2.append(row)
    print(json.dumps(result2,cls=DecimalEncoder,indent=4,ensure_ascii=False,sort_keys=True))
    # for i in result2:
    #     print(json.dumps(i,cls=DecimalEncoder,indent=4,ensure_ascii=False,sort_keys=True))
    # for i in result2:
    #     for key in i:
    #         print(i[key])

if __name__=='__main__':
    sqltest()
    db1.close()
    db2.close()