I’m trying to figure out how to write a data frame to a sqlite3 table using SQLite.jl, but can’t wrap my head around the docs?
I figured it out.
db = SQLite.DB(db_path)
SQLite.load(db,"tablename",dataframe)
2 Likes
SQLite.load(db, "tablename", dataframe) is now deprecated.
SQLite.load!() does not work for me. I can see the db becomes the third arg, but load! does not seem to like DataFrames. instead, it is asking for Tables.jl implementation, which DataFrames does not seem to be.
so how do I write a DataFrame to an SQL table now?
1 Like
my best practice:
- checking the
runtest.jl: SQLite.jl/runtests.jl at master · JuliaDatabases/SQLite.jl · GitHub sometimes the documentation is not perfect yet.
one of the example from the test:
_
_ _ _(_)_ | Documentation: https://docs.julialang.org
(_) | (_) (_) |
_ _ _| |_ __ _ | Type "?" for help, "]?" for Pkg help.
| | | | | | |/ _` | |
| | |_| | | | (_| | | Version 1.0.1 (2018-09-29)
_/ |\__'_|_|_|\__'_| | Official https://julialang.org/ release
|__/ |
julia> using SQLite, DataFrames
julia> db = SQLite.DB("./mytest.sqlite")
SQLite.DB("./mytest.sqlite")
julia> dt = DataFrame(zeros(5, 5))
5×5 DataFrame
│ Row │ x1 │ x2 │ x3 │ x4 │ x5 │
│ │ Float64 │ Float64 │ Float64 │ Float64 │ Float64 │
├─────┼─────────┼─────────┼─────────┼─────────┼─────────┤
│ 1 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 2 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 3 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 4 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 5 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
julia> tablename = dt |> SQLite.load!(db, "temp")
"temp"
julia> r = SQLite.Query(db, "select * from $tablename") |> DataFrame
5×5 DataFrame
│ Row │ x1 │ x2 │ x3 │ x4 │ x5 │
│ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │
├─────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ 1 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 2 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 3 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 4 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 5 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
julia>
2 Likes
the save works perfectly. the query works but tells me that
julia> r= SQLite.query(db, "SELECT * from $tablename") |> DataFrame
┌ Warning: `SQLite.query(db, sql)` will return an `SQLite.Query` object in the future; to materialize a resultset, do `DataFrame(SQLite.query(db, sql))` instead
│ caller = ip:0x0
└ @ Core :-1
┌ Warning: `SQLite.Source(db, sql)` is deprecated in favor of `SQLite.Query(db, sql)` which executes a query and returns a row iterator
│ caller = ip:0x0
└ @ Core :-1:
...
julia> DataFrame(SQLite.query(db, "SELECT * from $tablename")) ## this is the 'retrieve' operation
┌ Warning: `SQLite.query(db, sql)` will return an `SQLite.Query` object in the future; to materialize a resultset, do `DataFrame(SQLite.query(db, sql))` instead
│ caller = ip:0x0
└ @ Core :-1
┌ Warning: `SQLite.Source(db, sql)` is deprecated in favor of `SQLite.Query(db, sql)` which executes a query and returns a row iterator
│ caller = ip:0x0
└ @ Core :-1
┌ Warning: In the future DataFrame constructor called with a `DataFrame` argument will return a copy. Use `convert(DataFrame, df)` to avoid copying if `df` is a `DataFrame`.
│ caller = top-level scope at none:0
└ @ Core none:0
What is the right warning-free way to do this? regards, /iaw
in the runtest.jl upcase -Q : SQLite.Query() → right warning-free way !
in your example - lowcase-q : SQLite.query() → Warnings
1 letter difference q vs. Q
julia> r = SQLite.Query(db, "select * from $tablename") |> DataFrame
5×5 DataFrame
│ Row │ x1 │ x2 │ x3 │ x4 │ x5 │
│ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │
├─────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ 1 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 2 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 3 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 4 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 5 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
julia> r2 = SQLite.query(db, "select * from $tablename") |> DataFrame
┌ Warning: `SQLite.query(db, sql)` will return an `SQLite.Query` object in the future; to materialize a resultset, do `DataFrame(SQLite.query(db, sql))` instead
│ caller = ip:0x0
└ @ Core :-1
┌ Warning: `SQLite.Source(db, sql)` is deprecated in favor of `SQLite.Query(db, sql)` which executes a query and returns a row iterator
│ caller = ip:0x0
└ @ Core :-1
┌ Warning: In the future DataFrame constructor called with a `DataFrame` argument will return a copy. Use `convert(DataFrame, df)` to avoid copying if `df` is a `DataFrame`.
│ caller = |>(::DataFrame, ::Type) at operators.jl:813
└ @ Base ./operators.jl:813
5×5 DataFrame
│ Row │ x1 │ x2 │ x3 │ x4 │ x5 │
│ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │ Float64⍰ │
├─────┼──────────┼──────────┼──────────┼──────────┼──────────┤
│ 1 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 2 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 3 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 4 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
│ 5 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │ 0.0 │
julia>
2 Likes
oh, this is funny. I was playing with this for an hour yesterday, and couldn’t see it. thanks, imre.
2 Likes