1

I was wondering whether it is possible to retrieve data directly from SQL to generate *.dat file and draw plots with pgfplots, like for example:

\documentclass{article} 
\usepackage{pgfplots} 

\pgfplotsset{compat=1.5}
\begin{document}
\begin{tikzpicture}
\begin{axis}[
   title=Inv. cum. normal,
   xlabel={$x$},
   ylabel={$y$},
]
\addplot[blue] table {*.dat} -> data retrieve directly from SQL database;
\end{axis}
\end{tikzpicture}
\end{document}
MS-SPO
  • 11,519
unmark1
  • 469
  • 2
    If the data file is a text file with properly formatted content (i.e. as a table with comma/space/etc separated data), it doesn't matter what you used to create that file. Unless I'm misunderstanding your question. – Alenanno Sep 02 '16 at 23:43
  • Also, note https://github.com/bingmann/sqlplot-tools – JPi Sep 02 '16 at 23:57
  • But, do you think it would be possible to write te query inside the .tex document, for example: select from something ... and this result would be the *.dat file? – unmark1 Sep 03 '16 at 00:37
  • You could try the bashful package: Example at How to automatically include command line session output in documents?. – Peter Grill Sep 03 '16 at 09:12
  • pgfplots has an input mode \addplot shell [<options>]{ shell commands }; which allows to execute arbitrary shell commands. If you ha ve a sql command which spits out properly formatted CSV data, pgfplots will be able to read it. I believe the interface builds on top of the gnuplot interface, so you may need to check if it works. – Christian Feuersänger Sep 03 '16 at 16:14
  • Following the comments, I have tried this: \documentclass{article} \usepackage{bashful}

    \begin{document}

    \bash[stdout] mysql -u username -ppassword -D database -e "SELECT Date,Settle FROM table" >"/directory/file.dat" \END

    \end{document}, but I received line 1:mysql: command not found, even though when I run this directly from terminal it correctly generates the file. I will keep trying.

     – unmark1 Sep 03 '16 at 18:26

1 Answers1

2

I managed to succeed doing the following, which is a great integration tool!:

\documentclass{article} 
\usepackage{bashful} 
\usepackage{pgfplots} 
\usepgfplotslibrary{dateplot,statistics}
\pgfplotsset{compat=1.8}
\usepackage{pgfplotstable}



\begin{filecontents}{soyprice.dat}
Date    Settle
2016-08-02  985.25
2016-08-03  990.5
2016-08-04  990.5
2016-08-05  1003.75
2016-08-08  1018.75
2016-08-09  1022
2016-08-10  1017
2016-08-11  1022.25
2016-08-12  1003.25
2016-08-15  1023.75
2016-08-16  1019.5
2016-08-17  1030.5
2016-08-18  1032.25
2016-08-19  1027
2016-08-22  1035.25
2016-08-23  1034.25
2016-08-24  1030.75
2016-08-25  998.25
2016-08-26  990.75
2016-08-29  983.25
2016-08-30  965.5
2016-08-31  960
2016-09-01  959
2016-09-02  968.5
\end{filecontents}

\begin{document}

\bash[stdout]

/Applications/MAMP/Library/bin/mysql (YOUR PATH TO MYSQL) -u YOURUSERNAME -pYOURPASSWORD -e "SELECT * FROM YOURDATABASE" > soyprice.dat
\END

\begin{tikzpicture}[scale=1]
\begin{axis}[
           xlabel=Date, ylabel=Settle,table/col sep=space|tab|,
           date coordinates in=x,
           date ZERO=2016-08-01,
               xticklabel style={rotate=90},
               enlarge x limits = false,
               xticklabel={\month-\day},
               ymin=900,
               ymax=1100,
               minor y tick num=25,
              xmin=2016-08-02,
              xmax=2016-09-02
          title={Preço da soja - CME},
]
\addplot[blue] table [x={Date},y={Settle}] {soyprice.dat};
\end{axis}
\end{tikzpicture}

\end{document}
unmark1
  • 469