[ANN] SQLiteDF a simple package to use Tables.jl sources with SQL queries

General Usage
1

SQLiteDF.jl is a simple package with currently one macro

@sqlitedf a,b,c,...,"select * from .."

in the context where it’s called it loads the data from Tables.jl compatible variables named a,b,c etc into a new in-memory SQLite database using the same names, and then calls the query and returns the results, which can for example be piped into a DataFrame.

I’m thinking of adding a few additional similar macros, maybe something like

@withsqlitedb db, a,b,c,..., expr

which would create a new in memory sqlite database called “db” load the a,b,c… tables into it and then evaluate the expr where the expr could do arbitrary stuff to the db.

Basically the reason this needs to be macros is so that it can capture the names a,b,c and create the appropriate named tables so that the names match in the SQLite db. Unless I’m mistaken.

This is both my first Julia package, and my first foray into metaprogramming in Julia (I’ve written macros in Common Lisp, ages ago).

Any constructive criticism, suggestions, or bug reports welcome.

Apparently back in 2014 or so the SQLite.jl library had its own sqldf macro, but it was removed at one point, and I’m not sure why.

2 Likes
2

Question for Julians with a little more experience than I have…

In my implementation I loop over the symbols passed to the macro and eval each one to get the table value so that I can shove it into the sqlite database… Then I read how eval always evals in the scope of the module and not in the local scope, so if you were writing a function using @sqlitedf you’d wind up with some borkage.

What’s the right way to handle this?

The macro is so short I’ll just put it here…

macro sqlitedf(a...)
    db = gensym()
    return esc(quote
        $db = SQLite.DB();
        for sym in $(a[1:end-1])
            SQLite.load!(eval(sym),$db,String(sym))
        end;
        SQLite.DBInterface.execute($db,$(a[end]))
    end)
end

it’s called like

@sqlitedf a,b,c, "select * from a join b..."

so a,b,c are intended to refer to variables in your local scope that are Table.jl objects that SQLite.load! can load into the database giving them the same name.

This macro works at top level but it seems wouldn’t work inside a function where a,b,c are local variables because of the “eval”. How do I fix it?

3

Well, I couldn’t figure out how to write it as a loop, but I could figure out how to loop over the tables and generate one line per table… I think this makes more sense and doesn’t use eval:

macro sqlitedf(a...)
    q = a[end]
    tabs = a[1:end-1]
    names = String.(tabs)
    db = gensym()
    code = :(begin end)
    push!(code.args,esc(:($db = SQLite.DB())))
    for (tab,name) in zip(tabs,names)
        push!(code.args,esc(:(SQLite.load!($tab,$db,$name))))
    end
    push!(code.args,esc(:(SQLite.DBInterface.execute($db,$q))))
    return code
end

Anyone with some macro chops have any comments?