hly_temp_normal

表 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, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %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, 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))
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

 

posted on 2019-09-10 13:14  名侦探科林  阅读(113)  评论(0)    收藏  举报

导航