Just in case someone might find this useful I was download Bank of Canada
FX information and scrubbing the information for injection into a database
with DAL. See code below:
import csv
import requests
import datetime as dt
from datetime import timedelta
from datetime import datetime
import os
import sys
from itertools import izip_longest
#import web2py DAL
from gluon.sql import DAL, Field
from gluon.validators import *
import pprint
# CSVFile : This is the name of the file on disk to store the newly produced
# csv file.
CSVFile = 'temp.csv'
def main():
# The protocol and full path to our database file.
db = DAL('sqlite://pricing_db.sqlite',
folder='D:\Anaconda\envs\web2py\web2py\database')
# Define the table, note that the field "id" is automatic.
db.define_table('cad_fx',
Field('date', 'datetime'),
Field('usd', 'double'),
Field('convrate', 'double'),
Field('recrate', 'double'))
# Set date information
t = dt.date.today()
startDate = (t - timedelta(days=0)).strftime("%Y-%m-%d")
endDate = t.strftime("%Y-%m-%d")
tenDate = (t - timedelta(days=(365*10))).strftime("%Y-%m-%d")
# Call string composition function
s = BOCFX_url_string(tenDate, startDate, endDate)
# Call download file function
get_File(s, CSVFile)
# Remove string from list
string_to_remove = 'Bank holiday'
price_list = format_list(CSVFile, string_to_remove)
for item in price_list:
db.cad_fx.update_or_insert(date=datetime.strptime(item[0],'%Y-%m-%d'),
usd=float(item[1]),
convrate=float(item[2]),
recrate=float(item[3]))
db.commit()
def BOCFX_url_string(tDate, fDate, lDate):
"""Returns an http string composed of the start date [fDate], the end
date [lDate]
and the ten month prior date from the start date [tDate] for
downloading the
Bank of Canada USD foreign exchange rate.
INPUT:
tDate = Date ten years back from start date.
fDate =
"""
BOCFX_string =
('http://www.bankofcanada.ca/stats/results/csv?sF=LOOKUPS_CAD' +
'&lP=lookup_currency_converter.php&sR={0}&sTF=to&sT=_0101&co=1.00&dF=' +
'{1}&dT={2}'
).format(tDate, fDate, lDate)
return(BOCFX_string)
def get_File(urls, write_file):
"""Takes http url string to a file and file location and writes to disk.
"""
try:
f = requests.get(urls, allow_redirects=True)
with open(write_file, 'wb') as xfile:
for chunk in f.iter_content():
xfile.write(chunk)
except requests.exceptions.RequestException as e:
print e
sys.exit(1)
def format_list(write_file, s):
# Open CSV file and input into a list of tuples
with open(write_file, 'Ur') as f:
data = list(tuple(rec) for rec in csv.reader(f, delimiter=','))
# Removes unwanted empty elements
data = [list(x for x in y if x) for y in data]
# Remove unwanted rows
data.pop(0)
data.pop(0)
header = data.pop(0)
data.pop()
data.reverse()
#data.insert(0, header)
# Remove bank holidays
d = list()
for item in data:
for subitem in item:
if subitem == s:
d.append(data.index(item))
break
data = [i for j, i in enumerate(data) if j not in d]
return(data)
if __name__ == '__main__':
main()
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
