Lisp-Stat / Lisp-Stat/sqldf

sql query

Open
#4 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Common Lisp
Stars
2
Forks
1
PR merge metrics
No merged PRs in 30d

Description

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

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start with the sqldf entry point and reproduce the reported difference between a REPL call and a call through Hunchentoot. Inspect how the query identifies the dataframe in the current package, then verify that the normal call works from the web request context without requiring the proposed optional parameter.

Written by the indexing model from the issue text.

Assessment

Tech stack
sqlite
Domain
backend, databases
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
30/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.