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.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.

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)
      :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 []
   "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.

Related Posts

Tags: ,