import json

import pymysql
import time


def save(filename, contents):
    fh = open(filename, 'w', encoding = 'utf-8')
    fh.write(contents)
    fh.close()


mysqldb = pymysql.connect(host = '122.112.141.214', user = 'chyy_demo', passwd = 'Waw8DXh3fJJ-', port = 3306,
                          db = 'shchyy',
                          charset = 'utf8')
mysqlcur = mysqldb.cursor()  # 生成游标对象
# SQL语句
sql = """
SELECT a.number,a.name,b.area FROM (
SELECT number,name FROM shchyy4dw_geca.tb_hosp
UNION
SELECT number,name FROM shchyy4dw.tb_hosp
UNION
SELECT number,name FROM shchyy4dw_qg.tb_hosp) a
left JOIN
shchyy_dw.tmp_tb_hosp_area b
ON a.name=b.name
WHERE a.NAME IS NOT null and b.area in ('上海市','浙江省','江苏省','安徽省');
"""  # id,name,ename
mysqlcur.execute(sql)

i = 0
rows = mysqlcur.fetchall()
timestamp = int(time.time())

hosp_list = []
for row in rows:  # 循环处理所有记录
    jsonS = ""
    i = i + 1
    jsonS = jsonS + "{\"_id\":{\"$oid\":\"" + str(timestamp) + str(i).rjust(14,
                                                                            '0') + "\"}"  # 5e,00,30,d0,/2b,85,a7,/76,e1,9c,50,98
    jsonS = jsonS + ",\"_class\":\"com.ewell.bean.HospitalBean\""  #
    jsonS = jsonS + ",\"hospitalId\":\"" + str(i).rjust(7, '0') + "-" + str(timestamp) + "\""  # 0000001-1577070800036
    jsonS = jsonS + ",\"hospitalName\":\"" + row[1] + "\""  # 上海市浦东新区公利医院
    jsonS = jsonS + ",\"shortName\":\"\""  #
    jsonS = jsonS + ",\"hospitalNumber\":\"" + str(i).rjust(7, '0') + "\""  # 0000001
    jsonS = jsonS + ",\"ownership\":\"公立\""  #
    jsonS = jsonS + ",\"mechanismCategory\":\"综合\""  #
    jsonS = jsonS + ",\"hospitalRank\":\"二级\""  #
    jsonS = jsonS + ",\"hospitalHierarchy\":\"甲等\""  #
    jsonS = jsonS + ",\"provinceId\":\"\""  #
    jsonS = jsonS + ",\"provinceName\":\"\""  #
    jsonS = jsonS + ",\"prefectureId\":\"\""  #
    jsonS = jsonS + ",\"prefectureName\":\"\""  #
    jsonS = jsonS + ",\"handler\":\"tsh\""  #
    jsonS = jsonS + ",\"isUse\":{\"$numberInt\":\"1\"}"  #
    jsonS = jsonS + ",\"createTime\":\"" + time.strftime('%Y-%m-%d %H:%M:%S',
                                                         time.localtime(timestamp)) + "\""  # 2019-12-23 11:13:20
    jsonS = jsonS + ",\"createUser\":{\"userId\":\"superadmin\",\"name\":\"超级管理员\"}}"  #
    
    data = json.loads(jsonS)
    hosp_list.append(data)
    print(data)

save('/Users/Bureaux/Documents/workspace/PyCharmProjects/mongodb2mysql/data/tb_hosp_shchyy/all.json',
     json.dumps(hosp_list, ensure_ascii = False))