python：sql建表语句转换为json

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了python：sql建表语句转换为json，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含10733字，纯文字阅读大概需要16分钟。

内容图文

CREATE TABLE 2 prpcitem_car 3 ( 4 proposalno CHAR(22) NOT NULL, 5 itemno DECIMAL(8,0) NOT NULL, 6 riskcode CHAR(3) NOT NULL, 7 insuredtypecode CHAR(2), 8 carinsuredrelation CHAR(1), 9 clausetype CHAR(3), 10 licenseno VARCHAR(20), 11 licensetype CHAR(3), 12 licensecolorcode CHAR(2), 13 nonlocalflag CHAR(1), 14 licenseflag CHAR(1), 15 vehicletype CHAR(4), 16 carkindcode CHAR(3), 17 hkflag CHAR(1), 18 hklicenseno CHAR(20), 19 engineno VARCHAR(30), 20 vinno VARCHAR(18), 21 frameno VARCHAR(30), 22 runareacode CHAR(10), 23 runareaname VARCHAR(60), 24 runmiles DECIMAL(14,2), 25 enrolldate DATE, 26 useyears DECIMAL(15,255), 27 vehiclecategory CHAR(3), 28 columvehiclecategoryn_68 CHAR(3), 29 modelcodealias VARCHAR(100), 30 modelcode CHAR(14), 31 brandname VARCHAR(200) NOT NULL, 32 aliasname VARCHAR(100), 33 countrynature CHAR(2), 34 countrycode CHAR(3), 35 usenaturecode CHAR(3), 36 businessclasscode CHAR(1), 37 seatcount DECIMAL(15,255), 38 toncount DECIMAL(10,3), 39 exhaustscale DECIMAL(8,4), 40 cylindercount INTEGER, 41 iscriterion INTEGER, 42 lossratio CHAR(5), 43 salesname VARCHAR(50), 44 salesnumber VARCHAR(20), 45 salesphone VARCHAR(15), 46 carcounts INTEGER, 47 isdropinvisitinsure INTEGER, 48 carloteququality DECIMAL(14,2), 49 colorcode CHAR(6), 50 safedevice CHAR(30), 51 coefficient1 DECIMAL(8,4), 52 coefficient2 DECIMAL(8,4), 53 coefficient3 DECIMAL(8,4), 54 othernature VARCHAR(10), 55 ratecode CHAR(8), 56 rationname VARCHAR(60), 57 makedate DATE, 58 carusage VARCHAR(20), 59 currency CHAR(3), 60 purchaseprice DECIMAL(14,2), 61 actualvalue DECIMAL(14,2), 62 invoiceno CHAR(20), 63 carloanflag CHAR(1), 64 cardealercode CHAR(16), 65 cardealername VARCHAR(120), 66 remark VARCHAR(40), 67 carid CHAR(10), 68 versionno CHAR(4), 69 monopolyflag CHAR(1), 70 monopolycode VARCHAR(22), 71 monopolyname VARCHAR(80), 72 newcarflag CHAR(1), 73 loanvehicleflag CHAR(1), 74 transfervehicleflag CHAR(1), 75 transferdate DATE, 76 operationarea VARCHAR(5), 77 inserttimeforhis DATETIME YEAR TO SECOND, 78 operatetimeforhis DATETIME YEAR TO SECOND, 79 startsitename VARCHAR(255), 80 discounttype VARCHAR(255), 81 drivertype VARCHAR(255), 82 endsitename VARCHAR(255), 83 carcusttype CHAR(2), 84 nodamageyears VARCHAR(4), 85 modeldemandno VARCHAR(50), 86 energytype CHAR(1), 87 fueltype CHAR(2), 88 carprooftype CHAR(2), 89 carproofno CHAR(50), 90 carproofdate DATE, 91 certificatedate DATE, 92 isremote CHAR(1), 93 fullendor CHAR(1), 94 issilageharvester CHAR(1), 95 flag CHAR(10), 96 licenseno1 CHAR(10), 97 licenseno2 VARCHAR(20), 98 licenseno3 CHAR(10), 99 vehiclebrand VARCHAR(20), 100 vehicletypedescription VARCHAR(5), 101 groupcode VARCHAR(50), 102 groupstartdate DATE, 103 groupenddate DATE, 104 PRIMARY KEY (proposalno, itemno) CONSTRAINT pk_citem_car, 105 FOREIGN KEY (proposalno) REFERENCES prpcmain (proposalno) CONSTRAINT fkb28a3afd9b27a158 106 ); View Code

对应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   

本文系统来源：http://www.cnblogs.com/hy928302776/p/6770663.html

内容总结

以上是互联网集市为您收集整理的python：sql建表语句转换为json全部内容，希望文章能够帮你解决python：sql建表语句转换为json所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---