Can queries be exported to CSV?

[normansimonr]

Hi! I am creating a notebook for teaching SQL and need learners to be able to export the results of their queries to CSV files. I was wondering if that can be easily done from within the notebook with the Xeus-SQLite kernel. Thanks!

[marimeireles]

It might be... Specially if there's a SQLite command for that.
Or you could try xeus-sql and check if SOCI has a command for that.
If not it's not suuper hard to implement a functionality like this... Someone with some understanding of C++ could do it :)
I'm around to help!

[normansimonr]

@marimeireles Thanks! Do you mean something like this? Is it possible to use these commands from within the notebook?

[marimeireles]

Ah no, it seems like I wrote all of these functions by hand as jupyter magic.
I guess it's not too hard to do the same...
We need a library to parse data and convert it to csv, then we just write a file to the user.
This is how load is currently implemented in xeus-sql

    static std::unique_ptr<soci::session> load_db(
            const std::vector<std::string> tokenized_input)
    {
        std::string aux;
        for (std::size_t i = 2; i < tokenized_input.size(); i++)
        {
            aux += tokenized_input[i] + ' ';
        }
        return std::make_unique<soci::session>(
                xv_bindings::to_lower(tokenized_input[1]), aux);
    }

So to implement an output mode I guess we'd do smth similar...
We need a function to grab the data and generate a csv file with it, not sure how to do it, but seems like a task that these robots that generate code could do and then you can just adapt it.
I guess it's in this line: nl::json data = process_SQL_input(code, xv_sql_df); (the data we'd like to convert).
The most complicated thing is how to make the context of which table you're generating clear... I'd have to get deeper into the code and right now I don't have the time, sorry.
But if you or anyone are interested in giving it a try I'm around to answer questions.
Cheers!