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

Question

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

Arthur Ulfeldt · Accepted Answer · 2016-06-03 23:31:54Z

2

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)

edited Jun 3, 2016 at 23:31

answered Jun 3, 2016 at 23:12

Arthur Ulfeldt

91.7k30 gold badges205 silver badges285 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

amalloy Over a year ago

Unluckily, id1 happens to be '); DROP TABLE Orders; --. Much safer to output Id IN (?, ?, ?) and then pass the actual list of ids as a parameter to the query.

Arthur Ulfeldt Over a year ago

Yes, of course the usual warnings about forming SQL as strings from user input apply. If you are taking the column names from files then not much can be done :-/ let's assume this question is about forming strings ;-)

Arthur Ulfeldt Over a year ago

added that to the answer (because eventually everything gets cut-and-pasted into somones code

amalloy Over a year ago

I think it's weird to assume the column names are being generated and also correspond to the IDs. I'd just use col1..col3 as fixed column names; then this becomes the correct answer to a pretty common question.

Dylan D. Over a year ago

thank you for the response. let me clarify a few things. The column names will just be whatever columns i want to pull. col1, col2, etc are just placeholders for the example and will never change. The ids are order ids from various ecommerce marketplaces so they differ in length and do not increment, they are random

|

Collectives™ on Stack Overflow

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

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related