成果，概列九月份重要指標發佈日：
('2012-09-04', 'Manufacturing ISM Report', 'US')
('2012-09-05', '消費者物價指數', 'TW')
('2012-09-07', 'Total Nonfarm Payroll Employment', 'US')
('2012-09-12', 'Wholesale Trade Report', 'US')
('2012-09-13', 'Producer Price Index', 'US')
('2012-09-14', 'Industrial Production and Capacity Utilization', 'US')
('2012-09-14', 'Consumer Price Index', 'US')
('2012-09-14', 'Advance Monthly Sales for Retail and Food Services', 'US')
('2012-09-25', 'Conference Board Consumer Confidence Index', 'US')
('2012-09-27', '景氣指標－景氣對策信號', 'TW')
細節：SQLite schema，單獨抽出來，不要綁在程式裡頭。
drop table if exists Schedule;
drop table if exists ReleaseInfo;
drop table if exists Code;
create table if not exists Code
(
id integer primary key,
creation_time datetime default current_timestamp,
name text not null,
nation text,
unique (name, nation) on conflict ignore
);
create table if not exists ReleaseInfo
(
id integer primary key,
creation_time datetime default current_timestamp,
code_id integer not null,
period text,
coverage integer,
url text,
description text,
foreign key (code_id) references Code (id),
unique (code_id, period, coverage, url) on conflict ignore
);
create table if not exists Schedule
(
id integer primary key,
creation_time datetime default current_timestamp,
code_id integer not null,
release_date datetime,
description text,
foreign key (code_id) references Code (id),
unique (code_id, release_date) on conflict ignore
);
使用 Sqliteman - SQLite database made easy 操作界面。
為了方面使用，自己用 Python 兜操作界面：
import logging
import os
import sqlite3
class IndicatorDao():
def __init__(self):
self._dbfile = './db/indicator.db'
self._schedule = 'Schedule'
self._release_info = 'ReleaseInfo'
self._code = 'Code'
self._logger = logging.getLogger()
def InsertSchedule(self, indicator, nation, dates):
conn = sqlite3.connect(self._dbfile)
cursor = conn.cursor()
cursor.execute('''insert or ignore into
%s (name, nation) values ('%s', '%s')
''' % (self._code, indicator, nation))
cursor.execute('''select id from %s where name="%s" and nation="%s"
''' % (self._code, indicator, nation))
code = cursor.fetchone()[0]
for date in dates:
cursor.execute('''insert into
%s (code_id, release_date) values ('%s', '%s')
''' % (self._schedule, code, date))
conn.commit()
cursor.close()
conn.close()
def UpdateSchedule(self, indicator, nation, old_date, new_date):
conn = sqlite3.connect(self._dbfile)
cursor = conn.cursor()
cursor.execute('''select id from %s where name="%s" and nation="%s"
''' % (self._code, indicator, nation))
code_id = cursor.fetchone()[0]
cursor.execute('''update %s set release_date="%s"
where code_id=%d and nation="%s" and release_date="%s"
''' % (self._schedule, new_date, code_id, nation, old_date))
conn.commit()
cursor.close()
conn.close()
def DeleteSchedule(self, indicator, date):
conn = sqlite3.connect(self._dbfile)
cursor = conn.cursor()
cursor.execute('''select id from %s where name="%s" and nation="%s"
''' % (self._code, indicator, nation))
code_id = cursor.fetchone()[0]
cursor.execute('''delete from %s
where code_id=%d and nation="%s" and release_date="%s"
''' % (self._schedule, code_id, nation, date))
conn.commit()
cursor.close()
conn.close()
def GetSchedule(self, begin_date, end_date):
sql_cmd = '''select %s.release_date, %s.name, %s.nation
from %s inner join %s on %s.code_id = %s.id where 1=1
''' % (self._schedule, self._code, self._code,
self._schedule, self._code,
self._schedule, self._code)
if begin_date:
sql_cmd += ''' and release_date >= "%s"''' % begin_date
if end_date:
sql_cmd += ''' and release_date <= "%s"''' % end_date
sql_cmd += ''' order by release_date'''
self._logger.debug(sql_cmd)
conn = sqlite3.connect(self._dbfile)
cursor = conn.cursor()
rv = [ _ for _ in cursor.execute(sql_cmd)]
cursor.close()
conn.close()
return rv
def InsertReleaseInfo(self,
indicator,
nation,
period,
coverage,
url,
description):
conn = sqlite3.connect(self._dbfile)
cursor = conn.cursor()
cursor.execute('''insert or ignore into
%s (name, nation) values ('%s', '%s')
''' % (self._code, indicator, nation))
cursor.execute('''select id from %s where name="%s" and nation="%s"
''' % (self._code, indicator, nation))
code = cursor.fetchone()[0]
cursor.execute('''insert or replace into
%s (code_id, period, coverage, url, description)
values ('%s', '%s', '%s', '%s', '%s')
''' % (self._release_info,
code,
period,
coverage,
url,
description))
conn.commit()
cursor.close()
conn.close()
def GetReleaseInfo(self):
sql_cmd = '''select %s.name, %s.nation, %s.url
from %s inner join %s on %s.code_id = %s.id
''' % (self._code, self._code, self._release_info,
self._release_info, self._code,
self._release_info, self._code)
self._logger.debug(sql_cmd)
conn = sqlite3.connect(self._dbfile)
cursor = conn.cursor()
rv = [ _ for _ in cursor.execute(sql_cmd)]
cursor.close()
conn.close()
return rv
初始化：
import logging
import sys
import indicator_dao
import base.logger
def main():
base.logger.config_root(level=logging.DEBUG)
InsertKnownSchedule()
for date in GetSchedule(None, None): print(date)
InsertKnownReleaseInfo()
def InsertKnownSchedule():
d = indicator_dao.IndicatorDao()
d.InsertSchedule(
'Industrial Production and Capacity Utilization',
'US',
['2012-09-14', '2012-10-16', '2012-11-15', '2012-12-14']
)
d.InsertSchedule(
'Producer Price Index',
'US',
['2012-09-13', '2012-10-12', '2012-11-14', '2012-12-13']
)
d.InsertSchedule(
'Consumer Price Index',
'US',
['2012-09-14', '2012-10-16', '2012-11-15', '2012-12-14']
)
d.InsertSchedule(
'Total Nonfarm Payroll Employment',
'US',
['2012-09-07', '2012-10-05', '2012-11-02', '2012-12-07']
)
d.InsertSchedule(
'Wholesale Trade Report',
'US',
['2012-09-12', '2012-10-10', '2012-11-09', '2012-12-11']
)
d.InsertSchedule(
'Manufacturing ISM Report',
'US',
['2012-09-04']
)
d.InsertSchedule(
'Conference Board Consumer Confidence Index',
'US',
['2012-08-28', '2012-09-25']
)
d.InsertSchedule(
'消費者物價指數',
'TW',
['2012-09-05', '2012-10-05', '2012-11-05', '2012-12-05']
)
d.InsertSchedule(
'景氣指標－景氣對策信號',
'TW',
['2012-08-27', '2012-09-27', '2012-10-26', '2012-11-27', '2012-12-27',
'2013-01-28', '2013-02-27', '2013-03-27', '2013-04-26', '2013-05-27',
'2013-06-27']
)
d.InsertSchedule(
'Advance Monthly Sales for Retail and Food Services',
'US',
['2012-08-14', '2012-09-14', '2012-10-15', '2012-11-14', '2012-12-13']
)
def InsertKnownReleaseInfo():
d = indicator_dao.IndicatorDao()
d.InsertReleaseInfo(
'Industrial Production and Capacity Utilization',
'US', 'monthly', '1',
'http://www.federalreserve.gov/releases/g17/default.htm', None
)
d.InsertReleaseInfo(
'Producer Price Index',
'US', 'monthly', '1',
'http://www.bls.gov/schedule/news_release/2012_sched.htm', None
)
d.InsertReleaseInfo(
'Consumer Price Index',
'US', 'monthly', '1',
'http://www.bls.gov/schedule/news_release/2012_sched.htm', None
)
d.InsertReleaseInfo(
'Total Nonfarm Payroll Employment',
'US', 'monthly', '1',
'http://www.bls.gov/schedule/news_release/2012_sched.htm', None
)
d.InsertReleaseInfo(
'Wholesale Trade Report',
'US', 'monthly', '2',
'http://www2.census.gov/wholesale/pdf/MWTS_Release_Schedule.pdf', None
)
d.InsertReleaseInfo(
'Manufacturing ISM Report',
'US', 'monthly', '1',
'http://www.ism.ws/ISMReport/MfgROB.cfm?navItemNumber=12942', None
)
d.InsertReleaseInfo(
'Conference Board Consumer Confidence Index',
'US', 'monthly', '1',
'http://www.conference-board.org/data/consumerconfidence.cfm', None
)
d.InsertReleaseInfo(
'消費者物價指數',
'TW', 'monthly', '1',
'http://win.dgbas.gov.tw/dgbas03/bs7/calendar/calendar.asp?SelOrg=1', None
)
d.InsertReleaseInfo(
'景氣指標－景氣對策信號',
'TW', 'monthly', '1',
'http://win.dgbas.gov.tw/dgbas03/bs7/calendar/calendar.asp', None
)
d.InsertReleaseInfo(
'Advance Monthly Sales for Retail and Food Services',
'US', 'monthly', '1',
'http://www.esa.doc.gov/sites/default/files/ei/documents/2012/2012_economic_release_calendar.pdf', None
)
def GetSchedule(begin_date, end_date):
d = indicator_dao.IndicatorDao()
return d.GetSchedule(begin_date, end_date)
if __name__ == '__main__':
sys.exit(main())
沒有留言:
張貼留言