Dylan D. Dylan D. - 5 months ago 19
MySQL Question

How to dynamically write a MySQL query from a text file in Clojure

How do I read from an input text file that is 1 column of ids and produce a MySQL query of the format:

SELECT col1,col2,col3 FROM Orders WHERE Id IN ('inputId1','inputId3','inputId3');


The ids in the input file are separated by /n and should be converted into the comma separated list of Ids enclosed in quotes for the MySQL query.

(ns export.core
(:require [clojure.java.jdbc :as j])

(:gen-class))

(defn -main [& args]

;; Get home directory
(def out-file
(str (System/getProperty "user.home") "/Desktop/export.txt"))

(def in-file
(str (System/getProperty "user.home") "/Desktop/orders.txt"))

;; Get string of order-ids
(def order-ids-string (slurp in-file))

???????????
???????????

;; Connect to database
(def db {:subprotocol "mysql"
:subname "XXXXXXXX"
:user "XXXXXXX"
:password "XXXXXXX"})

;; Get headers
(def header-seq
(j/query db ["DESCRIBE Orders"] :row-fn :field))

(def header-str
(str (clojure.string/join "\t" header-seq) "\n"))

;; Get product results and spit data to file
(def header-keys
(into []
(map keyword
(map clojure.string/lower-case header-seq))))

(def data-seq
(j/query db [<needed sql query>]))

(defn select-values [map]
(reduce #(conj %1 (map %2)) [] header-keys))

(spit out-file header-str)

(doseq [row data-seq]
(spit out-file
(str (clojure.string/join "\t" (select-values row)) "\n")
:append true)))

Answer

If I've understood your question correctly I would use line-seq, string/join, and format to form the query:

first some test data:

(spit "/tmp/input-file" "id1\nid2\nid3\nid4\n")

then lets read it back and form a string

user> (let [ids (line-seq (clojure.java.io/reader "/tmp/input-file"))
            col-names (clojure.string/join "," (map #(str "col" %) (range 1 (inc (count ids)))))
            col-ids (clojure.string/join "," (map #(str "'"% "'") ids))]
        (format "SELECT %s FROM Orders WHERE Id IN (%s);" col-names col-ids))

"SELECT col1,col2,col3,col4 FROM Orders WHERE Id IN ('id1','id2','id3','id4');"

I'm guessing that the number of order id's matches the number of lines in the file and that they should get sequential numbers in their names.

as amalloy points out it's basicly always better to use query params:

user> (let [ids (line-seq (clojure.java.io/reader "/tmp/input-file"))
            col-names (clojure.string/join "," (map #(str "col" %) (range 1 (inc (count ids)))))            
            question-marks (clojure.string/join "," (repeat (count ids) "?"))]
        (list 'exec-raw (format "SELECT %s FROM Orders WHERE Id IN (%s);" col-names question-marks) ids))
(exec-raw "SELECT col1,col2,col3,col4 FROM Orders WHERE Id IN (?,?,?,?);" ("id1" "id2" "id3" "id4"))

(replace list exec-raw with whatever function your use to make the SQL call)

Comments