sql query

[gassechen]

Hi, I ran into a problem. The sqldf query doesn't find the dataframe outside of the execution from the repl. So I made this small modification so that it accepts the dataframe as a parameter. This error occurs when I call sqldf query from a url with hunchentoot, I don't know how to explain it.

(defun sqldf (sql &optional df)
  "Execute SQL (a string) on a data frame and return a new data frame with the results.
An in-memory SQLite database is created, 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 developer's 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 (or df (symbol-value (find-symbol (string-upcase table) *package*))))
         nrows data)

    (unless (and df (typep df 'df:data-frame))
      (error "Could not find data frame ~A" table))

    (create-df-table db table df)
    (write-table db table df)

    ;; There's no good way to get the number of rows in a query in SQLite
    (setf nrows (sqlite:execute-single db (format nil "select count (1) from (~A)" sql)))

    (sqlite::with-prepared-statement stmt (db sql nil)
      (loop while (sqlite:step-statement stmt)
            for i = 0 then (1+ i)
            with column-names = (map 'list
                                     #'(lambda (x)
                                         (from-sql-name x))
                                     (sqlite:statement-column-names stmt))
            for types = (loop for i below (length column-names)
                              collect (statement-column-type stmt i))
            do (if (not data)
                   (setf data (loop
                               for name in column-names
                               for type in types
                               collect (cons (if (find-symbol name (find-package "SQLDF"))
                                                 (find-symbol name (find-package "SQLDF"))
                                                 (intern (string-upcase name) (find-package "SQLDF")))
                                             (alexandria:switch (type :test #'string=)
                                               ("REAL"    (make-array nrows :element-type 'double-float))
                                               ("INTEGER" (make-array nrows :element-type 'integer))
                                               ("TEXT"    (make-array nrows)))))))
            do (loop for j below (length column-names)
                     do (setf (aref (cdr (nth j data)) i) (sqlite:statement-column-value stmt j)))))
    (sqlite:disconnect db)
    (data-frame:alist-df data)))

[gassechen]

ERROR

"select userid, id, title, body from df2" [2024-06-03 09:54:12 [ERROR]] Could not find data frame df2
Backtrace for: #<SB-THREAD:THREAD tid=24317 "hunchentoot-worker-127.0.0.1:49738" RUNNING {1004E5E7A3}>
0: (TRIVIAL-BACKTRACE:PRINT-BACKTRACE-TO-STREAM #<SB-IMPL::STRING-OUTPUT-STREAM {7FCBCD10DD43}>)
1: (HUNCHENTOOT::GET-BACKTRACE)
2: ((LAMBDA (COND) :IN HUNCHENTOOT:HANDLE-REQUEST) #<SIMPLE-ERROR "Could not find data frame ~A" {1013314573}>)
3: (SB-KERNEL::%SIGNAL #<SIMPLE-ERROR "Could not find data frame ~A" {1013314573}>)
4: (ERROR "Could not find data frame ~A" "df2")
5: (SQLDF:SQLDF "select userid, id, title, body from df2")
6: ((FLET "<DIV>0