0
$\begingroup$

Below is an SQL query that I generated using DatabaseExplorer as well as a small sample of the result. How, att this point, can I perform analysis on the results of the query. For example how would I create a new variable that is equal to high - low? And how would I graph high?

In essence, I am having difficulty in having the result being used as actual data points.


SQLExecute[
 SQLSelect[
  "mySQLtry", {"both4"}, {SQLColumn[{"both4", "id"}], 
   SQLColumn[{"both4", "open"}], SQLColumn[{"both4", "high"}], 
   SQLColumn[{"both4", "low"}], SQLColumn[{"both4", "close"}], 
   SQLColumn[{"both4", "volume"}]}, None, "SortingColumns" -> None, 
  "MaxRows" -> 100000, "Timeout" -> 10, "Distinct" -> False, 
  "GetAsStrings" -> False, "ShowColumnHeadings" -> True]] // TableForm


  {
 {"id", "open", "high", "low", "close", "volume"},
 {1, 167.29, 170.13, 166.93, 168.84, 6401800},
 {2, 164.06, 167.28, 163.69, 166.7, 5996200},
 {3, 164.5, 165.2, 163.21, 164.12, 5214800},
 {4, 164.1, 165.71, 163.46, 163.76, 3586000},
 {5, 164., 164.9, 163.31, 163.97, 1858100},
 {6, 165.06, 165.8, 163.39, 163.63, 4052600},
 {7, 165.5, 166.3, 164.5, 164.6, 4190700},
 {8, 163.79, 166.75, 163.42, 165.45, 9392900},
 {9, 161.81, 163.38, 160.2, 163.19, 8687100},
 {10, 162.31, 163.62, 160.83, 160.93, 8943800},
 {11, 163.85, 165.89, 163.07, 164.99, 8637700},
 {12, 164.81, 166., 162.11, 162.74, 7539800},
 {13, 166.48, 166.5, 163.86, 166.1, 6652900},
 {14, 167.54, 167.95, 165.42, 166., 6425000},
 {15
$\endgroup$
  • $\begingroup$ If out is your expression w/o TableForm, then Evaluate[Symbol /@ First@out] = Transpose@Rest@out should work. This doesn't have anything to do with SQL queries though... $\endgroup$ – rm -rf Mar 14 '15 at 16:10
  • $\begingroup$ I get this message when I tried your kind suggestion: Symbol::string: String expected at position 1 in Symbol[{id,open,high,low,close,volume}]. >> Symbol::string: String expected at position 1 in Symbol[{1,167.29,170.13,166.93,168.84,6401800}]. >> Symbol::string: String expected at position 1 in Symbol[{2,164.06,167.28,163.69,166.7,5996200}]. >> $\endgroup$ – John Mar 14 '15 at 17:02
2
$\begingroup$

First of all you'd typically just assign the result of an SQLSelect to a variable and than use that for further processing, e.g.:

data = SQLSelect[
 "mySQLtry", {"both4"}, {SQLColumn[{"both4", "id"}], 
 SQLColumn[{"both4", "open"}], SQLColumn[{"both4", "high"}], 
 SQLColumn[{"both4", "low"}], SQLColumn[{"both4", "close"}], 
 SQLColumn[{"both4", "volume"}]}, None, "SortingColumns" -> None, 
 "MaxRows" -> 100000, "Timeout" -> 10, "Distinct" -> False, 
 "GetAsStrings" -> False, "ShowColumnHeadings" -> True];

what that replaces is just an array of values, which you can access with the standard Mathematica functions, e.g. Part (with shortcut [[]]). This will give the "high" and "low" columns without the headers:

 data[[2 ;; -1, {3, 4}]]

there are many ways to calculate the difference between these, here is a particularly short one:

 Subtract@@@data[[2 ;; -1, {3, 4}]]

to graph data, use the standard functions, e.g.:

 ListLinePlot[data[[2 ;; -1, 3]]]

in typical cases where the use of a database makes sense I'd suggest to extract only the data you need and in a form as you need it. So I'd probably only select those columns I actually want to process and not use the "ShowColumnHeadings" option. Then the processing will be somewhat simpler and the code will typically run faster and use less memory.

Actually IMO all the options given in your generated statement don't make much sense in a code fragment as they are either the defaults or not what you'd typically use, MaxRows and Timeout are most probably just set so that the DatabaseExplorer doesn't run into trouble and stay reactive when you play with a huge database (that is tables with many rows)...

$\endgroup$
1
$\begingroup$

I tried using Dataset but it is giving some odd errors so this answer skips that function.

If you have your result in res then you can setup a function to get the column you want from it. This would be better with Dataset but there appear to be some bugs with it in 10.0.2.

res = {{"id", "open", "high", "low", "close", "volume"}, 
 {1, 167.29, 170.13, 166.93, 168.84, 6401800}, 
 {2, 164.06, 167.28, 163.69,166.7, 5996200}, 
 {3, 164.5, 165.2, 163.21, 164.12, 5214800}, 
 {4, 164.1, 165.71, 163.46, 163.76, 3586000}, 
 {5, 164., 164.9, 163.31, 163.97, 1858100}, 
 {6, 165.06, 165.8, 163.39, 163.63, 4052600}, 
 {7, 165.5, 166.3, 164.5, 164.6, 4190700}, 
 {8, 163.79, 166.75, 163.42, 165.45, 9392900}, 
 {9, 161.81, 163.38, 160.2, 163.19, 8687100}, 
 {10, 162.31, 163.62, 160.83, 160.93, 8943800}, 
 {11, 163.85, 165.89, 163.07, 164.99, 8637700}, 
 {12, 164.81, 166., 162.11, 162.74, 7539800}, 
 {13, 166.48, 166.5, 163.86, 166.1, 6652900}, 
 {14, 167.54, 167.95, 165.42, 166., 6425000}};

This function expects that the First row of res contains the column names. It will find the position of the column name in that row then use it to extra items in the Rest of res using that position index.

getColumn[data_List, colName_String] := 
  Rest /* Flatten@data[[All, Flatten@Position[First@data, colName]]]

It uses RightComposition (/*) from version 10 so swap that around and use @ if you're on an earlier version.

Now you can use getColumn to return the column you are interested in and use them for calculations and plotting.

getColumn[res, "high"]
getColumn[res, "high"] - getColumn[res, "low"]
ListLinePlot[getColumn[res, "high"]]

Hope this helps.

$\endgroup$
  • $\begingroup$ can i ask, what role/function the term "data" in Flatten@data and in First@data I would have thought the term data would have actually been res $\endgroup$ – John Mar 15 '15 at 2:27
  • $\begingroup$ Btw, it did not work, i get this expression: getColumn[<<1>>, high] is not a list of numbers or pairs of numbers $\endgroup$ – John Mar 15 '15 at 2:37
  • $\begingroup$ @John , What version are you using? I'm on 10.0.2 . If you are not on 10 then you should change Rest /* Flatten@ to Flatten@Rest@. Also, do you pass in "high" or high? You need the quotes. $\endgroup$ – Edmund Mar 15 '15 at 16:42
  • $\begingroup$ I am in fact using 10.0.2 and i actually just cut/paste your example so the high is in fact "high". i added your code starting with getColumn[data_List and then added the code starting with getColumn[res,"high"]. sorry for the back and forth and i just wanted to say thank you for the persistence with me :-) $\endgroup$ – John Mar 16 '15 at 3:54
  • $\begingroup$ Big "eat crow" on my part. When i imported the data from a huge csv file i did not change the numbers from varchar to float so mathematica was looking at the values as strings. After fixing this oversight on my part, the suggestions from the group worked (as I would expect :-) $\endgroup$ – John Mar 16 '15 at 5:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.