It’s time to start saving the data we downloaded in the prior part in a MySQL database.
For this project though the standard clojure-contrib.sql library would do just fine.
At the time of writing it doesn’t seem like the JDBC connector for MySQL is available on for instance Clojars, so I downloaded it and ran the following command to install it with maven:
mvn install:install-file -DgroupId=mysql-connector-java -DartifactId=mysql-connector-java -Dversion=5.5.15 -Dpackaging=jar -Dfile=/tmp/mysql-connector-java-5.1.15-bin.jar
After that I made my project.clj look like this:
(defproject fred "0.1" :description "FRED parser and displayer" :dependencies [[org.clojure/clojure "1.2.0"] [org.clojure/clojure-contrib "1.2.0"] [mysql-connector-java "5.5.15"]] :dev-dependencies [[ring/ring-devel "0.3.4"] [swank-clojure "1.2.1"]])
Doing lein deps now will copy the jar from the m2 dir to your project lib dir, after that a lein swank and M-x slime-connect in emacs takes care of the rest.
Let’s get to the real work: first storing our data and then querying it.
For insertions I opted to use insert-values, it’s the most documented one but probably not the best for me, insert-records or insert-rows would probably have been faster for my batch requirements. Whatever, it’s a one off and I wasn’t in a hurry.
Here is my function that inserts an observation series meta entry:
(defn insert-series [s] (when (:id s) (sql/insert-values :series [:rt_start :rt_end :title :obs_start :obs_end :freq_short :units_short :sa_short :last_updated :notes :id] [(or (:realtime_start s) "0000-00-00") (or (:realtime_end s) "0000-00-00") (or (:title s) "no title") (or (:observation_start s) "0000-00-00") (or (:observation_end s) "0000-00-00") (or (:frequency_short s) "na") (or (:units_short s) "na") (or (:seasonal_adjustment_short s) "na") (or (re-find #"\d\d\d\d-\d\d-\d\d \d\d:\d\d:\d\d" (:last_updated s)) "0000-00-00") (or (:notes s) "no notes") (:id s)])))
I ran into problems when I didn’t catch nil values, despite employing the most forgiving table structure possible, hence all these or statements. In retrospect insert-values was therefore probably the best pick.
Note how you could wrap the (sql/with-connection db (insert-series s)) call here by creating more general functions, it could look something like this (not tested):
(defn insert-something [tbl keys vals] (sql/insert-values tbl keys vals)) (defn db-insert [tbl keys vals] (sql/with-connection db (insert-something tbl keys vals))
As far as querying and fetching results there are two approaches, you can either 1) pass along a function to do work on each retrieved row (this is usually the option you see in various writeups and short tutorials) or 2) turn the lazy returned result in to a “real” result and return it for further processing somewhere else.
1) Could look something like this:
(defn db-do-where [sql-str func] (sql/with-connection db (sql/with-query-results rs [sql-str] (dorun (map func rs))))) (defn save-observations  (db-do-where "SELECT * FROM series" #(save-observation (:id %))))
We get all series and save each’s observations with the help of its id.
2) Is simpler with the the help of doall:
(defn get-where [sql-str] (sql/with-connection db (sql/with-query-results rs [sql-str] (doall rs))))
And that’s that, next time we’ll start doing work on the actual data we now have in the MySQL database.