Clojure and MySQL
It’s time to start saving the data we downloaded in the prior part in a MySQL database.
Now I know there are a few fancy libraries for interacting with MySQL already, such as ClojureQL and CLJ-Record.
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.jarAfter 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.
Two articles were especially helpful, the JDBC examples and nakkaya’s Clojure and MySQL post.
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.