sql query
Open
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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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