时间：2021-07-01 10:21:17 帮助过：19人阅读
对应python语句：
1 import json 2 def get_list(sqlLines): 3 list =[] 4 str =‘‘ 5 table_name=‘‘ 6 primary_key=‘‘ 7 foreign_key=‘‘ 8 for line in sqlLines: 9 if ‘PRIMARY KEY‘ in line: 10 primary_key=line[line.find(‘(‘)+1:line.find(‘)‘)] 11 if ‘FOREIGN KEY‘ in line: 12 foreign_key=line[line.find(‘(‘)+1:line.find(‘)‘)] 13 str+=line 14 list.append(str) 15 list.append(primary_key) 16 list.append(foreign_key) 17 return list 18 19 def get_json(list): 20 str=list[0] 21 primary_key=list[1] 22 foreign_key=list[2] 23 table_name=str[str.find(‘CREATE TABLE‘)+len(‘CREATE TABLE‘):str.find(‘(‘)].strip() 24 str= str[str.find(‘(‘)+1:str.rfind(‘)‘)].strip() 25 str =str.lower() 26 str =str.replace(‘ not null‘,‘‘).strip() 27 if ‘primary key‘ in str: 28 str=str[0:str.find(‘primary key‘)] 29 if ‘foreign key‘ in str: 30 str=str[0:str.find(‘foreign key‘)] 31 if ‘datetime year to second‘ in str: 32 str= str.replace(‘datetime year to second‘,‘string‘) 33 34 str_lines =str.splitlines(False) 35 json_list=[] 36 for line in str_lines: 37 json_dict={} 38 lines =line.split() 39 if len(lines)>=2: 40 name=lines[0] 41 type=lines[1][0:len(lines[1])-1] 42 json_dict[‘name‘]=name 43 44 if ‘date‘ in type: 45 type=‘string‘ 46 if ‘decimal‘ in type: 47 type=‘string‘ 48 json_dict[‘type‘]=type 49 if name in primary_key: 50 json_dict[‘primary‘]=1 51 if name in foreign_key: 52 json_dict[‘foreign‘]=1 53 else: 54 continue 55 json_list.append(json_dict) 56 return json_list 57 58 59 60 def load(): 61 sqlStr =open(‘C:/Users/Administrator.PC--20150529IGF/Desktop/prpcitem_car.txt‘) 62 sqlLines =sqlStr.readlines(100000) 63 sql_list =get_list(sqlLines) 64 str =get_json(sql_list) 65 jsonS =json.dumps(str) 66 print(jsonS) 67 load()View Code
---------------------
第二种sql格式：
-- Start of generated script for 10.136.1.5-DB2-LIS (db2inst1) -- Apr-10-2017 at 16:35:18 CREATE TABLE "DB2INST1"."LLCASE" ("CASENO" VARCHAR(20) NOT NULL, "RGTNO" VARCHAR(20), "RGTTYPE" VARCHAR(1) NOT NULL, "RGTSTATE" VARCHAR(2) NOT NULL, "CUSTOMERNO" VARCHAR(24) NOT NULL, "CUSTOMERNAME" VARCHAR(120), "ACCIDENTTYPE" VARCHAR(1), "RECEIPTFLAG" VARCHAR(1), "HOSPITALFLAG" VARCHAR(1), "SURVEYFLAG" VARCHAR(1), "RGTDATE" DATE, "HANDLEDATE" DATE, "CLAIMCALDATE" DATE, "AFFIXGETDATE" DATE, "FEEINPUTFLAG" VARCHAR(1), "INHOSPITALDATE" DATE, "OUTHOSPITALDATE" DATE, "INVALIHOSDAYS" SMALLINT, "INHOSPITALDAYS" SMALLINT, "DIANOSEDATE" DATE, "POSTALADDRESS" VARCHAR(300), "PHONE" VARCHAR(20), "ACCSTARTDATE" DATE, "ACCIDENTDATE" DATE, "ACCIDENTSITE" VARCHAR(60), "DEATHDATE" DATE, "CUSTSTATE" VARCHAR(2), "ACCDENTDESC" VARCHAR(6000), "CUSTBIRTHDAY" DATE, "CUSTOMERSEX" VARCHAR(1), "CUSTOMERAGE" DECIMAL(5, 0), "IDTYPE" VARCHAR(1), "IDNO" VARCHAR(20), "HANDLER" VARCHAR(10), "UWSTATE" VARCHAR(10), "DEALER" VARCHAR(10), "APPEALFLAG" VARCHAR(1), "TOGETHERGET" VARCHAR(1), "GRPDEALFLAG" VARCHAR(1), "GETMODE" VARCHAR(2), "GETINTV" SMALLINT, "CALFLAG" VARCHAR(1), "UWFLAG" VARCHAR(1), "DECLINEFLAG" VARCHAR(1), "ENDCASEFLAG" VARCHAR(1), "ENDCASEDATE" DATE, "MNGCOM" VARCHAR(10) NOT NULL, "OPERATOR" VARCHAR(10) NOT NULL, "MAKEDATE" DATE NOT NULL, "MAKETIME" VARCHAR(8) NOT NULL, "MODIFYDATE" DATE NOT NULL, "MODIFYTIME" VARCHAR(8) NOT NULL, "BANKCODE" VARCHAR(10), "BANKACCNO" VARCHAR(40), "ACCNAME" VARCHAR(120), "CASEGETMODE" VARCHAR(2), "ACCMODIFYREASON" VARCHAR(400), "CASENODATE" DATE, "CASEPROP" CHARACTER(2), "REMARK" VARCHAR(2000), "CANCLEREASON" CHARACTER(2), "CANCLEREMARK" VARCHAR(500), "CANCLER" CHARACTER(10), "CANCLEDATE" DATE, "RIGISTER" CHARACTER(10), "CLAIMER" CHARACTER(10), "UWER" CHARACTER(10), "UWDATE" DATE, "SIGNER" CHARACTER(10), "SIGNERDATE" DATE, "RISKCODE" VARCHAR(10), "OTHERIDTYPE" VARCHAR(2), "OTHERIDNO" VARCHAR(50), "GRPNO" VARCHAR(20), "GRPNAME" VARCHAR(150), "CONTDEALFLAG" VARCHAR(1), "MOBILEPHONE" VARCHAR(15), "PREPAIDFLAG" VARCHAR(1), PRIMARY KEY("CASENO") ); -- End of generated script for 10.136.1.5-DB2-LIS (db2inst1)View Code
对应python语句：
1 import json 2 3 def cleanSql(lines): 4 list=[] 5 str =‘‘ 6 table_name=‘‘ 7 primary_key=‘‘ 8 for line in lines: 9 if ‘CREATE TABLE‘ in line: 10 table_name=line[line.find(‘."‘)+1:line.rfind(‘"‘)] 11 if ‘PRIMARY KEY‘ in line: 12 primary_key=line[line.find(‘"‘)+1:line.rfind(‘"‘)] 13 str += line 14 str = str.lower() 15 if str.startswith(‘create table‘) is False: 16 str =str[str.index(‘create table‘):len(str)] 17 if ‘primary key‘ in str: 18 str=str[0:str.find(‘primary key‘)] 19 if ‘foreign key‘ in str: 20 str=str[0:str.find(‘foreign key‘)] 21 str= str[str.index(‘(‘)+1:] 22 if ‘not null‘ in str: 23 str=str.replace(‘not null‘,‘‘) 24 list.append(table_name) 25 list.append(primary_key.lower()) 26 list.append(str) 27 return list 28 29 def changeType(type): 30 stype=type 31 if ‘character‘ in type: 32 stype =‘string‘ 33 if ‘date‘ in type: 34 stype= ‘string‘ 35 if ‘decimal‘ in type: 36 stype= ‘string‘ 37 return stype 38 39 def get_json(list): 40 primary_key=list[1] 41 str =list[2] 42 lines= str.splitlines(False) 43 json_list=[] 44 for line in lines: 45 json_dict={} 46 name=line[line.find(‘"‘)+1:line.rfind(‘"‘)].strip() 47 type=line[line.rfind(‘"‘)+1:line.find(‘,‘)].strip() 48 type =changeType(type) 49 json_dict[‘type‘]=type 50 json_dict[‘name‘]=name 51 if primary_key==name: 52 json_dict[‘primary‘]=1 53 if len(name)==0: 54 continue 55 json_list.append(json_dict) 56 return json_list 57 58 def load(): 59 schema=open(‘C:/Users/Administrator.PC--20150529IGF/Desktop/data/data/lis/llcase/full/20170401_09_37_01/schema.sql‘) 60 sqlStr =schema.readlines(100000) 61 list=cleanSql(sqlStr) 62 63 jsonStr =get_json(list) 64 jsonS =json.dumps(jsonStr) 65 print(jsonS) 66 load()View Code
python：sql建表语句转换为json
标签：-- discount cat class sage int iter rtti ace
