1 # -*- coding: utf-8 -*-
2 """
3 获取实时汇率
4 Created on Fri Oct 18 13:11:40 2013
5
6 @author: alala
7 """
8
9 import httplib
10 import re
11 import MySQLdb
12 import datetime
13
14 URL = 'fx.cmbchina.com' #网站名
15 PATH = '/hq/' #页面路径
16 HOST = 'localhost' #数据库地址（ip）
17 DB = "money" #数据库名称
18 USER = 'root' #数据库用户名
19 PSWD = 'sheet' #数据库密码
20
21 httpClient = None
22
23 try:
24 #抓去网页内容
25 httpClient = httplib.HTTPConnection(URL, 80, timeout=30)
26 httpClient.request('GET', '/hq/')
27 response = httpClient.getresponse()
28 html = response.read()
29 #print html
30
31 #用正则表达式抓去汇率数据
32 reg = re.compile(r"""
33 <tr>\s*<td\s+class="fontbold">\s*(?P<name>\S+)\s*</td>\s* #交易币
34 <td\s+align="center">\s*(?P<unit>\d+)\s*</td>\s* #交易币单位
35 <td\s+align="center"\s+class="fontbold">\s*(?P<base>\S+)\s*</td>\s* #基本币
36 <td\s*class="numberright">\s*(?P<midPrice>\d+\.\d+)\s*</td>\s* #中间价
37 <td\s*class="numberright">\s*(?P<sellPrice>\d+\.\d+)\s*</td>\s* #卖出价
38 <td\s*class="numberright">\s*(?P<buyPrice1>\d+\.\d+)\s*</td>\s* #现汇买入价
39 <td\s*class="numberright">\s*(?P<buyPrice2>\d+\.\d+)\s*</td>\s* #现钞买入价
40 <td\s*align="center">\s*(?P<time>\d+:\d+:\d+)\s*</td>\s* #时间
41 """, re.MULTILINE | re.X)
42 rows = reg.findall(html)
43 #打印汇率数据
44 for r in rows:
45 print ','.join(map(str,r)), '\n'
46
47 #数据库操作
48 #确保mysqldb已经安装，可以用下面的命令安装
49 #pip install MySQL-python
50
51 #建立和数据库系统的连接
52 conn = MySQLdb.connect(host=HOST, user=USER,passwd=PSWD)
53
54 #获取操作游标
55 cursor = conn.cursor()
56 #执行SQL,创建一个数据库.
57 cursor.execute("CREATE DATABASE IF NOT EXISTS " + DB)
58
59 #选择数据库
60 conn.select_db(DB);
61 #执行SQL,创建一个数据表.
62 cursor.execute("""CREATE TABLE IF NOT EXISTS exchange_rate(
63 name VARCHAR(50) COMMENT '交易币' PRIMARY KEY,
64 unit INT COMMENT '交易币单位',
65 base VARCHAR(50) COMMENT '基本币',
66 midPrice FLOAT COMMENT '中间价',
67 sellPrice FLOAT COMMENT '卖出价',
68 buyPrice1 FLOAT COMMENT '现汇买入价',
69 buyPrice2 FLOAT COMMENT '现钞买入价',
70 time DATETIME COMMENT '时间' ) """)
71 records = []
72 for r in rows:
73 (name,unit,base,midPrice,sellPrice,buyPrice1,buyPrice2,time) = r
74 time = datetime.datetime.strptime(datetime.datetime.now().strftime('%Y-%m-%d')
75 + " " + time,'%Y-%m-%d %H:%M:%S')
76 record = (name,int(unit),base,float(midPrice),float(sellPrice),
77 float(buyPrice1),float(buyPrice2),time)
78 records.append(record)
79 #print records
80 #更新汇率
81 cursor.executemany("REPLACE exchange_rate VALUES(%s,%s,%s,%s,%s,%s,%s,%s)"
82 ,records);
83 conn.commit()
84
85 #关闭连接，释放资源
86 cursor.close();
87
88 except Exception,e:
89 print e
90 finally:
91 if httpClient:
92 httpClient.close()
