CODEWITHSUNDEEP
databasef#
nickfinity
nickfinity

Reputation: 1119

Creating insert statements with F#

I use F# once every few months or so and in between it seems I forget everything, so I hope you'll excuse my ignorance. My code below is pulling data from Yahoo. It's a good example that represents what I need to do. The first row returned has the column headings. I need to take the data (the tail of the list) and insert it into the database. What's the best way to generate an insert statement based on the column headings returned (the column headings match the database column names)?

In the example below dataWithHeaders.[0] will contain "Date,Open,High,Low,Close,Volume,Adj Close." Should I just take that string and put brackets around the headings to create the insert? Then in insertData add the value as a parameter? Is there a more elegant solution?

let url = System.String.Format("http://ichart.finance.yahoo.com/table.csv?s={0}&g=d&ignore=.csv", "FB")

let splitLineIntoArray (line : string) = 
    line.Split(",".ToCharArray())

let insertData (data : string[]) =
    // insert data
    ()

let client = new WebClient()
let dataWithHeaders = 
    client.DownloadString(url).Split(Environment.NewLine.ToCharArray())

let data =
    dataWithHeaders
    |> Array.toList
    |> List.tail
    |> List.map(splitLineIntoArray)
    |> List.iter insertData

Upvotes: 2

Views: 1534

Answers (2)

The Internet
The Internet

Reputation: 8103

EDIT: Type Providers might be a good way to go, but SqlBulkCopy is def. known for its simplicity.

Type Provider Code for Insert: http://msdn.microsoft.com/en-us/library/hh361033(v=vs.110).aspx#BKMK_UpdateDB

type dbSchema = SqlDataConnection<"Data Source=MYSERVER\INSTANCE;Initial Catalog=MyDatabase;Integrated Security=SSPI;">
let db = dbSchema.GetDataContext()

// Enable the logging of database activity to the console.
db.DataContext.Log <- System.Console.Out

let newRecord = new dbSchema.ServiceTypes.Table1(Id = 100,
                                                 TestData1 = 35, 
                                                 TestData2 = 2.0,
                                                 Name = "Testing123")
let newValues =
    [ for i in [1 .. 10] ->
          new dbSchema.ServiceTypes.Table3(Id = 700 + i,
                                           Name = "Testing" + i.ToString(),
                                           Data = i) ]
// Insert the new data into the database.
db.Table1.InsertOnSubmit(newRecord)
db.Table3.InsertAllOnSubmit(newValues)
try
    db.DataContext.SubmitChanges()
    printfn "Successfully inserted new rows."
with
   | exn -> printfn "Exception:\n%s" exn.Message

I did something similar. Actually this code I wrote while watching Luca Bolognese give a presentation on F#. This actually will scrape yahoo's feed and return the standard dev. and variance on stock prices.

Full project here: https://github.com/djohnsonm/Stock-Ticker-App

open System.Net
open System.IO

let internal loadPrices ticker = async {
let url = @"http://ichart.finance.yahoo.com/table.csv?s=" + ticker + "&d=6&e=22&f=2011&g=d&a=2&b=13&c=1986&ignore=.csv"
let req = WebRequest.Create(url)
let resp = req.GetResponse()
let stream = resp.GetResponseStream()
let reader = new StreamReader(stream)
let csv = reader.ReadToEnd()
let prices = 
    csv.Split([|'\n'|])
    |> Seq.skip 1
    |> Seq.map (fun line -> line.Split([|','|]))
    |> Seq.filter(fun values -> values |> Seq.length = 7)
    |> Seq.map(fun values ->
        System.DateTime.Parse(values.[0]),
        float values.[6])
return prices}

type StockAnalyzer (lprices, days) =
    let prices =
        lprices
        |> Seq.map snd
        |> Seq.take days
    static member GetAnalyzers (tickers, days) =
        tickers
        |> Seq.map loadPrices
        |> Async.Parallel
        |> Async.RunSynchronously
        |> Seq.map (fun prices -> new StockAnalyzer(prices, days))
    member s.Return =
        let lastPrice = prices |> Seq.nth 0
        let startPrice = prices |> Seq.nth(days-1)
        lastPrice / startPrice - 1.
    member s.StdDev =
        let logRets =
            prices
            |> Seq.pairwise
            |> Seq.map (fun (x,y) -> log(x/y))
        let mean = logRets |> Seq.average
        let sqr x = x * x
        let var = logRets |> Seq.averageBy (fun r -> sqr (r-mean))
        sqrt var

Upvotes: 4

Daniel
Daniel

Reputation: 47904

If you're loading the data into SQL Server you can use this excellent CSV reader (free) and the SqlBulkCopy class. It's simple and efficient.

let loadStockPrices ticker =
  use client = new WebClient()
  let url = sprintf "http://ichart.finance.yahoo.com/table.csv?s=%s&g=d&ignore=.csv" ticker
  use stringReader = new StringReader(client.DownloadString(url))
  use csvReader = new CsvReader(stringReader, hasHeaders=true)
  use con = new SqlConnection("<connection_string>")
  con.Open()
  use bulkCopy = new SqlBulkCopy(con, DestinationTableName="<destination_table>")
  bulkCopy.WriteToServer(csvReader)

The destination table should have the same columns as the incoming data (OHLC, etc).

Upvotes: 8

Related Questions