1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
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))