Sqlite3 interface problem

General Usage
1

i have an sqlite db and did the following. Did the very obvious thing:

db=SQLite.DB("test.db")
s=SQLite.Query(db, "select distinct date from table")

I should point out that i ran both SQLite.tables and SQLite.columns and they both tell me the right thing, that the table is present and has correctly named columns. Also the query comes back with the correct column names it’s just that all the values are “missing”, as if it was empty or something which it most definitely isn’t.

If i run an equivalent test using python3 and sqlite3 module it works just fine.

Is there a missing step that I’m not seeing in the documentation ?

edit:
the step i’m missing is that the return result appears to be an iterable.

so

for x in s
  println(x)
end

provides the expected results.

An SQLite.Query object will iterate NamedTuple rows by default, and also supports the Tables.jl interface for integrating with any other Tables.jl implementation.

I’m finding that sentence a little hard to parse, but it looks like it’s telling me that the resulting object is an iterable. I think my brain read that as returning a NameTuple object.

2

How are you “collecting” the query results? In a DataFrame? Can you share the code where you’re seeing the problem? That would help in trying to diagnose what’s going on.

3

Thanks for responding so quickly.

I didn’t “collect” the query results , that was my problem :slight_smile:

I thought it was returning something array-like.

p.s. Would you be so kind as to show how i could collect it into a dataframe ? because that’s really want i would like to do.

p.p.s oh good grief! it’s definitely a Monday. There’s a perfectly fine example later on in the docs:

SQLite.Query(db, "SELECT FirstName, LastName FROM Employee WHERE LastName REGEXP 'e(?=a)'") |> DataFrame
4

You got it. The SQLite.Query type actually implements the Tables.jl interface, meaning any package that “uses” Tables.jl, can “collect” the query; for example:

  • query |> CSV.write("file.csv"): to write an sqlite query directly out to csv file
  • query |> Feather.write("file.feather"): write the query out as a feather file
  • query |> JuliaDB.table: materialize a query as a JuliaDB table

And that’s just a sample of the kinds of things that work out of the box. I recently wrote a blog post that listed a bunch of other integrations.

Enjoy Julia!

3 Likes