search

Lisp-Stat / sqldf

SQL for Data Frames
https://lisp-stat.github.io/sqldf
Microsoft Public License
2 stars 1 forks source link

query #1

Closed gassechen closed 1 year ago

gassechen commented 1 year ago

Hello. I have this function 1-

(lisp-stat:defdf df0
(make-data-frame (extract-keys-columns *db*) *db*))
#<DATA-FRAME:DATA-FRAME (44 observations of 20 variables)>

which creates a data-frame. then this to query and create an html table 2-

(defun query-lang (query-string )
   (let ((query query-string ))
   (print-html-table(sqldf:sqldf query))))

then it is in the hunchetoot web server 3-

(easy-routes:defroute foo ("/foo" :method :post)()
   (setf (hunchentoot:content-type*) "text/html")
   (let ((html (hunchentoot:post-parameters*)))
     (query-lang (cdr (car html)))))

If I run (2) from the repl there is no problem. when I call it from (3) it gives me the following error

 ((LAMBDA (COND) :IN HUNCHENTOOT:HANDLE-REQUEST) #<SIMPLE-ERROR "Could not find data frame ~A" {1006FB4FA3}>)
3: (SB-KERNEL::%SIGNAL #<SIMPLE-ERROR "Could not find data frame ~A" {1006FB4FA3}>)
4: (ERROR "Could not find data frame ~A" "df0")
5: (SQLDF:SQLDF "select esn from df0")
snunez1 commented 1 year ago

This is may be because you didn't use defdf to create the data frame, so it's not on the global list. It hard to say for sure without analysing the code.

My suggestion would be to start with the current work on data-server at Symbolics/plotview (be sure to checkout the 'data-server' branch). That way you'll be using code that other people are familar with and, if you discover bugs or make improvements, they will make they're way upstream. I know that the routes serving up data frames are working in that code. In this way you can eliminate back-end problems and focus on HTML/JavaScript on the front end.

gassechen commented 1 year ago

I was looking at the code Symbolics/plotview but it doesn't use sqldf. The problem is the following if I call this function from the repl 

(defun page-data-frame ()
   (spinneret:with-html-string
     (:doctype)
     (:html
      (:body :class "theme-default"
       (header)
       (content "From DATAFRAME" " get from DATAFRAME")
       (sql-area)
       ;;(table-list *data*)
       (print-html-table(sqldf:sqldf "select esn from df0"))
       ;;(print-html-table df0)

       (test form)
       (footer)))))
"<!DOCTYPE html>
<html lang=en><body class=theme-default><title>Dashboard</title><meta name=viewport content=\"width=device-width, initial-scale=1\"><link rel=stylesheet href=https://www.w3schools.com/w3css/4/w3.css><link rel=stylesheet href=https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css><link rel=stylesheet href=https://logistica-lite.microtrack.com.ar/styles.e893813ccc545810.css><link rel=stylesheet href=static/style.css><script src=https://code.jquery.com/jquery-3.6.0.min.js></script><script src=https://cdn.plot.ly/plotly-latest.min.js></script><script src=static/gauge.min.js></script><script src=https://unpkg.com/htmx.org@1.9.4></script><script src=static/script.js></script><main><h1>From DATAFRAME</h1><section id=about-me> get from DATAFRAME</section></main><h1>SQL QUERY</h1><textarea class=\"w3-panel w3-border w3-round-xlarge\" data-hx-post=/foo data-hx-swap=outerHTML data-hx-target=#query-result id=fname name=fname rows=4 cols=50></textarea><button class=\"w3-button w3-black\">SUBMIT</button><div id=query-result style=overflow-x:auto;><table class=\"w3-table w3-large w3-bordered\" id=table-data><tr class=w3-blue-grey><th>ESN<tr><td>2-3211333<tr><td>2-3211115<tr><td>2-3209916<tr><td>2-3198084<tr><td>0-4513034<tr><td>2-3209028<tr><td>70001602<tr><td>2-3209383<tr><td>2-3209124<tr><td>0-4513034<tr><td>2-3207035<tr><td>2-3209231<tr><td>70008193<tr><td>2-3209218<tr><td>0-4513034<tr><td>2-3209115<tr><td>2-3199021<tr><td>0-4513034<tr><td>2-3210527<tr><td>2-3209951<tr><td>2-3210527<tr><td>2-3210667<tr><td>2-3204299<tr><td>0-4513034<tr><td>2-3198000<tr><td>2-3205820<tr><td>0-4513034<tr><td>2-3198052<tr><td>70008193<tr><td>2-3211032<tr><td>70001602<tr><td>0-4513034<tr><td>2-3209951<tr><td>0-4513034<tr><td>2-3209728<tr><td>2-3198000<tr><td>0-4513034<tr><td>0-4513034<tr><td>2-3210272<tr><td>70008193<tr><td>2-3258897<tr><td>0-4513034<tr><td>2-3210667<tr><td>2-3210667<tr><td>2-3258912<tr><td>0-4513034<tr><td>2-3198084<tr><td>2-3210667<tr><td>2-3208794<tr><td>2-3258912<tr><td>2-3258897<tr><td>2-3209916<tr><td>2-3211115<tr><td>2-3208794<tr><td>2-3209124</table></div><form action=/foo method=post><label for=fname>First name</label><input type=text id=fname value=John><br><label for=lname>Last name</label><input type=text id=lname name=lname value=Doe><input type=submit value=Submit></form><section id=footer></section></body></html>"

works correctly.

but when I call it from the browser http://localhost:8899/test-data-frame This is the error

1: (HUNCHENTOOT::GET-BACKTRACE)
2: ((LAMBDA (COND) :IN HUNCHENTOOT:HANDLE-REQUEST) #<SIMPLE-ERROR "Could not find data frame ~A" {100921E743}>)
3: (SB-KERNEL::%SIGNAL #<SIMPLE-ERROR "Could not find data frame ~A" {100921E743}>)
4: (ERROR "Could not find data frame ~A" "df0")
5: (SQLDF:SQLDF "select esn from df0")
6: ((FLET "<BODY>7" :IN MYPROJECT::PAGE-DATA-FRAME))

(defun sqldf (sql)
  "Execute SQL (a string) on a data frame and return a new data frame with the results.
The data frame is identified by the word following FROM (case insensitive) in the SQL string.  An in-memory SQLite database is creaetd, the contents of the data frame loaded, the query performed and a new DATA-FRAME returned with the results and the database deleted.  In most cases, using this library is faster, from a developers time perspective, than writing the code to perform the same query.  SQLDF has been tested with data frames of 350K rows with no slow-down noted.  The R documentation for their version of SQLDF suggests that it could be faster than Lisp native queries.  Note that the SQL query must use SQL style names for columns and not the Lisp versions, e.g. flight-time becomes flight_time."
  (let* ((db    (sqlite:connect ":memory:"))
     (words (uiop:split-string sql))
     (table (nth (1+ (position "from" words :test #'string=)) words))
     (df-name (find-symbol (string-upcase table)))
     df nrows data)

    (if (not (and (boundp df-name)
          (typep (symbol-value df-name) 'df:data-frame)))

_(error "Could not find data frame ~A" table)_

    (setf df (symbol-value df-name)))

It would seem like it loses context, and only works in the repl environment. I don't know if I could explain myself well.

gassechen commented 1 year ago 
(defun print-html-table (df &key (row-numbers nil))
  "Print data frame DF as an HTML table with Spinneret, to STREAM.
   If ROW-NUMBERS is true, also include row numbers."
  (spinneret:with-html 
    (let* ((array (aops:as-array df)))
      ;; Print opening <table> tag
      (:div :id "query-result" :style "overflow-x:auto;"
      (:table :id "table-data" :class "w3-table w3-large w3-bordered"

       ;; Print header row
       (:tr :class "w3-blue-grey"
        (when row-numbers (:th "#" ))
        (map nil #'(lambda (x)
                     (:th x ))
             (data-frame:keys df)))

       ;; Print data rows
       (aops:each-index i
         (:tr
          (when row-numbers (:td i ))
          (aops:each-index j
            (:td (aref array i j) )))))))))
gassechen commented 1 year ago

My guess is the problem is with how sqlite handles threads That code is using an in-memory database and sqlite might make a separate database per thread or something.

maybe 

(defparameter *conn* (sqlite:connect #P"c:/Users/lisp-stat/data/iris.db3")) ;filel to save to
*CONN*

and 

(defun sqldf (sql)

  (let* ((db    *conn*))
     (words (uiop:split-string sql))
     (table (nth (1+ (position "from" words :test #'string=)) words))
     (df-name (find-symbol (string-upcase table)))
     df nrows data) .....
snunez1 commented 1 year ago

I would try two things:

(print-html-table (as-array (sqldf:sqldf "select esn from df0"))) ;argument to print-html-table is now an array

and of course change print-html-table to not use as-array in the variable assignment.

gassechen commented 1 year ago 
(defun page-data-frame ()
   (spinneret:with-html-string
     (:doctype)
     (:html
      (:body :class "theme-default"
       (header)
       (content "From DATAFRAME" " get from DATAFRAME")
       (sql-area)
       (sqldf:sqldf "select esn from df0"))
       (test form)
       (footer)))))

If I do this the error persists.

1: (HUNCHENTOOT::GET-BACKTRACE)
2: ((LAMBDA (COND) :IN HUNCHENTOOT:HANDLE-REQUEST) #<SIMPLE-ERROR "Could not find data frame ~A" {100921E743}>)
3: (SB-KERNEL::%SIGNAL #<SIMPLE-ERROR "Could not find data frame ~A" {100921E743}>)
4: (ERROR "Could not find data frame ~A" "df0")
5: (SQLDF:SQLDF "select esn from df0")
6: ((FLET "<BODY>7" :IN MYPROJECT::PAGE-DATA-FRAME))

"In the data frame name with a package, e.g. PKG:DF-NAME. That way there isn't any ambiguity about where the symbol is." I don't know how to do this

snunez1 commented 1 year ago

sqdlf is not going to return an array, it's going to return a data-frame. Why are you using sqldf anyway? If your query is complex, i.e. has joins, then that's the way to go. If you're only filtering rows, I'd use the filter-rows function.

gassechen commented 1 year ago

Right, I want to be able to use the full set of sql languages supported by sqlite. On the page there is a query-box to enter the queries you want.

gassechen commented 1 year ago

(defparameter *conn1* (sqlite:connect ":memory:"))

(defun sqldf-1 (sql )
  (let* ((db    *conn1*)
     (words (uiop:split-string sql))
     (table (nth (1+ (position "from" words :test #'string=)) words))
     (df-name (find-symbol (string-upcase table)))
     df nrows data)

the error persists. It seems that the database, when created in memory, is not propagated globally and does not reach the subroutines.

snunez1 commented 1 year ago

The find-symbol also takes a package name. If you call it from the REPL, it will take the current package. If you call it from hunchentoot, it will also take the current package, but that's unlikely to be the one that the symbol was interned in. Put some print statements in at the point of call to find out what the current package is there.

gassechen commented 1 year ago 
(let* ((db    (sqlite:connect ":memory:"))
     (words (uiop:split-string sql))
     (table (nth (1+ (position "from" words :test #'string=)) words))
     (df-name (find-symbol (string-upcase table)))
     df nrows data)
    (print  db)
    (print  words)
    (print  table)
    (print  df-name)

MYPROJECT> (sqldf:sqldf "select esn from df0")

"select esn from df0" 
#<SQLITE:SQLITE-HANDLE {100F4E0C43}> 
("select" "esn" "from" "df0") 
"df0" 
DF0 
#<DATA-FRAME:DATA-FRAME (72 observations of 1 variables)>
  1. HUNCHENTOOT call
"select esn from df0" 
#<SQLITE:SQLITE-HANDLE {10108482A3}> 
("select" "esn" "from" "df0") 
"df0" 
NIL [2023-09-06 09:52:52 [ERROR]] Could not find data frame df0
gassechen commented 1 year ago

From repl

"select esn from df0" 
#<SQLITE:SQLITE-HANDLE {100F4E1433}> 
("select" "esn" "from" "df0") 
"df0" 
DF0 REPL PACKAGE: #<PACKAGE "MYPROJECT">
#<DATA-FRAME:DATA-FRAME (72 observations of 1 variables)>

Form web server

"select esn from df0" 
#<SQLITE:SQLITE-HANDLE {1011720233}> 
("select" "esn" "from" "df0") 
"df0" 
NIL  Hunchentoot PACKAGE: #<PACKAGE "COMMON-LISP-USER">
[2023-09-06 09:58:44 [ERROR]] Could not find data frame df0
gassechen commented 1 year ago 
(defun page-data-frame ()

  (spinneret:with-html-string
    (:doctype)
    (:html
     (:body :class "theme-default"
      (header)
      (content "From DATAFRAME" " get from DATAFRAME")
      (sql-area)
       (in-package :myproject)
      (print-html-table(sqldf:sqldf "select esn from df0"))

      (test-form)
      (footer)))))

That's how it works for me, but I don't think it's right to do this.