hly_temp_normal

发布时间：2020-12-20 10:45:50 所属栏目：Python 来源：网络整理

导读：表 hly_temp_normal CREATE TABLE hly_temp_normal ( id int NOT NULL PRIMARY KEY AUTO_INCREMENT,stnid char ( 11 ), month tinyint , day tinyint ,value1 VARCHAR ( 5 ),value2 VARCHAR ( 5 ),value3 VARCHAR ( 5 ),value4 VARCHAR ( 5 ),value5 VARCHAR

表 hly_temp_normal

CREATE TABLE hly_temp_normal (
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,stnid char(11),month tinyint,day tinyint,value1 VARCHAR(5),value2 VARCHAR(5),value3 VARCHAR(5),value4 VARCHAR(5),value5 VARCHAR(5),value6 VARCHAR(5),value7 VARCHAR(5),value8 VARCHAR(5),value9 VARCHAR(5),value10 VARCHAR(5),value11 VARCHAR(5),value12 VARCHAR(5),value13 VARCHAR(5),value14 VARCHAR(5),value15 VARCHAR(5),value16 VARCHAR(5),value17 VARCHAR(5),value18 VARCHAR(5),value19 VARCHAR(5),value20 VARCHAR(5),value21 VARCHAR(5),value22 VARCHAR(5),value23 VARCHAR(5),value24 VARCHAR(5)
);

下载数据集

https://www1.ncdc.noaa.gov/pub/data/normals/1981-2010/products/hourly/hly-temp-normal.txt?

insert_hly.py

 1 #!/usr/bin/python
 2 
 3 import MySQLdb
 4 from optparse import OptionParser
 5 
 6 parser = OptionParser()
 7 parser.add_option("-f","--file",dest="filename", 8                           help="FILE contains data to be inserted",metavar="FILE")
 9 parser.add_option("-t","--table",dest="tablename",10                           help="TABLE to be inserted",metavar="TABLE")
11 (options,args) = parser.parse_args()
12 
13 if not options.filename:   # if filename is not given
14     parser.error(‘Filename not given‘)
15 
16 if not options.tablename:   # if table is not given
17     parser.error(‘Tablename not given‘)
18 
19 filename = options.filename
20 table = options.tablename
21 
22 conn = MySQLdb.connect (host = "db_host",23                        user = "db_user",24                        passwd = "db_password",25                        db = "db_database")
26 
27 sql = "insert into " + table + " values ( NULL,%s,%s )"
28 
29 cursor = conn.cursor ()
30 
31 f = open(filename,"r")
32 for line in f:
33     stationid = line[0:11].strip()
34     month = int(line[12:14].strip())
35     day = int(line[15:17].strip())
36     value1 = line[18:24].strip()
37     value2 = line[25:31].strip()
38     value3 = line[32:38].strip()
39     value4 = line[39:45].strip()
40     value5 = line[46:52].strip()
41     value6 = line[53:59].strip()
42     value7 = line[60:66].strip()
43     value8 = line[67:73].strip()
44     value9 = line[74:80].strip()
45     value10 = line[81:87].strip()
46     value11 = line[88:94].strip()
47     value12 = line[95:101].strip()
48     value13 = line[102:108].strip()
49     value14 = line[109:115].strip()
50     value15 = line[116:122].strip()
51     value16 = line[123:129].strip()
52     value17 = line[130:136].strip()
53     value18 = line[137:143].strip()
54     value19 = line[144:150].strip()
55     value20 = line[151:157].strip()
56     value21 = line[158:164].strip()
57     value22 = line[165:171].strip()
58     value23 = line[172:178].strip()
59     value24 = line[179:185].strip()
60 
61 #    print sql % (table,stationid,month,day,value1,value2,value3,value4,value5,value6,value7,value8,value9,value10,value11,value12,value13,value14,value15,value16,value17,value18,value19,value20,value21,value22,value23,value24)
62     cursor.execute (sql,(stationid,value24))
63 
64 
65 f.close()
66 cursor.close ()
67 conn.close ()

View

shell加载数据

 python insert_hly.py -f hly-temp-normal.txt -t hly_temp_normal

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!