How to use results from SQLite select query in Julia script

New to Julia
1

SUMMARY: I need a simple collection of select query results such as Python’s sqlite3.connect.cursor.fetchall(). In Python if I do a select query on my place table for IDs and names I get [(18, “Wichita”), (9, “Topeka”), (233, "Kansas City)]. So I just do
places = cur.fetchall(). To use the value “Topeka” I reference places[1][1].

The rest of the story:

I’ve been using sqlite3 with Python for three years and it’s really easy. I hope to move to Julia because Python might bog down under large databases.

I can get insert, and update queries to work as well as everything else I’ve tried, but can’t find documentation that includes a simple example of how to use results from a select query in a Julia script. I can get a pretty table of results to print in REPL, I can save that as a variable name and if I print the variable I get another copy of the pretty table. The pretty table is for my eyes, I can’t use it in a script.

If I remove the DataFrame() call I get a collection of stuff that must mean something to somebody but according to this source, this is just a pointer to the query results. I see that DataFrame() does collect the results but I don’t see a collection I can reference in my code.

The pretty table shows the correct results but I’m looking for a collection such as the list of tuples returned by Python’s fetchone() or fetchall(). I can’t believe no one else has asked this question. I am so very new to Julia that I must be deficient in my understanding.

Here’s the code that prints the pretty table which as far as I can see is useless except for my eyes.

using SQLite, DataFrames
results = DataFrame(DBInterface.execute(db, "SELECT * FROM place"))

This on the other hand prints the output shown below in the REPL:

DBInterface.execute(db, "SELECT * FROM place")

output:
SQLite.Query(SQLite.Stmt(SQLite.DB(“test”), 2), Base.RefValue{Int32}(100), [:place_id, :places], Type[Union{Missing, Int64}, Union{Missing, String}], Dict(:place_id => 1, :places => 2))

Thanks.

2

The execute statement returns data using the https://github.com/JuliaData/Tables.jl interface. This can be processed by multiple Julia packages, including DataFrames.jl.
Putting the output into a DataFrame is probably a good idea if you fetch a significant amount of data and need to analyze / process it further.
Alternatively, you can write the query results directly into a file, e.g. using https://github.com/lungben/TableIO.jl (shameless self promotion :wink: ).
Or use the Tables.jl interface directly to get specific values, as described here: Home · Tables.jl

1 Like
3

You may find it useful to take a look at the documentation for the DataFrames.jl package. This is in many ways the Julia equivalent of a pandas dataframe, although the usage is a little different.

You can access a particular column of the Dataframe with the dot-accessor notation. I.e,

using SQLite, DataFrames
results = DataFrame(DBInterface.execute(db, "SELECT col1, city FROM place"))
results.city[2]
# outputs "Topeka"
2 Likes
4

Thanks for the simple example. It not only answers my question and reassures me, but it points me in the right direction for further research.