On Monday, April 7, 2014 3:57:12 PM UTC-7, Trent Telfer wrote:
>
> 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:
>
>
Cool! Thanks for sharing!
/dps
> 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.
