Hello,
I've seen in the manual how to read data from sqlite databases. However, to a less savvy user it might be unclear how to read a dataframe from another database.
With systems like postmodern or cl-dbi it's a trivial task. I think it would be nice to include some machinery for this in the data-frame package or document examples like the one below in the manual.
(defvar *db-connection*
(dbi:connect :postgres
:host "localhost"
:port 5000
:database-name "my-database"
:username "user"
:password "my-strong-password"))
(defun my-sql-rows ()
"Fetches rows from the database as list of plists"
(let* ((dbi:*row-format* :plist)
(query (dbi:prepare *db-connection* "select * from my_table")))
(dbi:fetch-all (dbi:execute query))))
(defun plists-to-df (plists)
"Convert PLISTS to a dataframe. Each plist represents a row, and its keys
become column names. All plists must share the same keys as the first plist.
Example:
(plist-list-to-df '((:a 1 :b 2) (:a 3 :b 4)))
=> a dataframe with columns :A and :B
Returns a dataframe via MAKE-DF."
(let* ((keys (alexandria+:plist-keys (first plists)))
(columns (mapcar (lambda (k)
(map 'vector (lambda (row) (getf row k))
plists))
keys)))
(make-df keys columns)))
;; Fetch query results and convert to a dataframe.
(defdf *df* (plists-to-df (my-sql-rows)))The only obvious problem with this example is that dbi escapes plist keys by default, enclosing them in ||. However, all the dataframe printing functions obscure this fact and print not escaped symbol names. This is confusing because the user still has to address columns using escaped symbols. For example, if my_table contains a column called id, it turns into *df*:|id|.
I may study this path in more detail if you are interested in adding features like that to lisp-stat. They may be implemented as a complementary system to avoid a hard dependency on cl-dbi, for example. I'm also working on a Trino SQL connector for cl-dbi, which may be handy for accessing datasets scattered across large heterogeneous storage.
Otherwise, I'll be glad to write a detailed example for the manual. Please let me know what you think about it.
Hello,
I've seen in the manual how to read data from sqlite databases. However, to a less savvy user it might be unclear how to read a dataframe from another database.
With systems like postmodern or cl-dbi it's a trivial task. I think it would be nice to include some machinery for this in the data-frame package or document examples like the one below in the manual.
The only obvious problem with this example is that
dbiescapes plist keys by default, enclosing them in||. However, all the dataframe printing functions obscure this fact and print not escaped symbol names. This is confusing because the user still has to address columns using escaped symbols. For example, ifmy_tablecontains a column calledid, it turns into*df*:|id|.I may study this path in more detail if you are interested in adding features like that to lisp-stat. They may be implemented as a complementary system to avoid a hard dependency on cl-dbi, for example. I'm also working on a Trino SQL connector for cl-dbi, which may be handy for accessing datasets scattered across large heterogeneous storage.
Otherwise, I'll be glad to write a detailed example for the manual. Please let me know what you think about it.