Use julia array in SQLite Query

General Usage
,
1

I asked this before in Slack, but failed to write down the answer. As penance, I vow to write this up as a PR to the SQLite docs :laughing:

I’d like to use an existing vector as part of a SQLite.Query. Eg:

julia> using SQLite, DataFrames

julia> df = DataFrame(label=string.(rand("abcdefg", 10)), value=rand(10));

julia> db = SQLite.DB(mktemp()[1]);

julia> tbl |> SQLite.load!(db, "temp");

julia> SQLite.Query(db,"SELECT * FROM temp WHERE label IN ('a','b','c')") |> DataFrame
4×2 DataFrame
│ Row │ label   │ value    │
│     │ String⍰ │ Float64⍰ │
├─────┼─────────┼──────────┤
│ 1   │ c       │ 0.603739 │
│ 2   │ c       │ 0.429831 │
│ 3   │ b       │ 0.799696 │
│ 4   │ a       │ 0.603586 │

julia> q = ['a','b','c'];

julia> SQLite.Query(db,"SELECT * FROM temp WHERE label IN ($q)") |> DataFrame
ERROR: SQLite.SQLiteException("no such column: 'a', 'b', 'c'")
Stacktrace:
 [1] sqliteerror(::SQLite.DB) at /home/kevin/.julia/packages/SQLite/msdQN/src/SQLite.jl:15
 [2] macro expansion at /home/kevin/.julia/packages/SQLite/msdQN/src/consts.jl:21 [inlined]
 [3] sqliteprepare at /home/kevin/.julia/packages/SQLite/msdQN/src/SQLite.jl:77 [inlined]
 [4] SQLite.Stmt(::SQLite.DB, ::String) at /home/kevin/.julia/packages/SQLite/msdQN/src/SQLite.jl:64
 [5] #Query#19(::Array{Any,1}, ::Bool, ::Bool, ::Type{SQLite.Query}, ::SQLite.DB, ::String) at /home/kevin/.julia/packages/SQLite/msdQN/src/tables.jl:82
 [6] SQLite.Query(::SQLite.DB, ::String) at /home/kevin/.julia/packages/SQLite/msdQN/src/tables.jl:82
 [7] top-level scope at REPL[43]:1
2

@quinnj responded on slack, recording here for posterity. The solution is esc_id:

julia> SQLite.Query(db,"SELECT * FROM temp WHERE label IN ($(SQLite.esc_id(q)))") 
4×2 DataFrame
│ Row │ label   │ value    │
│     │ String⍰ │ Float64⍰ │
├─────┼─────────┼──────────┤
│ 1   │ c       │ 0.603739 │
│ 2   │ c       │ 0.429831 │
│ 3   │ b       │ 0.799696 │
│ 4   │ a       │ 0.603586 │
3 Likes