python汇率编程_python抓取汇率

1 #-*- coding: utf-8 -*-

2 """

3 获取实时汇率4 Created on Fri Oct 18 13:11:40 20135

6 @author: alala7 """

8

9 importhttplib10 importre11 importMySQLdb12 importdatetime13

14 URL = 'fx.cmbchina.com' #网站名

15 PATH = '/hq/' #页面路径

16 HOST = 'localhost' #数据库地址（ip）

17 DB = "money" #数据库名称

18 USER = 'root' #数据库用户名

19 PSWD = 'sheet' #数据库密码

20

21 httpClient =None22

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

\s*(?P\S+)\s*\s*(?P\d+)\s*\s*(?P\S+)\s*\s*(?P\d+\.\d+)\s*\s*(?P\d+\.\d+)\s*\s*(?P\d+\.\d+)\s*\s*(?P\d+\.\d+)\s*\s*(?P\d+:\d+:\d+)\s*

44 for r inrows: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 inrows:73 (name,unit,base,midPrice,sellPrice,buyPrice1,buyPrice2,time) =r74 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 exceptException,e:89 printe90 finally:91 ifhttpClient:92 httpClient.close()